A high resolution timer in VBA

VBA’s own timer function is useful when you need to time some code that takes seconds or minutes to execute, but often you need to test the performance at smaller intervals, perhaps milliseconds or even microseconds.

VBA doesn’t provide a function with resolution down to this level, so we need to use the performance counter provided by the Windows API. The performance counter provides a high-resolution time-stamp that goes down to the microsecond level, i.e. one millionth of a second.

The two useful functions are QueryPerformanceCounter and QueryPerformanceFrequency

QueryPerformanceCounter returns the current value of the computer’s performance counter. This will be whole number representing the number of “counts”. You can think of this as the number of ticks on a very fast clock.

QueryPerformanceFrequency returns the number of “counts” there are per second, which is fixed at system boot.

Both of these functions take a LARGE_INTEGER as an argument, which is a 64-bit integer. VBA doesn’t have a 64-bit integer, the VBA long integer has only 32-bits. Luckily, we can use the often-overlooked VBA currency data type, which is 64-bit. Whereas integer and long store numbers to the nearest whole number, currency stores numbers to the nearest 10,000th of a whole number. If we use it where the API expects an integer type the result will be scaled down by a factor of 10,000. This isn’t a problem as long as we remember to scale it up again.

Here’s some example code to show how they can be used. Note that when calculating timeElapsed both the numerator and denominator are scaled down by a factor of 10,000. The scale factor therefore cancels out and gives us a result in seconds

Private Declare Function getFrequency Lib "kernel32" _
      Alias "QueryPerformanceFrequency" ( _
      ByRef Frequency As Currency) _
      As Long

Private Declare Function getTime Lib "kernel32" _
      Alias "QueryPerformanceCounter" ( _
      ByRef Counter As Currency) _
      As Long

Sub timeSomeCode()

   Dim startTime As Currency
   Dim endTime As Currency
   Dim perSecond As Currency
   Dim timeElapsed As Double

   getFrequency perSecond

   getTime startTime

   '####################################
   '# Insert Your Code Here
   '####################################

   getTime endTime

   timeElapsed = (endTime - startTime) / perSecond

   Debug.Print "Code took " & timeElapsed & " seconds to run"

End Sub