I’m making available a beta version of my VBA performance profiler for people to try it out and let me know what they think. I’m interested in seeing what kinds of VBA code trip it up and produce syntax errors.
It adds profiling code to your VBA project which collects procedure timing and code coverage statistics. Here is what the Help form shows:
Open the file XLTEST_VBA_PROFILER.xlam and enable macros, or install it as an add-in.
Open the workbook you want to profile. Save it under a different name to avoid overwriting the original.
On the Developer tab, in the VBA Profiler group, click the button “Profiling”.
This gives a form in which you can specify whether you wish to:
1. Add a call to every line of code to trace its execution for coverage analysis.
A tab-delimited text file is output of all the code after trace lines have been added
so that the Sub VBA_Profile_Reports can show what lines of code were executed.
The text file is named from your workbook name suffixed with _VBA_COVERAGE.TXT
2. Collect Profile timing data (needed to produce profile reports)
If unchecked, it sends the output to Outputdebugstring.
You can watch that output using TraceSpy (https://tracespy.codeplex.com/)
To be sure that TraceSpy can keep up with the output, do not use Auto-Scroll.
3. Export all the VBA to a text file before making the changes.
This is just for your record, it is not used in this process.
The tab-delimited text file is named from your workbook name suffixed with _VBA_BEFORE.TXT
When you click OK, it adds a line to every procedure in the project, unless its declaration line contains a comment ‘@notrace
After it is finished, go into the VBE and click Compile to check that no syntax errors have been introduced. If you get a Compile error: Variable not defined VBA_Profiler then you need to add the Tools Reference to VBA_Profiler although that should have been automatically added by the profiler.
If you checked Profile timing above, this line looks like this:
Dim P_ As Object: Set P_ = VBA_Profiler.New_Profiler(): P_.Start 12345, “modulename”, “procname”
Otherwise it looks like
Dim P_ As Object: Set P_ = VBA_Profiler.New_Debugger(): P_.Start 12345, “modulename”, “procname”
The way that works is that when the procedure exits and the variable P_ goes out of scope, the profiler counts up the number of calls and the length of time the procedure took. The resolution of the timer is about a microsecond.
If you checked code coverage above, it inserts a call at the beginning of every exceutable line like this:
In the examples above 12345 is a unique line number in the project, corresponding to lines in the coverage file.
Finally, insert a test sub which initialises the profiler, calls your main line procedure, and then produces reports.
Sub testMyProc VBA_Profile_Initialize ThisWorkbook MyProc VBA_Profile_Reports End Sub
VBA_Profile_Reports outputs a tab-delimited profile report named from the workbook name suffixed by _VBA_PROFILE.TXT;
and if you do the coverage analysis, a workbook is created with one sheet containing the contents of the _VBA_COVERAGE.TXT file with an extra column added to show the number of times a line of code was executed.
The workbook is unsaved, you can save it as a .xlsx file if you wish to keep it.
Let me know what you think!