Quick Start VBA Profiling

The FastExcel VBA Profiler provides an easy-to-use way of finding out which parts of a VBA solution are using the most execution time. The profiler works by adding timing calls to your VBA. These timing calls store timing information whilst your VBA code runs. Then a profiling report can be produced showing the slowest parts of the VBA first. The VBA Profiling commands are available from:

The Ribbon

The ProfVBA Toolbar in the VBE

The ProfVBA Menu in the VBE Addins Menu

The 4 Step VBA Profiling VBA Process

“Add Profiler Calls”  “Enable Profiling”  run your VBA  “Show Profiling Report"

Profiling your VBA

1.Make sure that you have enabled Access to the VBA Object Model (File->Options->Trust Centre->Settings->Macro Settings)

2.Use the VBIDE (Alt-F11) to unprotect the VBA you want to profile.

3.Use “Add Profiler Calls” to choose which projects, modules and procedures to Profile.

4.Enable Profiling

5.Run the VBA code you want to profile

6.“Show Profiling Report” to create the profiling report worksheet.

Drilling down within VBA Procedures

There are 3 different ways you can drilldown the profiling to within a specific VBA Procedure.

1.To profile a particular block of VBA statements, select them in the VBA and then use “Profile Statements”.

2.To profile all the Loops in a particular VBA Procedure, select any line in the procedure that contains the Loops, then click “Profile Loops”

3.Use “Add Profiler Calls”, select a Project, Module and Procedure then choose “Every Nth line of Proc”

Cleaning Up

Don’t forget to “Remove Profiler Calls” to clean up your VBA!

Note:

The FastExcel timing buttons will work with VBA Profiling so that UDFs and VBA calculation event code can be profiled, but the FastExcel Profiling commands ignore VBA profiling because they trigger multiple calculation events.

Copyright © 2022 Decision Models Ltd.