Call Hierarchy for a VBA procedure

What is a Call Hierarchy? Here's a (contrived!) example:

You can see from the dialog title that this is a Call Hierarchy for a Sub named 'TheSubToGetInfoAbout' in 'Module1'.

Then, in the upper list, you can see that there are 4 calls of this Sub from other procedures:

• One call from 'SomeTopLevelSub' which is also in 'Module1'

• Two calls from separate lines in 'AnotherTopLevelSub' which is in 'Module2'

• One call from 'AndAnotherTopLevelSub' which is in 'Module2'

Then, in the lower list, you can see that there are 2 calls of other procedures from this Sub:

• One call of 'LowerLevelSub' which is also in 'Module1'

• One call of 'AnotherLowerLevelSub' which is in 'Module2'

So, unsurprisingly, this dialog is helping you to understand where a particular procedure sits in the overall hierarchy of calls between different procedures.

But it goes further. If, in the above example, you select any of the listed procedures (in either the upper or lower lists) and click 'View call' (or just double-click directly on any of the listed procedures) then you can drill-down further into the calls for that other procedure. For example, if you double-click on 'LowerLevelSub', then (in this still contrived example) I get:

Now you can see from the dialog title that this is a Call Hierarchy for 'LowerLevelSub' in 'Module1'.

Then, in the upper list, you can see that there is just 1 call of this Sub from another procedure:

• One call from 'TheSubToGetInfoAbout' which is also in 'Module1' ... you'd expect to see this because that's where we just drilled-down from

Then, in the lower list, you can see that there are 2 calls of other procedures from this Sub:

• One call of 'SomeEvenLowerLevelSub' which is also in 'Module1'

• One call of 'SomeOtherEvenLowerLevelSub' which is in 'Module3'

Note also that 'TheSubToGetInfoAbout' in the upper list is in bold font and also note that the 'Back' button is now enabled ... clicking 'Back' would take you back ('drill-up'?!) to the Call Hierarchy for the procedure that we just drilled-down from. It's possible to drill-down (or up!) as many levels as there are calls in the VBA code.

How do you create Call Hierarchy? You use VBE_Extras of course! Just put your cursor within the procedure that you want a Call Hierarchy for, then in the VBE main menu select 'Extras' > 'Other tools' > 'Call Hierarchy'.

Note that:

• The 'calls' are static design-time calls … the Call Hierarchy command does not provide a view of the run-time call stack.

• If any 1 or more calls are recursive then a "Recursive" column will be shown in the upper and lower lists indicating which calls are recursive.

• If you are working across multiple VBA Projects with 'Project References' linking them:

  • If the VBA Project containing the procedure (that Call Hierarchy is shown for) has 'Project References' of other (child) VBA Projects, it will find calls to procedures in that child Project but if you then drill-down to a procedure in that child Project then the Call Hierarchy command will not find calls from that procedure to its child Projects (i.e. to grand-child Projects of the initial Project).

  • If the VBA Project containing the procedure (that Call Hierarchy is shown for) is referenced by other (parent) VBA Projects, it will find calls of the procedure in that parent Project but if you then drill-down to a procedure in that parent Project then the Call Hierarchy command will not find calls of that procedure from its parent Projects (i.e. from grand-parent Projects of the initial Project).

• I've referred to 'procedures' (i.e. Subs and Functions) throughout this post, however, the Call Hierarchy command does also work for Properties.