PerformanceClass.vba

An Excel VBA performance class.

This is essentially a republish of Jan Karel Pieterse’s article https://jkp-ads.com/Articles/performanceclass.asp. So all credits go to him!

The main reason for this repository is to bundle all improvements at one point.

Features

• Measure the run-time of subs/functions,

• including sub-subs/sub-functions, and

• create a report as a workbook.

Prerequisites / Dependencies

Since this is for Excel VBA code you obviously need Microsoft Excel.

How to install / Getting started

First, to be able to measure the performance, you need to add the file(s) from the src folder to your project.

If you need some more advice on how to do that, expand me.

1. Open Microsoft Excel.

2. Open the Visual Basic Editor (VBE) (Alt+F11).

3. Add the file(s) in the src folder to your VBA project.

   • With Rubberduck:

     1. Right-click on the project to which you want to add the file(s) in the “Code Explorer” (to show it press Ctrl+R) and click on Add › Existing Files…​.

     2. Select all files in the src folder and click on Open.

   • Without Rubberduck:

     1. Select all files in the src folder in Windows File Explorer.

     2. Drag-and-drop them to the corresponding project in VBE’s “Project Explorer”.
        (To show it press Ctrl+R. Hit it twice if the Code Explorer shows up first.)

4. Check, if there are obvious errors by compiling the project (Debug › Compile ‹project name›).

5. Save the file/project.

   1. Be sure that the file/project you want to save is “active” in the VBE by checking, if its name is shown in VBE’s title bar.
      (If it’s not, open a (class) module of the corresponding project (and close it again).)

   2. Press the “Save” button (the disc symbol similar to 💾) in VBE’s toolbar.

   3. Check that the file (really) was saved by having a look at the “last modified date” of the (project) file in the Windows File Explorer.

Next, you add the “measurement” code to the procedures you want to measure.

ℹ️	Ensure that you put the code after stuff that needs user interaction like message or input boxes. Otherwise you will just measure how fast the user responds to these boxes instead of the runtime of the (rest) of the code.

1. Add the lines

       Dim cPerf As clsPerf
       Set cPerf = MeasureProcedurePerformance("Master", True)

   near the top of the “master” procedure, i.e. the procedure that is the entry point/start point, and rename “Master” to the name of the actual procedure.

2. Add

       Application.OnTime Now, "modPerf.ReportPerformance"

   at the exit point of the “master” procedure when you “automatically” want to call the performance report.

3. Add the lines

       Dim cPerf As clsPerf
       Set cPerf = MeasureProcedurePerformance("Client")

   near the top of all the “client” procedures, i.e. the procedures that you want to track as well, and rename “Client” to the name of the actual procedures.

Last, to really measure the performance, you need to “activate” the measurement. To do so

• set the global variable gbDebug in the modPerf module to True.

❗	Without this last step, everything should work as without the addition of all the above codes, i.e., as we never would have added some performance measurement code.

Usage / Show it in action

If you have followed the instructions given in the How to install / Getting started section, running the “master” procedure will be trigger the performance measurement(s). A new workbook will open when “master” procedure has finished where you can inspect the results.

In case you want to first see code in action before modifying our own procedures, you can have a look at the tests folder. There you will find a test a test file. Follow the given instructions.

Running Tests

Unfortunately we don’t know how to create automated tests/unit tests for this project. If you have an idea, we would love to see it! Please add an issue or – even better – a pull request (see the Contributing section).

But of course one can manually test it. Please have a look at the tests folder.

Used By

Currently we can’t remember of some public code where this code is used 😞. If you know some, we will be happy to add them here.

Known issues and limitations

None that we are aware of.

Contributing

All contributions are highly welcome!!

If you are new to git/GitHub, please have a look at https://github.com/firstcontributions/first-contributions where you will find a lot of useful information for beginners.

We recently were pointed to https://www.conventionalcommits.org which sounds very promising. We will use them from now on too (and hopefully don’t forget it in a hurry).

FAQ

1. What are the '@... comments good for in the code?

   You should really have a look at the awesome Rubberduck project!

License

MIT