[Solved] Determining how a macro was called

[Alex6361]

From within a Basic macro / sub / function how can I determine how the macro was called? I.e., from another macro, from a Button on a worksheet, a Menu option, etc. And, if called from a Button, which Button and what is the worksheet location (row and column) of that Button?

Thanks.

[Zizi64]

You can determine the calling form control elements. maybe this topis will help you:
viewtopic.php?f=39&t=38725

[Zizi64]

Other similar topics:
viewtopic.php?f=39&t=72419
viewtopic.php?f=39&t=57982
viewtopic.php?t=27860

[Alex6361]

Tibor,

Thanks for you efforts to help! However, unless I'm failing to see something in the links you provided, I don't find the solution to my question there. What I'm looking for is something reasonably comparable to Application.Caller in VBA; and from other searches, it doesn't appear that the openoffice api yet offers such a thing (which seems surprising to me since Microsoft saw the usefulness / need of this feature and addressed it 20 years ago). With a lot of inelegant programming I can probably -- in a brute-force sort of way -- find out whether my sub got called as a macro from a button on a worksheet or from a menu item in the workbook or from another Basic sub (and, in each case, which button, line, sub, or whatever), but I hoped for something better. One way, of course, is to create a separate macro for each way a sub could be called, and from within each such macro (which therefore knows how it was called) to then call my main sub using a parameter which identifies where the call is coming from. Not very elegant, but it would do the job.

[Zizi64]

Have you read the linked topics?


Here is a simple example code based on the content of those topics:

Code: Select all

Sub MySub(oEvent)

     oCaller=oEvent.Source.Model
     sName = oCaller.Name
     sLabel = oCaller.Label
     MsgBox("You clicked on: " & sLabel,0,sName)

End Sub

Determine caller.ods

(13.72 KiB) Downloaded 235 times

[Alex6361]

Tibor,

I had read the links, but until getting your last reply and the ods example you kindly provided I failed to grasp that when a sub is called as a macro from a control object there is an optional parameter provided to the sub which is an object of type com.sun.star.awt.ActionEvent that triggered the call. I subsequently found this feature described in the documentation (on https://wiki.openoffice.org/wiki/Docume ... ide/Events), where it is mentioned so casually that I missed it on the numerous times I had previously read that page.

Using that feature I can easily determine which control object initiated the call, but so far I have not been able to find out how to determine which cell on the spreadsheet that control is anchored to, and thereby get the row and column of that cell. But, that's a new and separate problem to the original topic.

So, I think you've answered my question regarding how a sub or function can learn at least something about how it was called (by examining its first parameter and programatically reacting to that). I'm not sure that covers all the situations that VBA's Application.Caller handled, but it's something to start with. Thanks!

[Zizi64]

but so far I have not been able to find out how to determine which cell on the spreadsheet that control is anchored to, and thereby get the row and column of that cell. But, that's a new and separate problem to the original topic.

Use the Sheet(), Row() Column() functions without any parameter to pass the sheet number and the coordinates of the caller cell:

Code: Select all

function MyFunction(SheetNr as integer, RowNr as long, ColNr as long, optional OtherParameter as double)

	ThisCell= getSheetCell(SheetNr,RowNr,ColNr)
	msgbox("This function was called from cell "& ThisCell.AbsoluteName,0,"Information")
	
	MyFunction = OtherParameter * 2 + 1
	
End Function

Determine caller2.ods

(14.8 KiB) Downloaded 209 times

[Alex6361]

Strangely, the ods you provided caused my version of LibreOffice to terminate suddenly each time I tried to open it until I turned off macro execution. But, with execution turned off, I was able to see your code and how the sheet was constructed. The situation I have is different. I am not using User Defined Function's. Rather, I have a group of simple push-buttons located on spreadsheets to which macros have been assigned, such that when any button is clicked the macro assigned to it gets called. I don't believe the suggestion you made for UDFs can apply here, since there is no way to provide parameters to the macro (except for the implicit one representing the call Event which you informed me about in previous replies).

While I would like to think that somewhere in the vast API there is a method to get the information I wanted, having spent over 10 hours looking for and not finding one, I have chosen to just create in my sub a table of the names of the buttons which can call the sub (through their assigned macro calls) with the corresponding sheet cell addresses where they are located. I can get the name of the calling button (thanks to your earlier help), and from that, can look up in my table where it's supposed to be. Such an approach doesn't automatically adjust if the buttons get moved on a sheet as I had hoped to be able to allow for (and have done with VBA for Excel), but in the absence of knowing a better solution, it will suffice for now. Thanks so much for your help and patience!

[Zizi64]

but so far I have not been able to find out how to determine which cell on the spreadsheet that control is anchored to, and thereby get the row and column of that cell. But, that's a new and separate problem to the original topic.

Sorry I missed it...

Here is an example code:
viewtopic.php?f=9&t=30737

[Alex6361]

Tibor, thanks again for your help in getting me over another bump on the learning process! The link you provided answered my question, and is a better solution than the work-around I described earlier. I'll restate that code here with a few modifications for the benefit of others looking for the same answer.

Code: Select all

Sub WhereIsButton (Optional HowCalled as Object)

'When called as a macro by a Control Button on a spreadsheet, displays the column and 
'row numbers of the cell the button is anchored to.  In this scenario, HowCalled is
'an Event object with information about the event initiated by clicking the button.

   Dim ActiveSheet as Object
   Dim ButtonName as String
   Dim ButtonRow as Long
   Dim ButtonColumn as Long
   Dim ControlName as String
   Dim DrawPageItem as Object
   Dim I as Integer

   ActiveSheet = thisComponent.CurrentController.ActiveSheet
	
   'Determine which button called this sub . . .
   ButtonName = HowCalled.Source.Model.Name

   '. . . and which cell is it anchored to in order to get its row and column numbers:
   ButtonColumn = -1
   'index through the elements on the active sheet's DrawPage
   For I = 0 to (ActiveSheet.DrawPage.count -1) 	
        DrawPageItem = ActiveSheet.DrawPage(I)
        'since there can be a lot of stuff on a DrawPage, 
        'eliminate those items which can't be control buttons
        if InStr(DrawPageItem.ShapeType, "ControlShape") > 0 then
              ControlName = DrawPageItem.Control.Name   'usually works without the .Control
              if ControlName = ButtonName then
                      ButtonColumn = DrawPageItem.Anchor.CellAddress.Column
                      ButtonRow = DrawPageItem.Anchor.CellAddress.Row
                      MsgBox "Button position is Column # " & ButtonColumn & ", Row # " & ButtonRow
                      Exit For
               end if
         end if
    Next
    if ButtonColumn < 0 then MsgBox "Unable to find button named " & ButtonName & " on DrawPage."
    
End Sub

The method basically requires one to find something unique about the control which initiated the call, then search all of the items on the spreadsheet's DrawPage looking for a control item possessing that unique something, and then get that object's Anchor location. It certainly works, although I might have expected that having found the control object via the Event object, one would be able to gets its location in a more direct fashion.