Getting Caller (Calc Cell) in an UDF

Java, C++, C#, Delphi, ??? - Using the UNO bridges
Post Reply
fries
Posts: 6
Joined: Mon Feb 22, 2010 8:22 pm

Getting Caller (Calc Cell) in an UDF

Post 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

Application.Caller
.
User avatar
Villeroy
Volunteer
Posts: 30803
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Getting Caller (Calc Cell) in an UDF

Post by Villeroy »

No way. Spreadsheet functions calculate nothing but cell values.
http://api.openoffice.org/docs/common/r ... AddIn.html
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
fries
Posts: 6
Joined: Mon Feb 22, 2010 8:22 pm

Re: Getting Caller (Calc Cell) in an UDF

Post 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

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?
OpenOffice 3.2 for Mac OS 10.6
User avatar
Villeroy
Volunteer
Posts: 30803
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Getting Caller (Calc Cell) in an UDF

Post 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: http://user.services.openoffice.org/en/ ... =21&t=2762
But this is just a work-around which requires hard recalculations.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ms777
Volunteer
Posts: 155
Joined: Mon Oct 08, 2007 1:33 am

Re: Getting Caller (Calc Cell) in an UDF

Post by ms777 »

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

a stupid workaround, but AFAIK the only solution to getting the calling cell
FJCC
Moderator
Posts: 8645
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Getting Caller (Calc Cell) in an UDF

Post 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

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
fries
Posts: 6
Joined: Mon Feb 22, 2010 8:22 pm

Re: Getting Caller (Calc Cell) in an UDF

Post 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

Function ViewData
	ViewData = ThisComponent.CurrentController.viewData
End Function
OpenOffice 3.2 for Mac OS 10.6
fries
Posts: 6
Joined: Mon Feb 22, 2010 8:22 pm

Re: Getting Caller (Calc Cell) in an UDF

Post 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.
OpenOffice 3.2 for Mac OS 10.6
fries
Posts: 6
Joined: Mon Feb 22, 2010 8:22 pm

Re: Getting Caller (Calc Cell) in an UDF

Post 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?!?
OpenOffice 3.2 for Mac OS 10.6
User avatar
Villeroy
Volunteer
Posts: 30803
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Getting Caller (Calc Cell) in an UDF

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 30803
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Getting Caller (Calc Cell) in an UDF

Post by 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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
feddd
Posts: 1
Joined: Mon May 30, 2011 11:19 am

Re: Getting Caller (Calc Cell) in an UDF

Post by feddd »

You absollutly right, fries.
If you use ROW() without its argument you get the ROW() of the cell that called the function.
OpenOffice 3.1 on Windows Vista
eeigor
Posts: 210
Joined: Sun Apr 12, 2020 10:56 pm

Re: Getting Caller (Calc Cell) in an UDF

Post 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

Option VBASupport 1

Function UDF(Arg1, Arg2, Optional Caller)
    If Not IsMissing(Caller) Then
        sCallerName = Caller.CellRange.AbsoluteName
        Print sCallerName
    End If
End Function
Ubuntu 18.04 LTS • LibreOffice 7.3.5.2 Community
User avatar
Villeroy
Volunteer
Posts: 30803
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Getting Caller (Calc Cell) in an UDF

Post by Villeroy »

eeigor wrote:Use Excel VBA Range. Just referring to itself. And without quotes.
Seems to work with LibreOffice since version 7.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply