Getting Caller (Calc Cell) in an UDF

[fries]

How do I get the address of the cell which called my (Java) user defined function in a Calc sheet? (So if cell C5 contains the call to MyFunction(..), I would like to get that "C5" inside the Java implementaton of MyFunction.

C.

PS: In Excel / VBA this can be achived by

Code: Select all

Application.Caller

.

[Villeroy]

No way. Spreadsheet functions calculate nothing but cell values.
http://api.openoffice.org/docs/common/r ... AddIn.html

[fries]

No way. Spreadsheet functions calculate nothing but cell values.
http://api.openoffice.org/docs/common/r ... AddIn.html

I don't see why that implies that I cannot get the callers address. For example, there are functions like ROW() which return the row index of the calling cell. Or you can get the currently active sheet with

Code: Select all

Function Test
	Test = ThisComponent.CurrentController.ActiveSheet.Name
End Function

In Excel/VBA you can get the calling cells address and this is a very useful function - e.g. for debugging. If your Function / Macro throws an exception you can write a log message containing the address of the spreadsheet cell which caused the problem.

Or: Does anyone know how ROW() is implemented?

[Villeroy]

There is nothing like Application.caller and the documentation proves my point.
The active sheet is useless unless your function really wants to access the active sheet. Anything related to the controller is not availlable for hidden documents or while the document is loading.
I wrote a set of function to fix broken spreadsheet with values hidden behind attributes. In fact they use the indices of sheet, row and column in order to reference a cell in the active document: http://user.services.openoffice.org/en/ ... =21&t=2762
But this is just a work-around which requires hard recalculations.

[ms777]

see http://www.oooforum.org/forum/viewtopic ... 047#255047

a stupid workaround, but AFAIK the only solution to getting the calling cell

[FJCC]

I certainly don't know of any way for a function to know which cell called it. The example of ROW() is not relevant, I think, because ROW() requires an argument. The argument is a reference to the cell for which you want to know the row. The value returned depends on that argument, not on what cell called ROW(). You can, however, pass to your function the address of the calling cell. I made this simple function:

Code: Select all

Function Tester(arg1, arg2)
On Error GoTo EH

Tester = arg1^0.5
exit function
EH:
Print "This cell caused an error: " &  arg2
End function

and then used it in cell B5 like this.

Code: Select all

=TESTER(D3; CELL("ADDRESS";B5))

If D3 contains a non-negative number, B5 gets the value of D3^0.5. If D3 is negative, the function prints the message "This cell caused an error: $B$5". Of course, you could make the second argument of Tester just the string "B5". I added the CELL() function so the whole thing can be copied to other cells yet keep the second argument referring to the correct cell. This isn't exactly what you wanted, but it is as close as I know how to get.

[fries]

There is nothing like Application.caller and the documentation proves my point.

But ROW() gets you the row of the calling cell - so there IS an implementation the makes use of some way to get the callers address. Note that I do not need to rely on Basic. It would be sufficient to implement some UNO Java stuff.

In Basic - for exampe - the following code gets you a string which contains row and column of the active cell (this is not necessarily the caller, but close)

Code: Select all

Function ViewData
	ViewData = ThisComponent.CurrentController.viewData
End Function

[fries]

The example of ROW() is not relevant, I think, because ROW() requires an argument.

If you use ROW() without argument you get the ROW() of the cell that called the function. Just try it.

[fries]

Just as a side note: The reason why I am interested in this function: The upcomming version of the Excel add-in Obba has the property that if an exception is raised in a Java method, then the cell triggering that exception is printed to the log file. I would like to add this to the OpenOffice port of Obba.

Maybe it is possible to register an event listener which is trigger whenever a formula is executed and fetch the address from the event source, then store it in a global variable which gets printed by the exception handler?!?

[Villeroy]

Yes, ROW(), COLUMN() and SHEET() with or without argument can specify a cell's position within a spreadsheet document. The links provided by ms777 and me point to usable examples with explanations how this indirect referencing can be used analog to NextSheet.$A1:X$99
However, you need a reference to some document (ThisComponent) so your code must not be called when ThisDocument points to some unwanted document. This bites you when ActiveSheet or CurrentSelection is dereferenced during recalculation of your userdefined formulas.

[Villeroy]

The easiest event handler in Basic:
Z1 =TRIGGER(A1:Y1;SHEET();ROW();COLUMN())

Code: Select all

Function trigger(a(),s,r,c)
   ThisCell= getSheetCell(s,r,c)
msgbox "something in the referenced range of "& ThisCell.AbsoluteName &" did happen"
End Function

getSheetCell is the same as in http://user.services.openoffice.org/en/ ... =21&t=2762

[feddd]

You absollutly right, fries.
If you use ROW() without its argument you get the ROW() of the cell that called the function.

[eeigor]

No SHEET (), ROW (), COLUMN ()
Use Excel VBA Range. Just referring to itself. And without quotes.

C5: =UDF(Arg1;Agr2;C5)

Code: Select all

Option VBASupport 1

Function UDF(Arg1, Arg2, Optional Caller)
    If Not IsMissing(Caller) Then
        sCallerName = Caller.CellRange.AbsoluteName
        Print sCallerName
    End If
End Function

[Villeroy]

Use Excel VBA Range. Just referring to itself. And without quotes.

Seems to work with LibreOffice since version 7.