VBA Code Tracer / Profiler

Simple VBA code tracing

To track down a problem with Excel crashing I needed to get a debug trace of every procedure so I could find which one was causing the crash and I could then single step through it with F8. The simplest method I found was to insert a line at the top of every procedure which initialises an object from a user-defined class.  There is a Start method to set the proc name as we can’t pass that to the class Initialize event. The inserted line to create an object variable named P_ looks like this, immediately after each Sub or Function declaration:

Sub MyProc(param1 as long,etc)
Dim P_ As New Profiler: P_.Start "MyProc"

When this object goes out of scope when the sub exits, its Terminate event is called to record the exit. The class methods simply OutputDebugString the procedure name and a 1 for a start or -1 for the exit. I use OutputDebugString because I can see what the output is in DebugView whereas with Debug.Print the immediate window is lost when Excel crashes. I could write to a text file but the need to close and reopen it every time is slower than DebugView which itself adds quite an overhead.
I have also developed the idea to add a tracing line to each numbered line of code to be able to track in greater detail, particularly when there are long loops where F8 is a tedious way to find the point of a crash. If there is interest, I may post that too.

Here is the VBA code to inject the tracing calls. Copy and paste the code into a standard module and add a reference in the VBA Editor to Microsoft Visual Basic for Applications Extensibility 5.3.

http://www.sysmod.com/modVBATracer.txt                  (Updated 8-Apr-16)

It outputs lines to DebugView which you can then save to a file. The lines look like:

00005595 17883.11132813 ,Module1.MyProc,1
00005596 17883.23456789 ,Module1.MyProc,-1
The first number is the DebugView sequence number, the next after a tab character is the time. The procedure name is prefixed by the module name, as there can be several procedures (especially event procedures) with the same name. The 1 marks the start of the proc and the -1 is the exit. You can analyse that data to see which procedure is called most often, or takes the longest.