CODEWITHSUNDEEP
excelvba
Joe Messingschlager
Joe Messingschlager

Reputation: 11

Is there a way to tell if the Sub currently running is the parent or a called child

I have routines in VBA that can run standalone or can be called by another routine. When called as a subroutine (child) or as a primary routine (parent) there are operations that I may or may not want to execute. Is there a built-in function in VBA that I can call that will tell me if my executing code is a parent or child?

I have created global variables to test for parent/child but I would like to have a more elegant solution.

Upvotes: 1

Views: 333

Answers (4)

Joe Messingschlager
Joe Messingschlager

Reputation: 11

This is my Occam's Razor solution that I have used in the past.

Public ChildCount as Integer
Sub EveryProc()
  ChildCount = ChildCount + 1
  ... rest of code...
  ChildCount = ChildCount - 1
  End Sub

This allows me to test how far I am into subroutines as ChildCount will be 1 for the parent and >1 for the children. I think the first time into the VBA, ChildCount will be zero so you need to increment and decrement the variable at the beginning and end of every sub. I am spoiled in SAP...

Upvotes: 0

Vityata
Vityata

Reputation: 43585

In .Net getting info for a method, which called a method is called Reflection. It is quite straight-forward in C# - How can I find the method that called the current method?. VBA does not support it, but you could run around it, and log somewhere data about it - through a variable or logging to a worksheet or database.

There is a way to do it, if you stop the code in the middle of the called sub/function and call the Call Stack diaglog box. E.g., imagine the following seqeuence:

Sub TestMe()
    Testme2
End Sub

Sub Testme2()
    Stop
End Sub

If you run the code and press Ctrl+L once you are on the Stop you would get this:

enter image description here

If you only run TestMe2 and press Ctrl+L, you would get it correspondingly:

enter image description here

Upvotes: 2

Pᴇʜ
Pᴇʜ

Reputation: 57683

I think a good way would be to have a procedure for the action itself that has a switch (parameter) and a procedure that calls it.

Private Sub MyProcedure(Optional ByVal IsChild As Boolean = True) 'set default here
    If IsChild Then
        'child
    Else
        'parent
    End If
End Sub

Now you can have a procedure to call it 

Public Sub ParentCallMyProcedure()
    MyProcedure IsChild:=False
End Sub

Public Sub ChildCallMyProcedure()
    MyProcedure IsChild:=True
    'which would be the same as
    MyProcedure
End Sub

Eg if you want to call MyProcedure from a button then use

Public Sub Button1_Click()
    MyProcedure IsChild:=False
End Sub

In all other procedures just use MyProcedure and IsChild is default True.

At least this is more elegant than a public/global variable.

Upvotes: 3

Ralph
Ralph

Reputation: 9434

While the following is not really a solution it may work depending on your setup:

enter image description here

Upvotes: 1

Related Questions