There are many times when you want to know how fast your code really is. Especially if you find your VBA application responding slowly you need to know where the bottleneck is occurring. You can build a Stopwatch type class around the built-in VBA Timer function, but this will only get you resolution up to a few milliseconds and also suffers from some quirks in the Timer function. I won’t cover those in this article, but I will show how to make a super accurate Stopwatch class using the Windows API functions QueryPerformanceFrequency and QueryPerformanceCounter. For an in-depth look at these functions, see the MSDN Magazine article Implement a Continuously Updating, High-Resolution Time Provider for Windows.

High-resolution performance counters

The performance counter API functions provide access to the high-resolution performance counter on your computer. Performance counters are implemented directly in hardware on the CPU (see Hardware Performance Counter). Performance counters don’t actually measure time, instead they just increment a counter at a very small and very precise interval. Performance counters are not perfectly accurate for measuring calendar time over the long term but they are extremely precise and regular. You can count on it always taking exactly the same amount of time to “tick”, say, 10000 times, to a precision on the order of nanoseconds.

That said you usually want to convert ticks to time. In order to do this you need three pieces of information: the start counter value, the end counter value, and the counter frequency. QueryPerformanceCounter provides the the counter value, while QueryPerformanceFrequency tells us how many times the counter increments its count per second. This allows us to convert the number of ticks elapsed to seconds elapsed. On my system the counter frequency is 2,922,431. Yes, that’s more than 2 million ticks per second, or a tick length of 342 nanoseconds. Compare this to the tick rate of the VBA Timer function, which on my computer is 256 ticks per second, or a tick length of 3.91 milliseconds. Put another way, my hardware performance counter ticks about 11,416 times for every tick of the system clock represented by the Timer function.

Declaring the API functions

Both QueryPerformanceFrequency and QueryPerformanceCounter use unsigned 64-bit integers. This data type is not available in VBA natively, so we first have to define our own type to hold the data passed from the API functions, along with a helper function to convert the raw unsigned 64-bite integers to VBA Doubles:

Uint64 Type

The Declare statements for the functions themselves are simple. Note the argument for each function is implicitly ByRef, and uses the user defined type just created:

Measuring time elapsed

Here’s how you would use these API functions directly to measure the time it takes to do something:

Creating a Stopwatch class

We can make these methods a lot more useful by wrapping them in a Stopwatch class. This will hide the API complexity from the rest of the code while adding some handy intuitive methods. Here’s the complete code for my Stopwatch class. Paste it in a new class module call Stopwatch and you’re good to go:

Using the Stopwatch class

Now it’s easy to accurately measure time. Here’s the updated version of the example above, using the Stopwatch class instead of the direct API functions:

Conclusion

Using a couple of Windows API functions you can use your hardware performance counter to accurately measure extremely short time intervals. This is helpful when profiling and tuning your code. Going an extra step and wrapping these function in a class module makes them even easier to use throughout your project.