Page 1 of 1

Getting Caller (Calc Cell) in an UDF

PostPosted: Mon Feb 22, 2010 9:51 pm
by 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   Expand viewCollapse view
Application.Caller
.

Re: Getting Caller (Calc Cell) in an UDF

PostPosted: Mon Feb 22, 2010 9:55 pm
by Villeroy
No way. Spreadsheet functions calculate nothing but cell values.
http://api.openoffice.org/docs/common/r ... AddIn.html

Re: Getting Caller (Calc Cell) in an UDF

PostPosted: Mon Feb 22, 2010 11:25 pm
by fries
Villeroy wrote: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   Expand viewCollapse view
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?

Re: Getting Caller (Calc Cell) in an UDF

PostPosted: Tue Feb 23, 2010 12:17 am
by 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: viewtopic.php?f=21&t=2762
But this is just a work-around which requires hard recalculations.

Re: Getting Caller (Calc Cell) in an UDF

PostPosted: Tue Feb 23, 2010 12:34 am
by ms777
see http://www.oooforum.org/forum/viewtopic ... 047#255047

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

Re: Getting Caller (Calc Cell) in an UDF

PostPosted: Tue Feb 23, 2010 12:47 am
by 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   Expand viewCollapse view
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   Expand viewCollapse view
=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.

Re: Getting Caller (Calc Cell) in an UDF

PostPosted: Tue Feb 23, 2010 10:03 am
by fries
Villeroy wrote: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   Expand viewCollapse view
Function ViewData
   ViewData = ThisComponent.CurrentController.viewData
End Function

Re: Getting Caller (Calc Cell) in an UDF

PostPosted: Tue Feb 23, 2010 10:05 am
by fries
FJCC wrote: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.

Re: Getting Caller (Calc Cell) in an UDF

PostPosted: Tue Feb 23, 2010 1:25 pm
by 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?!?

Re: Getting Caller (Calc Cell) in an UDF

PostPosted: Tue Feb 23, 2010 2:44 pm
by 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.

Re: Getting Caller (Calc Cell) in an UDF

PostPosted: Tue Feb 23, 2010 2:52 pm
by Villeroy
The easiest event handler in Basic:
Z1 =TRIGGER(A1:Y1;SHEET();ROW();COLUMN())
Code: Select all   Expand viewCollapse view
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 viewtopic.php?f=21&t=2762

Re: Getting Caller (Calc Cell) in an UDF

PostPosted: Mon May 30, 2011 11:21 am
by feddd
You absollutly right, fries.
If you use ROW() without its argument you get the ROW() of the cell that called the function.

Re: Getting Caller (Calc Cell) in an UDF

PostPosted: Sun Feb 28, 2021 3:36 pm
by eeigor
No SHEET (), ROW (), COLUMN ()
Use Excel VBA Range. Just referring to itself. And without quotes.

C5: =UDF(Arg1;Agr2;C5)

Code: Select all   Expand viewCollapse view
Option VBASupport 1

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

Re: Getting Caller (Calc Cell) in an UDF

PostPosted: Sun Feb 28, 2021 6:29 pm
by Villeroy
eeigor wrote:Use Excel VBA Range. Just referring to itself. And without quotes.

Seems to work with LibreOffice since version 7.