How to Time Code

Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Almost 50 years of programming experience. Click '+ More' in my "Full Biography" to see links to some articles I've written.
Published:
Updated:
There is often more than one way to accomplish a particular coding task and one of the criteria that can be used to determine which method to use is to determine which one is the fastest.  But how do you do that? You could try using displays of Visual Basic's Now() or Time() functions, and doing that will get you some results but they won't be very accurate since the accuracy of those functions is only about 1 second and that is huge compared to the time it takes to run a line, or even in most cases, thousands of lines of code. To improve on that you'll need to use an API. Some of the API possibilities are GetTickCount, TimeGetTime and QueryPerformanceCounter. Here's a chart of the commonly used methods and their accuracy.

Accuracy
As you can see, GetTickCount and TimeGetTime are a hundred times more accurate than the VB functions and 10 ms is likely fine enough for any code that you want to time. QueryPerformanceCounter is much more accurate than that in that it can measure actual CPU cycles but most programmers will never need to do that.

Here's a VB6 example that uses GetTickCount. In this case a comparison is done between 'Test1' where the Array function to fill an array, and 'Test2' where a more manual approach is used.  

Each test uses a loop that repeats an action the same, large, number of times. The use of the large number of loops magnifies the results of the test and that is necessary because an individual line of code can be almost too fast to measure. It also helps smooth out small differences caused by random processes going on in the background.

When you run this example you'll find the second method is faster than the first and that's because Test1's Array function requires that the target array be of type Variant, and Variants are slow (and large). Some people would still use the first because it requires less coding. In my opinion that's short-sighted because while you only write code once, the code may get executed thousands of times.

 
Option Explicit
                      Private Declare Function GetTickCount Lib "kernel32" () As Long
                      Private Sub Command1_Click()
                          ' Place this code in any Sub or Function
                          Dim lngStart As Long
                          Dim lngFinish As Long
                          Dim lngCounter As Long
                          Dim varArr As Variant
                          Dim strArr(2) As String
                          
                          'TEST1
                          ' Record the start "time"
                          lngStart = GetTickCount()
                          
                          ' Some process that you want to time
                          For lngCounter = 1 To 100000
                              varArr = Array("A", "B", "C")
                          Next
                          
                          ' Record the finish "time"
                          lngFinish = GetTickCount()
                          
                          ' Display the difference
                          Debug.Print "Test 1: " & CStr(lngFinish - lngStart)
                          
                          'TEST2
                          ' Record the start "time"
                          lngStart = GetTickCount()
                          
                          ' Some process that you want to time
                          For lngCounter = 1 To 100000
                              strArr(0) = "A"
                              strArr(1) = "B"
                              strArr(2) = "C"
                          Next
                          
                          ' Record the finish "time"
                          lngFinish = GetTickCount()
                          
                          ' Display the difference
                          Debug.Print "Test 2: " & CStr(lngFinish - lngStart)
                      
                      End Sub
                      

Open in new window


And here is an example using VBA in Excel where a comparison is made between two different ways to fill a range.

 
Private Sub CommandButton1_Click()
                      
                          ' Place this code in any Sub or Function
                          Dim lngStart As Long
                          Dim lngFinish As Long
                          Dim lngCounter As Long
                          
                          'TEST1
                          ' Record the start "time"
                          lngStart = GetTickCount()
                          
                          ' Some process that you want to time
                          For lngCounter = 1 To 30000
                              Range("A" & lngCounter).Value = lngCounter
                          Next
                          
                          ' Record the finish "time"
                          lngFinish = GetTickCount()
                          
                          ' Display the difference
                          Debug.Print "Test1: " & CStr(lngFinish - lngStart)
                          
                              
                          'TEST2
                          ' Record the start "time"
                          lngStart = GetTickCount()
                          
                          ' Some process that you want to time
                          For lngCounter = 1 To 30000
                              Cells(lngCounter, 1).Value = lngCounter
                          Next
                          
                          ' Record the finish "time"
                          lngFinish = GetTickCount()
                          
                          ' Display the difference
                          Debug.Print "Test2: " & CStr(lngFinish - lngStart)
                      
                      End Sub
                      

Open in new window


Finally, I never actually use it, but here's a QueryPerformanceCounter example.
 
Option Explicit
                      Private Declare Function QueryPerformanceCounter Lib "Kernel32" (X As Currency) As Boolean
                      Private Declare Function QueryPerformanceFrequency Lib "Kernel32" (X As Currency) As Boolean
                      
                      Private Sub CommandButton1_Click()
                      Dim frequency As Currency
                      Dim curStart As Currency
                      Dim curFinish As Currency
                      Dim lngCounter As Long
                      Dim varArr As Variant
                      ' Get the frequency counter
                      ' Return zero if hardware doesn't support high-res performance counters
                      If QueryPerformanceFrequency(frequency) = 0 Then
                          MsgBox "This computer doesn't support QueryPerformanceCounter"
                          Exit Sub
                      End If
                      ' start timing
                      QueryPerformanceCounter curStart
                      ' Code to be timed
                      For lngCounter = 1 To 100000
                          varArr = Array("A", "B", "C")
                      Next
                      ' end timing
                      QueryPerformanceCounter curFinish
                      
                      Debug.Print (curFinish - curStart) / frequency
                      
                      End Sub
                      

Open in new window


If you find that this article has been helpful, please click the “thumb’s up” button below. Doing so lets me know what is valuable for EE members and provides direction for future articles. It also provides me with positive feedback in the form of a few points. Thanks!
13
8,771 Views
Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Almost 50 years of programming experience. Click '+ More' in my "Full Biography" to see links to some articles I've written.

Comments (2)

Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Distinguished Expert 2020

Commented:
How about using Timer() in VBS?

~bp
Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2022

Author

Commented:
I can't say one way or the other for VBA since my main experience is with VB6 but Microsoft says the resolution of the Timer function is just 1 second.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.