Calling the stack dialog box in VBA is actually a very useful feature of the VBA Editor. This is what the Microsoft documentation says about it:
Displays a list of currently active procedure calls during break mode. When executing code in a procedure, that procedure is added to a list of active procedure calls. Each time a procedure calls another procedure, it is added to the list. Called procedures are removed from the list when execution returns to the calling procedure. Procedures called from the Immediate window are also added to the calls list.
How does it work and why is it useful?
Imagine having this code, throwing the well-known Stack Overflow error, translated as “Out of Stack Space” in VBA:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
Option Explicit Sub Main() Dim cnt As Long Sub1 cnt End Sub Sub Sub1(cnt As Long) cnt = cnt + 1 sub2 cnt End Sub Sub sub2(cnt As Long) cnt = cnt + 1 sub3 cnt End Sub Sub sub3(cnt As Long) cnt = cnt + 1 Debug.Print cnt ' If cnt > 20 Then ' Debug.Print cnt ' Exit Sub ' End If Sub1 cnt End Sub |
The code starts at Main(), and then starts calling sub1 > sub2 > sub3 until a stack overflow error number 28 appears (To avoid the error, uncomment the block in sub3) This is where the Stack Dialog Box becomes useful. With its help we can take a good look at the stack:
The shortcut Ctrl + L shows us that the Stack is pretty full:
And if you start clicking on the VBAProject.Modul1.sub3 lines, it would show you the line with the corresponding function and it would assign your cursor to it.
Well, if you are a fan of any modern dev. environment, it is not something which would make you say “WOW”, but still:
