Today, I wanted to discuss Timers.

How can we time the execution of a process so we can evaluate it against another process to optimize things?

Today, I will cover 3-4 approaches:

• DateDiff
• VBA Timer
• the winmm.dll timeGetTime
• the kernel32 QueryPerformanceCounter and QueryPerformanceFrequency

DateDiff

The DateDiff() function allows use to calculate the difference between two date/time values using the interval of our choosing.

So we can get the initial Date/Time (using Now()), run a process and then get the ending Date/Time and use DateDiff to calculate the elapsed time, thus the time the process took to complete.  The code could look a little like:

Sub DateDiff_Example()
    Dim dStart As Date
    Dim dEnd As Date
    
    dStart = Now()
    Debug.Print CreateObject("WScript.Network").ComputerName
    dEnd = Now()
    Debug.Print "Elapsed time (s): " & DateDiff("s", dStart, dEnd)
End Sub

This in turn would output a result of:

Elapsed time (s): 0

The problem with DateDiff that make it unsuitable for most cases is that its smallest interval is seconds and typically we need to calculate execution times in milliseconds, or even smaller intervals. So DateDiff will not be a reliable solution!

VBA Timer

Another built-in approach, which was brought to my attention by Ryan W in the comments below, is the built-in VBA Timer property.

An example of its usage looks like:

Sub Timer_Example()
    Dim Start                 As Single
    Dim Finish                As Single
    
    Start = Timer
    Debug.Print CreateObject("WScript.Network").ComputerName
    Finish = Timer
    Debug.Print "Elapsed time (s): " & Finish - Start
End Sub

This in turn would output a result of:

Elapsed time (s): 0

So similarly to DateDiff approach, the VBA Timer lacks the level of precision sometimes needed. In other scenarios, it did work nicely though. So to me, this also proved to be an unreliable solution.

winmm.dll timeGetTime

Another possible approach would be to employ the timeGetTime function.

Once again the concept is similar to the DateDiff approach in that we get an initial value, run a process, get a final value and substract them to determine the elapsed time the process took to run. It would look like:

#If VBA7 Then
    Private Declare PtrSafe Function timeGetTime Lib "winmm.dll" () As Long
#Else
    Private Declare Function timeGetTime Lib "winmm.dll" () As Long
#End If
Private m_lStartTime   As Long


Public Function ElapsedTime() As Long
    ElapsedTime = timeGetTime() - m_lStartTime
End Function

Public Sub StartTime()
    m_lStartTime = timeGetTime()
End Sub

and then we would use it by simply doing:

Sub timeGetTime_Example()
    Call StartTime
    Debug.Print CreateObject("WScript.Network").ComputerName
    Debug.Print "Elapsed time (ms): " & ElapsedTime()
End Sub

and it too would output a result of:

Elapsed time (s): 0

Sadly, just like DateDiff(), timeGetTime just isn’t sensitive/precise enough to truly be useful..

kernel32 QueryPerformanceCounter and QueryPerformanceFrequency

Which finally brings us to QueryPerformance APIs: QueryPerformanceCounter and QueryPerformanceFrequency!

These will allow us to get a satisfactory level of precision that will allow us to time even the fastest process.

Standard Module Approach

***** Updated Version *****
So if we want to implement it using a Standard Module with Subs and Functions, we would simply do:

#If VBA7 And Win64 Then
    ' 64-bit VBA
    Private Declare PtrSafe Function QueryPerformanceCounter Lib "kernel32" (ByRef lpPerformanceCount As LongLong) As Long
    Private Declare PtrSafe Function QueryPerformanceFrequency Lib "kernel32" (ByRef lpFrequency As LongLong) As Long
    Private m_CounterStart    As LongLong
    Private m_CounterEnd      As LongLong
#Else
    ' 32-bit VBA
    Private Declare Function QueryPerformanceCounter Lib "kernel32" (ByRef lpPerformanceCount As Double) As Long
    Private Declare Function QueryPerformanceFrequency Lib "kernel32" (ByRef lpFrequency As Double) As Long
    Private m_CounterStart    As Double
    Private m_CounterEnd      As Double
#End If

' Start the timer
Sub ProcessTimer_Start()
    #If VBA7 And Win64 Then
        If QueryPerformanceCounter(m_CounterStart) = 0 Then
            Debug.Print "QueryPerformanceCounter failed.", vbCritical
        End If
    #Else
        If QueryPerformanceCounter(m_CounterStart) = 0 Then
            Debug.Print "QueryPerformanceCounter failed.", vbCritical
        End If
    #End If
End Sub

' Stop the timer and return elapsed time in desired units
Function ProcessTimer_End(Optional sOutputFormat As String = "s") As Double
    Dim elapsed           As Double
    
    #If VBA7 And Win64 Then
        Dim freq              As LongLong
        
        If QueryPerformanceCounter(m_CounterEnd) = 0 Then
            Debug.Print "QueryPerformanceCounter failed.", vbCritical
            Exit Function
        End If
        If QueryPerformanceFrequency(freq) = 0 Then
            Debug.Print "QueryPerformanceFrequency failed.", vbCritical
            Exit Function
        End If
        
        elapsed = (m_CounterEnd - m_CounterStart) / freq    ' seconds
    #Else
        Dim freq              As Double
        
        If QueryPerformanceCounter(m_CounterEnd) = 0 Then
            Debug.Print "QueryPerformanceCounter failed.", vbCritical
            Exit Function
        End If
        If QueryPerformanceFrequency(freq) = 0 Then
            Debug.Print "QueryPerformanceFrequency failed.", vbCritical
            Exit Function
        End If

        elapsed = (m_CounterEnd - m_CounterStart) / freq    ' seconds
    #End If

    ' Output in requested format
    Select Case LCase(sOutputFormat)
        Case "s", "sec", "second", "seconds"
            ProcessTimer_End = elapsed
        Case "ms", "msec", "millisecond", "milliseconds"
            ProcessTimer_End = elapsed * 1000
        Case "us", "usec", "microsecond", "microseconds"
            ProcessTimer_End = elapsed * 1000000
        Case Else
            ProcessTimer_End = elapsed    ' default: seconds
    End Select
