<

How to Time Code

Published on
16,262 Points
6,562 Views
12 Endorsements
Last Modified:
Approved
Martin Liss
Over 40 years of programming experience. Expand my "Full Biography" to see links to some articles I've written.
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!
12
Comment
Author:Martin Liss
2 Comments
LVL 62

Expert Comment

by:Bill Prew
How about using Timer() in VBS?

~bp
0
LVL 53

Author Comment

by:Martin Liss
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.
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month