Tracing & Profiling VBA speed with TraceSpy

I’m interested in measuring VBA performance. I add tracing calls to the routines so I get the time on entrance and exit. I also optionally add a call to trace every line executed, which I use when I get unexpected crashes (“Excel has stopped working”), so that I can see the line it crashed on. Of course, Debug.Print is no use in that so I use the Windows API OutputDebugString. I used to monitor that by DebugView but was disappointed to see when I examined the log that dbgview.exe missed some output. It looks like if one call – eg an entry into a fast sub – is followed too quickly by another, DebugView only reports the second output. That makes it impossible for me to calculate the elapsed time for each sub. So I looked around and found TraceSpy. It kept up very well. The only thing I had to do was to turn off its Auto Scroll. With auto-scroll it looked like its queue filled up and it would take a half hour to finally work through all the debug messages, and if VBA was too fast, it would miss the last half of them. So I also put a Sleep 0 after OutputDebugString to get Excel to give other processes a chance. It doesn’t need DoEvents, I’m not looking to process events in Excel itself, just to give TraceSpy some time. TraceSpy does not log to file but it is easy enough to click on the first log line, Shift+End to select to the end, Copy entire line, and paste into Notepad++ to be able to save it. My longest log files so far are 600,000 lines, so I have not yet hit the Notepad++ file size limit.

When I saw the results, I could see which functions were slow and needed optimising so that the overall execution speed would be improved.

About Patrick O'Beirne, Excel/VBA developer

Patrick provides consultancy and training in spreadsheet automation, data analysis, testing and model review; and the Excel spreadsheet auditing addin XLtest
This entry was posted in Software Testing, Uncategorized. Bookmark the permalink.

Leave a 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