Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®

The PerfMon Utility

The PerfMon utility is a set of three DLLs that enable us to monitor and record the performance of a VBA application as it is executing. It achieves this by adding a line of code to the top and bottom of every procedure to notify the monitoring DLL when the procedure starts and finishes. The DLL records the number of times each procedure is called and the maximum, total and average time spent in each. The result is a complete list of all the procedures called and the detailed timings for each, either copied to the clipboard or saved to a text file. Once imported into an Excel worksheet and sorted by total time, the result looks something like Figure 17-1.

Figure 17-1. An Example of the PerfMon Results

We can immediately see the first procedure accounts for nearly the entire processing time and is therefore where we should focus our optimization efforts.

The setup program for the PerfMon utility can be found in the \Tools\Performance Monitor directory on the CD and installs the following DLLs:

PerfMonitor.dll An ActiveX DLL that uses the Windows high-performance counter to track the performance of each routine. It is listed in the Project > References dialog as PerfMon: VB/VBA Performance Monitor.

PerfMonOffice.dll An add-in for the Office VBE to add and remove the calls to the PerfMonitor DLL.

PerfMonVB6.dll An add-in for the VB6 IDE to add and remove the calls to the PerfMonitor DLL.

The setup program also installs the CPerfMon.cls file, which is a class module that can be included in a VB6 project to enable cross-process performance monitoring for use during development of combined Excel/VB6 solutions (see Chapter 20 Combining Excel and Visual Basic 6).

To start using the utility, click on Addins > PerfMon > Add PerfMon Calls and select which procedures to add the calls to, as shown in Figure 17-2.

Figure 17-2. The Add PerfMon Calls Dialog

When you click OK, the utility will add a reference to the PerfMonitor DLL and add calling code to the top and bottom of the selected routine(s), as shown in Listing 17-1.

Listing 17-1. A Procedure with the Automatic PerfMon Calls Added

Sub ALengthyRoutine()
PerfMonProcStart "PrjChapter17.MPerfMon.AlengthyRoutine"
'Do something lengthy
PerfMonProcEnd "PrjChapter17.MPerfMon.AlengthyRoutine"
End Sub

Note that every procedure is given a unique ID, being the concatenation of the project name, module name, procedure name and property type (if it is a property procedure). If you have a particularly long procedure that would be better monitored in separate blocks, extra PerfMon calls can be added manually, taking care to match the ProcStart and ProcEnd calls, as shown in Listing 17-2.

Listing 17-2. A Procedure with Manual PerfMon Calls Added

Sub ALengthyRoutine()
PerfMonProcStart "PrjChapter17.MPerfMon.AlengthyRoutine"
PerfMonProcStart "PrjChapter17.MPerfMon.AlengthyRoutine1"
'Do something lengthy
PerfMonProcEnd "PrjChapter17.MPerfMon.AlengthyRoutine1"
PerfMonProcStart "PrjChapter17.MPerfMon.AlengthyRoutine2"
'Do something else lengthy
PerfMonProcEnd "PrjChapter17.MPerfMon.AlengthyRoutine2"
PerfMonProcEnd "PrjChapter17.MPerfMon.AlengthyRoutine"
End Sub

The last thing to do is to add a line to tell the utility when to start and stop monitoring, as shown in Listing 17-3.

Listing 17-3. Include the Calls to Start and Stop the Monitoring

Sub ALengthyRoutine()
'Start monitoring all procedures from here
PerfMonStartMonitoring
PerfMonProcStart "PrjChapter17.MPerfMon.AlengthyRoutine"
PerfMonProcStart "PrjChapter17.MPerfMon.AlengthyRoutine1"
'Do something lengthy
PerfMonProcEnd "PrjChapter17.MPerfMon.AlengthyRoutine1"
PerfMonProcStart "PrjChapter17.MPerfMon.AlengthyRoutine2"
'Do something else lengthy
PerfMonProcEnd "PrjChapter17.MPerfMon.AlengthyRoutine2"
PerfMonProcEnd "PrjChapter17.MPerfMon.AlengthyRoutine"
'Stop monitoring and write the results to a file
'If no file name given, the results will be put on the clipboard
PerfMonStopMonitoring "c:\MyRoutineTiming.txt"
End Sub

The easiest way to analyze the results is to start a new Excel session, click Data > Get External Data > Import Text File (Excel 2000) or Data > Import External Data > Import Data (Excel XP/2003), select the text file that you gave in the PerfMonStopMonitoring call and click through the Text Import Wizard. It is better to import the data instead of just opening the file, because the latter locks the file and the PerfMon monitor will not then be able to overwrite it with new results for each subsequent run. We can also set the import to use the same filename each time, allowing us to re-import the new results by clicking the Refresh button, shown in Figure 17-3.

Figure 17-3. Importing the File Allows Us to Quickly Refresh the Data to Get the New Results