Finding VBA Bottlenecks with VBA Profiler

When you are faced with slow-running or complex VBA projects it can be very helpful to have a tool that allows you to time the individual parts of your code or trace the execution path as event-driven jumps occur.

Googling for VBA profiler shows several VBA Profiling solutions, but none of them really did what I was looking for. So I decided to develop my own VBA Profiler – FastExcel ProfVBA.

Acknowledgements

I am very grateful to Stephen Bullen and Rob Bovey for their help in providing some of the initial code: see pages 546-500 of “Professional Excel Development” ISBN 0-321-50879-3.

And thanks also to Don Soloway for his collaboration on the project. In particular he developed the code for profiling loops and for showing results in the Immediate window.

ProfVBA Design Objectives

The things I wanted the Profiler to do were:

• Make Profiler commands available both from the Excel Ribbon and the VBE
• Easy to use ways of automatically inserting and removing Profiler calls to/from VBA
• One-click drill-downs into chosen VBA Procedures
• Work with VBA Userforms, Subs, Functions and Classes
• Do both Profiling and Tracing
• Start and stop Profiling
• Generate a Profiling report that prioritised the slow parts of the code.

Inserting Profiler Calls in your VBA

To time your VBA statements calls to ProfVBA need to be inserted into your code. Start and End calls should surround the block of code you want to time.

Usually you start by adding start and end calls to every sub and function in a project.
You could do this by manually inserting the calls, but this is difficult for VBA projects with many subs and function.

So ProfVBA uses the VBE Object Model to add the calls to your VBA source statements.
https://docs.microsoft.com/en-us/office/vba/language/reference/visual-basic-add-in-model/objects-visual-basic-add-in-model#codemodule

Adding start calls is easy but many subs/functions have multiple exit statements and so ProfVBA needs to identify all these.

Clicking “Add Profiler Calls” shows a Form so that you can choose where to add the calls.

ProfVBA has added a Start call at the beginning of the sub and End calls before both the Exit Sub and End Sub statements.

This statement in FastExcel ProfVBA inserts a start call after the current line in the body of your code module.

                'Insert a line for the ProfVBAProcStart call
490             oCM.InsertLines lBodyLine + 1, "gfxlProfVBA.ProfVBAProcStart """ & sProcID & """"

After adding the calls:

• Enable Profiling
• Run your VBA
• Show the Profiling Report

To profile a particular action from a userform, click the enable profiling button then click the button on the form and then view the profiling report.

The report shows which procedures are using the VBA execution time.

Exclusive or Inclusive Time

Exclusive time means that the timings shown are the times used by the procedure exclude the times used by child procedures called by the parent procedure.

You can choose either Exclusive or Inclusive timing in the Enable Profiling Options form:

Drilling Down into Your VBA

Once you have discovered which procedures are using the most execution time you can drill down to find out the most expensive blocks of statements.

You can use the ProfVBA VBE Toolbar to do this:

Select the Procedure in the VBE

Click the Insert Profiler Calls button

This will automatically use the selected Procedure.
Then you can choose one of the 3 drill-down methods

• Every Nth Line: this inserts Profiler start and end calls around every n lines of VBA code.
• All Loops in Proc: This inserts Profiler start and end calls around all the loops in the proc.
• Every Iteration of the Loops: this inserts Profiler start and end calls inside each loop in the proc and tracks each iteration of the loop separately.

Thanks to Don Soloway for implementing the Profiling Loops feature.

Summary

FastExcel ProfVBA provides easy-to-use methods of profiling your VBA code to find out where the VBA execution time is being used.

You can download a trial version of FastExcel from here.

http://www.decisionmodels.com/FastExcelV4_Install.htm