Tracing Function Calls in VBA

Today’s guest blogger is Joel Graff. Joel is a field engineer with the Illinois Department of Transportation (IDOT).

Often, I find when looking for the source of an error in VBA code, a function call trace which returns the path of function calls preceding the error to be invaluable. While more complete compilers provide this feature as a part of the IDE, it is, unfortunately, absent in VBA. To accommodate this, I’ve written my own function call stack classes to help me trace errors. This implementation is based on something I found online, though I have since lost the reference. I cannot give credit where credit is due, only note that the basic implementation is at least somewhat borrowed.

There are two classes I use:  cErrStack and cErr.

cErrStack – Instanced as a global object, this provides the stack for procedure names as they are called. Procedures are added / removed (pushed / popped) from the stack as they come in and go out of scope. When an error occurs, the stack then contains, in order, the procedures called up to the procedure in which the error occurred. The cErrStack object returns the procedure name in which the error occurred and the trace path of function calls preceding the error with the methods ErrorProcedure() and Trace(). For higher-level error handling (where error handling occurs at the form level or only in main procedures), the ErrorProcedure() and Trace() routines will not provide correct information. In these cases, ErrorProcedureHistory() and TraceHistory() should be used instead.

cErr – Instanced at the procedure level, this object pushes the name of the procedure on the global error stack object (the cErrStack object). When the procedure terminates, the cErr object goes out of scope, triggering its Terminate() Event. This event then removes (pops) the terminating procedure’s name from the global error stack.

A sample error message may look like this:

Often, when I deploy a new database that hasn’t had all the bugs worked out, I write my error handling routine to automatically open an email and paste the above information into it. That way, users can send me the relevant error information automatically. I also use line numbers to help better identify problem code, though I didn’t implement them in this example.

The original implementation only allowed for trace information to be available in the procedure in which the error occurred. That is, if an error occurred in a function with no error handling, then by the time the error was passed back to the procedure which did handle it, the stack would no longer have accurate trace information. However, as one Access user pointed out to me, adding error trapping code to every function creates a lot of code bloat and isn’t really necessary. Better code practices suggest use of “high-level” error trapping – trapping in main functions or at the form level. To accommodate “high-level” error trapping, I added a second stack to the cErrStack class, which contains a “trace history”.

The idea is simple: When an error occurs in a function with no handling, the error is passed up the line until it is handled. That means that once an error occurs in higher level error handling, a “history” needs to be preserved until the error is handled. The class object does this by copying the contents of the immediate stack to the history stack when a “pop” occurs, and simply reassigning the history stack to the immediate stack when a “push” occurs. Thus, when an error occurs and is passed up the line to the handling procedure (a series of “pops” occur on the immediate stack), the history stack is preserved. Once a push occurs on the immediate stack (which presumably won’t happen until after the error is handled) the history stack is destroyed and referenced to the immediate stack.

Attached is a sample of how the application works. There are three scenarios. The first two (“Lefthand Error” and “Righthand Error”) are “immediate” error handling examples. The last one is a high-level error handling example.

Click the icon below to download the sample classes.

Send your Power Tips to Mike and Chris at accpower@microsoft.com.