Millisecond capable timer in VBA

When debugging code in VBA, it can sometimes be useful to measure how long a particular action takes to complete. In-built functions such as Timer can return you a time as accurate as 1/64th of a second, but what if you need to compare times with a greater degree of accuracy?

The code you see below solves this problem by calling on an API function named ‘QueryPerformanceCounter’, which is capable of measuring times to a thousandth of a second. The code has been modified from the original snippet, which can be found here:

In order for the code to work, all you need to do is paste it into a normal code module.



Option Explicit
Private Declare Function QueryPerformanceCounter Lib "kernel32" (t As Currency) As Boolean
Private Declare Function QueryPerformanceFrequency Lib "kernel32" (t As Currency) As Boolean
Function GetTime() As Currency
 Static Freq As Currency
 Debug.Assert QueryPerformanceFrequency(Freq)
 QueryPerformanceCounter GetTime
 GetTime = GetTime / Freq
End Function
Sub CalcTime()
 Dim start As Currency, test1 As Currency, test2 As Currency
 Dim n As Long
 start = GetTime()
 '' First test code goes here:
 '' ---------------------------
 For n = 1 To 100000
 Cells(n, 1).Font.ColorIndex = 10
 '' ---------------------------
 test1 = GetTime() - start
 start = GetTime()
 '' Second test code goes here:
 '' ---------------------------
 Range("A1:A100000").Font.ColorIndex = 10
 '' ---------------------------
 test2 = GetTime() - start
 Debug.Print "Test 1: " & Format(test1, "0.000") & " seconds"
 Debug.Print "Test 2: " & Format(test2, "0.000") & " seconds"
End Sub


About Roger Smith

Roger joined the team in 2010, and works full time at Gravity as an intern. His main role is to keep the accounts organised and up-to-date, but he also has a heavy hand in assisting other staff members in times of heavy workload. He keeps our company communicating through regular updates of our blog, Facebook page, and Twitter account. When he's not doing either the accounts or the social media, Roger is also responsible for drafting some of our technical documentation.

, , , , , ,

No comments yet.

Leave a Reply