User input or event procedure vs Called procedure (VBA)

Question

Is there a way to tell if a procedure is being run from an event or user input vs a procedure that called from another macro?

Eg, have a Boolean value represented if the procedure is called from another macro?

Obviously the code below is moot, but you get the idea.

dim macro

     macro = called.proccedure
   if macro = true then
      'do what ever
   end if

No, there is no way to know in the procedure how it is called — Paul Ogilvie, Apr 14 '16 at 12:22
I could be wrong with my answer, but if i'm understanding your answer correctly you can glean the information you want from using Application.caller in certain ways. — StormsEdge, Apr 14 '16 at 12:25

StormsEdge · Answer 1 · 2016-04-14T13:34:43.647

0

You can use Application.Caller to glean information about where your procedure is being called from.

I've used Application.Caller.Column to find what location my data was coming from in order to satisfy conditionals.

Example of how I've used the method:

 If Application.Caller.Column = 20 Then Call ChangeDirectionForSellSide(firstLegBuySell, secondLegBuySell, futureLegBuySell)

    'First option leg built
    chatConfirmString = "You " & firstLegBuySell & " " & Format(firstLegQuantity, "#,##0") & DetermineProductMeasurementType(productType, tradeDataRange.Item(2).Value, contractMonth) & " " & productType & " " & tradeDataRange.Item(2).Value & " " & contractMonth & " " & Format(tradeDataRange.Item(6).Value, "#,###.00##") & " " & tradeDataRange.Item(7).Value & " @ "

    'Formats the price type
    If productType = "WTI" Or productType = "BRT ICE" Or productType = "FO 3.5%" Or productType = "GO" Then

        chatConfirmString = chatConfirmString & Format(tradeDataRange.Item(8).Value, "#,###.00")

    Else

        chatConfirmString = chatConfirmString & Format(tradeDataRange.Item(8).Value, "#,###.0000")

    End If

There are a few issues with the code you posted above. You're utilizing the Application syntax wrong (from misunderstanding). You would have to refer to it as Application.Caller. "what ever object" you're trying to reference. Procedures are not objects (at least to my knowledge they're not), but what are the possible circumstances that could be presented in your code? Is this for a UDF and therefore could return a range? Could it return a sheet? Where is the procedure? These are questions that we would need to answer in order to accurately create a conditional that would evaluate a boolean value to true or false.

edited Apr 14 '16 at 13:34

answered Apr 14 '16 at 12:24

StormsEdge

854
2
10
35

How would you put that into a Boolean variable. ATM, that throws an error – Apr 14 '16 at 12:28
Well you'd use it as a conditional to determine the value of your bool. So If Application.Caller.Column = 3 Then sheetLocationBool = True. However, keep in mind that you're expecting a variable return from Application.Caller so it's very easy to receive an error if you attempt to utilize a data type from the Application that is irrelevant. I.E. if it was called from a sub and you're treating it like a range object you'll receive an error (obviously). – StormsEdge Apr 14 '16 at 12:33
I am not sure how I could use this to help me? Example code ? – Apr 14 '16 at 12:35
I am using variant type variable and still get an error, I can't get this to work at all. Also, what values tell you what the caller is doing? – Apr 14 '16 at 12:46
Can you please edit your question with your code so we can have a better idea of what you're trying to do and why it's perhaps not working? – StormsEdge Apr 14 '16 at 12:48
Updated, thanks thus far for the help. It is much appreciated. – Apr 14 '16 at 13:12
@JapanDave Added comments at the bottom of my answer. – StormsEdge Apr 14 '16 at 13:34
No, I am trying to establish if the function is called by another function or if it was user invoked. In other words purely within the modules of the VB editor. I thought I made that clear in my question? – Apr 14 '16 at 13:53
User invoked from where? Using it in a cell? "=TESTFUNCTION(data)"? Or being called by another module? The invoked by a user is I think where we are on different wave lengths. – StormsEdge Apr 14 '16 at 13:57
It does not matter from where, if a user clicks a button or if it is due to a cell change event, the users actions invokes the macro. What I am trying to establish is if the macro is called from another macro, ie `call myMacro ` from inside a macros code. – Apr 14 '16 at 14:02
Then the call would come from that cell if there was a cell change event (Application.Caller.Cell) no? Otherwise, I don't believe there is a way to make a distinction between an macro initialized from a button push or a change event. That being said; it is complicated, but you could test for changes in the cells in question from key strokes, which would then tell you that the function was called from the cell rather than a user initializing via a button push. Check this link: http://stackoverflow.com/questions/11153995/is-there-any-event-that-fires-when-keys-are-pressed-when-editing-a-cell – StormsEdge Apr 14 '16 at 14:08
1

Ahh, OK, I see what you are saying now. My feeble brain did not want to work. Repped you, thanks for the help. – Apr 15 '16 at 01:07

score 0 · Accepted Answer · answered Apr 14 '16 at 14:01

If my understanding is right, there's only the trivial solution of hard coding the sub/procedure/event name into some variable to be queried for decision

I remember I read a fine and deep thread in SO about this and at the end, after digging into very complex solutions, the most straightforward one left was what I wrote above

and it's almost compulsory to use a Public variable, to make it as less verbose a possible:

for instance:

Option Explicit

Public thingName As String


Sub main()

thingName = "Main" '"Mark" you're in 'Main' sub

'...
Call DoSomething 'call DoSomething() procedure

End Sub


Sub DoSomething()

thingName = "DoSomething" '"Mark" you're in 'DoSomething' sub

MsgBox ReturnSomething ' ReturnSomething() function

'...

End Sub


Function ReturnSomething()

thingName = "ReturnSomething" '"Mark" you're in 'ReturnSomething' Function

'...
ReturnSomething = "something"

End Function

that of course should be extended to any Userform event handler subs too

a more precise and (hopefully) ueseful but lengthy version is:

Option Explicit

Public Type Thing
    Name As String
    Type As String
    Job As String
End Type
Dim myThing As Thing


Sub main()

'"Mark" you're in 'Main' sub
With myThing
    .Name = "Main"
    .Type = "Sub"
    .Job = "main procedure"
End With

'...
Call DoSomething 'call DoSomething() procedure    

End Sub


Sub DoSomething()

'"Mark" you're in 'DoSomething' sub
With myThing
    .Name = "DoSomething"
    .Type = "Sub"
    .Job = "procedure to do something"
End With


MsgBox ReturnSomething 'call ReturnSomething() function

'...

End Sub


Function ReturnSomething()

'"Mark" you're in 'ReturnSomething' Function
With myThing
    .Name = "ReturnSomething"
    .Type = "Function"
    .Job = "procedure to return something"
End With

'...
ReturnSomething = "something"

End Function

So bsically use a global variable to tell if a sub has been called or not? — , Apr 14 '16 at 14:07
Yes. more precisely, use a global variable and define it every time you enter a sub/function. And if you want to keep track of the "parent" caller too then you would add another `public callerName as String` to be defined upon entering any sub/function as the "current" value of `thingName` before redefining this latter — user3598756, Apr 14 '16 at 14:19
Thanks, that is what I am going to do. Repped you for the help. Cheers — , Apr 15 '16 at 01:08

User input or event procedure vs Called procedure (VBA)

2 Answers2