Creating a Call Stack in VBA

VBA does not give you access to the call stack, which is a source of great pity to VBA developers. Nonetheless, it is possible to create your own call stack that can be called by your error handler.

Create a new class called ‘StackItem’ which contains the following code:

Public Value As String
Public NextItem As StackItem

As you can see each item in the stack has two properties only: a ‘Value’ that contains a string, and a reference/pointer, ‘NextItem’, to the next item on the stack. Two lines of code is probably the smallest class definition you’ll come across but it’s all we need. What we will be doing is, each time we enter a new procedure, we create a new StackItem and set its Value equal to a string being a concatenation of ModuleName and ProcedureName. At the same time, we’ll be assigning the memory location of the StackItem that is currently on top of the stack, to the reference NextItem. We do all of this in a separate class.

Create another new class called ‘Stack’ and add the following code to its definition:

Private TopItem As StackItem

Public Sub Push(ByVal str As String)

     Dim NewTop As StackItem
     Set NewTop = New StackItem

     NewTop.Value = str
     Set NewTop.NextItem = TopItem
     Set TopItem = NewTop

End Sub

Here we’re doing two things: creating a reference ‘TopItem’ for the StackItem object that is at the top of the stack, and creating the ‘Push’ method that allows us to push a new StackItem on to the stack. You can see that, in creating the new TopItem, we set its ‘NextItem’ property to point to the next item in the stack.

Having put items on the stack, we need to have some way of pulling items off the stack. Add the following methods to the ‘Stack’ class:

Private Property Get StackIsEmpty() As Boolean

     StackIsEmpty = (TopItem Is Nothing)

End Property

Public Function Pop() As String

     If Not StackIsEmpty Then
          Pop = TopItem.Value
          Set TopItem = TopItem.NextItem
     End If

End Function

The ‘Pop’ function returns the ‘Value’ for the StackItem that is at the top of the stack and it resets the reference ‘TopItem’ to point to the next StackItem on the stack. The StackItem just popped, having no references to it (ie. reference count = 0), is released from memory.

And that’s the basics of the stack. To use it to replicate the VBA IDE’s Call Stack (View > Call Stack) you need to do the following:

In a Standard Module, declare a global variable of data type Stack:

Public myCallStack as Stack

In your entry-procedure, instantiate a Stack object and reference it with the global variable:

Set myCallStack = New Stack

And then, immediately push the string reference you want onto the stack:

myCallStack.Push "Module1.MySubProc1"

Where ‘Module1.MySubProc1’ is the name of the Standard Module and Procedure in you code.
The last thing done in the same Procedure is to remove the item from the stack:


Note that I’m not interested in the return value from Pop, so I’m not assigning it to any variable I would have, otherwise, created.

Every procedure in your code that’s called in the chain starting with your entry-procedure should follow the same process: add the ‘myCallStack.Push’ at the very beginning of the procedure and the ‘myCallStack.Pop’ at the very end of the procedure. For example, consider the following code taken from my Standard Module (Module1):

Public myCallStack As Stack

Public Sub Proc1()

     Set myCallStack = New Stack

     myCallStack.Push "Module1.Proc1"
     Call Proc2

End Sub

Private Sub Proc2()

     myCallStack.Push "Module1.Proc2"
     Call Proc3

End Sub

Private Sub Proc3()

     myCallStack.Push "Module1.Proc3"
     Call Proc4

End Sub

Private Sub Proc4()

     myCallStack.Push "Module1.Proc4"
     Call Proc5

End Sub

Private Sub Proc5()

     myCallStack.Push "Module1.Proc5"
     Debug.Print myCallStack.Unwind

End Sub

Reading through the above you’ll see I’ve referenced a method (‘Unwind’) in the Stack class that I haven’t yet documented. Here it is:

Public Function Unwind() As String

     Dim str As String
     Do Until StackIsEmpty
          str = Pop & vbNewLine & str
     Unwind = str

End Function

All this method does is unwind the entire stack into a single string that shows the call stack (top down). It is very useful for error handling, which I will now discuss.

Typically, I like to handle errors at the entry-procedure: if an error occurs further down the call stack, it is unhandled all the way up to the entry-procedure where it is handled. That way I only have to write one error handling routine (per entry-procedure). The new entry-procedure (Proc1) is:

Public Sub Proc1()
On Error GoTo Catch


     Set myCallStack = New Stack

     myCallStack.Push "Module1.Proc1"
     Call Proc2
     Goto Finally


     Dim str As String
     str = "NUMBER: " & vbNewLine & Err.Number & vbNewLine & vbNewLine
     str = str & "DESCRIPTION: " & vbNewLine & Err.Description & _ vbNewLine & vbNewLine
     str = str & "CALL STACK: " & vbNewLine & myCallStack.Unwind
     MsgBox str, vbCritical, "An Error has occurred"



End Sub

A few final points are worth mentioning here. I’ve put ‘myCallStack.Pop’ in the ‘Finally’ block. Instead, it could go at the end of the ‘Try’ block. It’s personal preference. Also, I haven’t explicitly destroyed the Stack object I created (Set myCallStack = Nothing). Instead, I’ve relied on the object going out of scope at the end of the entry-procedure in order for memory to be freed. I know DAO has some issues that required specific destruction of objects in order to release them from memory, but DAO is old technology and I use ADO wherever possible (and, what’s more, haven’t used either in this example).


1 comment

  1. Great write up! The only thing you may want to consider is that if you change a procedure name, you have to do it twice. If you forget, your call stack will not reflect your code. I recommend using something like this to update all of your calls to Push with something like this.

Leave a Reply to Christopher J. McClellan Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: