Free Add-In to profile VBA speed and coverage

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:

INSTRUCTIONS

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:
P_.O 12345&:

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!

Advertisements

About Patrick O'Beirne, spreadsheet auditor

Patrick provides consultancy and training in spreadsheet development, auditing / testing and model review; and the Excel addin XLtest
This entry was posted in Excel/VBA, Software Testing. Bookmark the permalink.

2 Responses to Free Add-In to profile VBA speed and coverage

  1. cxw says:

    Thanks for the great tool.

    Your code snippet:

    `Sub testMyProc
    `VBA_Profile_Initialise ThisWorkbook
    `MyProc
    `VBA_Profile_Reports
    `End Sub

    Seems to only work with the (Americanised)
    `VBA_Profile_Initialize ThisWorkbook

    rather than
    `VBA_Profile_Initialise ThisWorkbook

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s