<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

How to Time Code

Published on
16,587 Points
6,887 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
Author:Martin Liss
2 Comments
LVL 63

Expert Comment

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

~bp
0
LVL 56

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

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month