Friday, October 29, 2010

Profiler for VBA code in Access

I found a nice little stack style Profiler for VBA from a book called Access Cookbook. Its been very handy for identifying and tracking down a couple of memory leaks (My fault as usual) The Profiler is in a module called basProfiler and is discussed in an article called "Create an Execution Time Profiler" which has been reprinted all over the web. I had to fix a small bug in the code that formatted the output to the log file. Other than that it was good to go out of the box.

My usage of basProfiler

1. Import the module.
2. Set the path to the log file to somewhere conveinent. (I put mine into my working directory for subversion to manage)
2. Instrument the code blocks with the opening and closing lines.

Public Sub Whatever()
     acbProPushStack "Whatever" 'profiler
         'code to be profiled here
     acbProPopStack 'profiler
End Sub

I open the log file in Notepad++ on the other monitor and every time I run the code, Notepad++ detects that the file has changed on disk and allows me to reload it. This gives me the power of a real text editor rather than a log window to play with the output.

Simple and elegant. Just the way I like my tools.

I considered hacking it to output a comma delimited file for import into excel for analysis but I ended up not needing that sort of power on this job.

And we are done.

No comments:

Post a Comment