Debug and trace in VBA

Often when you debug your Excel app you want to monitor execution. Unfortunately the VBE has no built in trace tools. In this post I describe a simple but effective trace utility.

Whenever I am developing a non trivial excel application or a more or less complex userform I want to monitor execution of the code: which procedures are called, which events are raised, in what sequence. In Visual Studio we can set tracepoints. Unfortunately the VBE has no built in trace functions, other than the Call Stack. The call stack gives a trace of all functions that were executed in the current event, but doesn’t show information about the history: events that were handled before.
A simple solution is to add a Debug.Print statement to every function/event you want to monitor. Let this command print the name of the procedure to the VBE Immediate Window, and you have a basic but complete trace of all functions that need monitoring.
Instead of a simple Debug.Print we can call our own procedure, like this:

Private Sub UserForm_Initialize()
    LogStart "UserForm_Initialize", Me.Caption
    ' your code
    LogPrint "Half way"
    ' more code
    LogEnd
End Sub

When we add the LogStart and LogEnd to all userform events and then open the userform the resulting trace list could look like this:

72624,82  UserForm_Initialize UserForm1
72625,32      Half way  
72625,32  TextBoxA_Enter   
72625,33  UserForm_Activate   

The line number is the Timer. It helps to analyze the performance of our app. In the example it took the userform 0.5 seconds to get “half way”. Maybe reason to check “your code”?

The module LogTrace has three procedures:

• LogStart: prints the parameters to the VBE Immediate Window and indents a level;
• LogEnd: prints “End” and its parameters and unindents one level;
• LogPrint: prints its parameters at the current level.

Every procedure has a few optional parameters. Usually you will start every function with LogStart “ProcedureName” and end the procedure with LogEnd. Tip: use a tool like MZ-Tools to insert these lines in your code easily.

A few contstants in the module make it easy to customize the layout of the trace list:

• bNOTRACE: turns the trace function on or off completely;
• bNOEND: print or don’t print “End” lines to the VBE Immediate Window (a condensed mode);
• bNOPRINT: print or don’t print the LogPrint lines;
• bNOTIMER: print or don’t print the line number;
• iDELAY: the number of seconds between two Log commands after which a blank line is printed.

Let me know which options you miss. To see this utility at work: The sequence of events in workbooks
Finally note that the buffer of the VBE Immediate Window has a limit of 199 lines. If you need more you could easily redirect the output to a textfile or a worksheet.
I hope this pixcel helps. Excel!

Download

DebugAndTrace-v1.xlsm