Getting Caller (Calc Cell) in an UDF

Java, C++, C#, Delphi, ??? - Using the UNO bridges

Getting Caller (Calc Cell) in an UDF

Postby fries » Mon Feb 22, 2010 9:51 pm

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
.
fries
 
Posts: 6
Joined: Mon Feb 22, 2010 8:22 pm

Re: Getting Caller (Calc Cell) in an UDF

Postby Villeroy » Mon Feb 22, 2010 9:55 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26249
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Getting Caller (Calc Cell) in an UDF

Postby fries » Mon Feb 22, 2010 11:25 pm

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?
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

Postby Villeroy » Tue Feb 23, 2010 12:17 am

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

Re: Getting Caller (Calc Cell) in an UDF

Postby ms777 » Tue Feb 23, 2010 12:34 am

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

a stupid workaround, but AFAIK the only solution to getting the calling cell
ms777
Volunteer
 
Posts: 131
Joined: Mon Oct 08, 2007 1:33 am

Re: Getting Caller (Calc Cell) in an UDF

Postby FJCC » Tue Feb 23, 2010 12:47 am

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.
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.
FJCC
Moderator
 
Posts: 7009
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Getting Caller (Calc Cell) in an UDF

Postby fries » Tue Feb 23, 2010 10:03 am

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
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

Postby fries » Tue Feb 23, 2010 10:05 am

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

Postby fries » Tue Feb 23, 2010 1:25 pm

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
fries
 
Posts: 6
Joined: Mon Feb 22, 2010 8:22 pm

Re: Getting Caller (Calc Cell) in an UDF

Postby Villeroy » Tue Feb 23, 2010 2:44 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26249
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Getting Caller (Calc Cell) in an UDF

Postby Villeroy » Tue Feb 23, 2010 2:52 pm

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

Re: Getting Caller (Calc Cell) in an UDF

Postby feddd » Mon May 30, 2011 11:21 am

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
feddd
 
Posts: 1
Joined: Mon May 30, 2011 11:19 am


Return to External Programs

Who is online

Users browsing this forum: No registered users and 1 guest