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

Question

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.

You said you have used global variables, why would that not be elegant? Just use a public boolean variable that gets set to `True` in the case a subroutine is called within another routine. — Tim Stack, Aug 08 '19 at 13:32
I guess in my mind, an elegant solution would be a one-step solution vs a multi-step solution. Using global variables is a three step solution - declare variable. set variable, test variable. I'm look for a one-step solution like "if cursub>0 then.." if cursub is the child (1), grandchild (2), etc. — Joe Messingschlager, Aug 08 '19 at 13:42
https://stackoverflow.com/questions/15696462/how-to-know-the-procedure-by-which-subroutine-called-in-excel-vba — Siddharth Rout, Aug 08 '19 at 13:52
And also see [THIS](https://hammondmason.wordpress.com/2015/03/10/creating-a-call-stack-in-vba/) link mentioned in the above link as well. — Siddharth Rout, Aug 08 '19 at 13:53
non-VBA solution: hit [**CTRL+L**] in the code editor to view to call stack. — ashleedawg, Nov 17 '19 at 07:10

Pᴇʜ · Answer 1 · 2019-08-08T13:51:46.413

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.

Vityata · Answer 2 · 2019-08-08T13:54:25.923

2

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:

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

edited Aug 08 '19 at 13:54

answered Aug 08 '19 at 13:34

Vityata

42,633
8
55
100

1

I didn't know you could do this, which is great, but I'm hoping to have something that I can run in the code to tell me if I'm currently a parent or a child. If you're familiar with SAP, you can use the "session.Children.count" function to perform similar. – Joe Messingschlager Aug 08 '19 at 13:45
1

@JoeMessingschlager - the only solution I can think about is usage of a variable or logging into an Excel sheet (which is pretty much the same). – Vityata Aug 08 '19 at 13:48

score 1 · Answer 3 · answered Aug 08 '19 at 13:51

1

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

answered Aug 08 '19 at 13:51

Ralph

9,284
4
32
42

score 0 · Answer 4 · answered Aug 08 '19 at 14:22

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

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

4 Answers4