End Function

and then we could use it by doing:

Sub QueryPerformance_Example()
    Dim dElapsedTime    As Double
    
    Call ProcessTimer_Start 'Initialize
    Debug.Print CreateObject("WScript.Network").ComputerName
    dElapsedTime = ProcessTimer_End  'Measure
    Debug.Print "Elapsed time (s): " & dElapsedTime
End Sub

which will output something like:

Elapsed time (s): 0.0110879

So a high level of precision for us to work with.

Class Module Approach

Some developers always ask me about using Class Modules. So here’s one version of doing so.

'Copy/Paste the following into a Class Module and save it with the name cls_Timer
#If VBA7 And Win64 Then
    'for 64-bit
    Private Declare PtrSafe Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
    Private Declare PtrSafe Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long
#Else
    'for 32-bit
    Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
    Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long
#End If

Private m_cCounterStart         As Currency
Private m_cCounterEnd           As Currency
Private m_dFrequency            As Currency

Private Sub Class_Initialize()
    QueryPerformanceFrequency m_dFrequency
End Sub

Public Sub StartCounter()
    QueryPerformanceCounter m_cCounterStart
End Sub

Property Get TimeElapsed() As Double    ' milliseconds
    QueryPerformanceCounter m_cCounterEnd
    TimeElapsed = (m_cCounterEnd - m_cCounterStart) / m_dFrequency
End Property

Then we can use it by doing:

Sub QueryPerformance_Example1()
    With New cls_Timer
        .StartCounter
        Debug.Print CreateObject("WScript.Network").ComputerName
        Debug.Print "Elapsed time (s): " & .TimeElapsed
    End With
End Sub

or, by doing:

Sub QueryPerformance_Example2()
    Dim Mytimer               As cls_Timer
    
    Set Mytimer = New cls_Timer
    With Mytimer
        .StartCounter
        Debug.Print CreateObject("WScript.Network").ComputerName
        Debug.Print "Elapsed time (s): " & .TimeElapsed
    End With
    Set Mytimer = Nothing
End Sub

either of which will return:

Elapsed time (s): 0.0110879

Just like the Standard Module version.

Testing

One thing I wanted to point out is that when performing timing tests, it is normally important to perform multiple test and then average out the values to get a ‘True’ picture of the reality of the process you are testing. Depending what you are validating, network conditions might be a factor, so you should test at different times of the day. You might get different results when an app is first loaded vs. it being running for a while, so you might want to close and reopen and test again, …

If you work for a larger organization, you should probably perform the test on different PC, with different users.

All I’m saying is don’t just rely on a single performance test when making comparisons.

Uses

What can a timer actually be useful to evaluate?!

That’s a doozie to answer! It can be used almost anywhere.

Comparing Code/Approaches

Say you want to implement some code to retrieve the computer’s name, for logging purposes. Then you might turn towards my article: VBA – Get The Computer Name, but how to know which is the most efficient. Well by testing them with a timer, you can easily decide for yourself which would be best to implement in your project.

Environ: 0.001305
WScript.Shell: 0.0026488
WScript.Network: 0.0016299
WMI_GetComputerName: 0.065635
API: 0.0004748

Where we can see that the API approach is the fastest and the WMI approach the slowest! Now you can make an informed decision as to which approach you want to implement within your application.

Evaluating The Impact Of Design Changes

In an Access Database, you can perform test when implementing indexes, to see if they truly improve WHERE/HAVING clause searches. You can use them to test different query approaches, form designs.

Hosting

I’ve used it to compare hosting before. I place the same db on 3 different hosts and used an Access front-end to connect. I used a timer like this to calculate the load time of a form to compare the resulting speed. I also test various data entry times. This way I could used the best host for my client.

The sky is the limit here. Basically, if you have more than 1 way to do something or you want to determine if a change will help or be detrimental, a timer can become a useful tool, amongst others, to evaluate things in an unbiased way.

So on and so forth…

Level of Precision

First of all, yes there are other timer APIs that you could use, for instance: GetLocalTime, GetSystemTime, GetSystemTimePreciseAsFileTime, GetTickCount, …

That said, I’ve personally found the QueryPerformance APIs to be reliable.

There always a question regarding the level of precision with any timer, but truthfully, IMHO it isn’t a factor as long as we use the same technique to do are timing for all our tests. So as long as we always use the QueryPerformance API, then all the tests will have the same inherent error level. What you don’t want to do is start timing with one timer and then comparing that to the timing of another process using another approach!

Also, always remember that we’re not truly after the precise current time, but rather a comparison in the length of execution times between various ‘processes’. It’s all about comparing apples with apples. For that, the QueryPerformance API delivers!

I’ll also leave you with a quote from Microsoft:

Resources on the Subject

DateDiff function (Visual Basic for Applications)

Returns a Variant (Long) specifying the number of time intervals between two specified dates.

Timer function (Visual Basic for Applications)

Returns a Single representing the number of seconds elapsed since midnight.

timeGetTime function (timeapi.h) - Win32 apps

The timeGetTime function retrieves the system time, in milliseconds. The system time is the time elapsed since Windows was started.

QueryPerformanceCounter function - Win32 apps

Retrieves the current value of the performance counter, which is a high resolution (<1us) time stamp that can be used for time-interval measurements.

QueryPerformanceFrequency function - Win32 apps

Retrieves the frequency of the performance counter.