Asked before: How can a Calc macro refer to the cell it was called from?

English
,
#1

I have an Excel macro that I am trying to port to LibreOffice Calc. The Excel macro has the line: iRow = Application.Caller.Row, but when this is run in IDE, I get the error: Object variable not set.

What can I do within the IDE to return the row, and column, from which the macro is being called?

#2

.

Depending on how literally you mean “within” you can add a parameter to the function and pass in ROW(). So

MYSELFAWAREFUNC(Datum1,Datum2,SHEET(),COLUMN(),ROW())

Just be sure to subtract 1 since everything in your macro (other than function access) will be 0-based addressing.

See Getting Caller (Calc Cell) in an UDF (View topic) • Apache OpenOffice Community Forum

I think you can also just send in a range…and pick the SCR from that. Check the link clear to the end.

#6

https://forum.openoffice.org/en/forum/viewtopic.php?f=44&t=27860

In short - it is not possible (unless you pass it explicitly, e.g. as @joshua4 suggests).

1 Like
#12

A Calc macro in StarBasic, Python, JavaScript, BeanShell can easily refer to the cell it was called from. But an Excel macro (somehow interpreted by LibreOffice) may fail because it was written for a completely different application.

#16

No, it only can refer to something it calculates from passed arguments. It may be used if appropriate, but is not exactly what is asked.

1 Like
#17

t73621.ods (8.4 KB)
Easy enough.

#19

Sigh.

#3

For example:

doc = ThisComponent
selection = doc.CurrentSelection
If selection.ImplementationName = "ScCellObj" Then
	MsgBox selection.CellAddress.Row
	MsgBox selection.CellAddress.Column
End If
#5

CurrentSelection is not “the cell it was called from”. When you press Ctrl+F9, your file may recalculate thousands of cells with the UDF, but only one (or even none) of them may be selected.

#7 
If selection.ImplementationName = "ScCellObj" Then
#8

That only shows that a cell is selected on a sheet, but is absolutely unrelated to the cell in which the formula is evaluated.

#9

the user say: What can I do within the IDE to return the row, and column, from which the macro is being called?

#10

The Application.Caller property relates to custom functions (or user-defined functions, UDF) that are written e.g. in Basic, and used as spreadsheet functions. These spreadsheet functions have nothing to do with selection.

E.g., you may have this Basic code:

Function Foo(n)
  Foo = n * n * n
End Function

and in a spreadsheet, you put this into A1: =Foo(5)
This calls the Basic code when calculating the cell A1; and OP wants to find a way to know in Foo that is is called from A1. But it is unrelated to what is selected on the sheet at the moment: you may go to another sheet on the same spreadsheet, and select B2, and press Ctrl+Shift+F9 to hard-recalc everything - and your current selection will be Sheet2.B2, but the Basic code Foo will still be run in cell Sheet1.A1.

#11

ok, you’re right…

#4

Try this.

A1: =UDF(1;2;A1)

Option VBASupport 1

Function UDF(Arg1, Arg2, Optional Caller)
	If Not IsMissing(Caller) Then
		sCallerName = Caller.CellRange.AbsoluteName
		Print sCallerName
		With Caller
			Print "Column = " & .Column, "Row = " & .Row  '1-based indexing
		End With
	End If
End Function
#13

Works, but the danger is, of course, that if the address passed ever gets desynchronized with the address the formula is in, then you have a mess, and one that might be pretty hard to sort out. This is why I think that if you are going to pass a parameter, anyway, it might be safer to pass ROW() and COLUMN() themselves, or just whichever you actually need.

#14

The address is relative. Move the cell to another location and the reference will change.
And such things, I suppose, are required for selected cells, there shouldn’t be many of them.

#18

:face_with_monocle: I’m sure you are right. I’m OCD about encoding the same value twice in any way, implicitly or explicitly, probably because I’m so good at finding a way to break things five minutes after I thought about not breaking them that way.

#15

[Calc, Basic] Introspective cell functions
works well enough with relative, absolute and mixed addresses.

#20

Thanks for the feedback. The CurrentSelection did not seem to work and the Caller seemed to reference to parameter passed in. The notion of passing the row and column worked. Please keep in mind that when it comes to LibreOffice, I am a total newbie!

Now, if there were only some way, perhaps something like a #ifdef, so that I could have one version of the code that worked under both Excel and LibreOffice…

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.