How do you test running time of VBA code?

Question

Is there code in VBA I can wrap a function with that will let me know the time it took to run, so that I can compare the different running times of functions?

Answer 1

Unless your functions are very slow, you're going to need a very high-resolution timer. The most accurate one I know is QueryPerformanceCounter. Google it for more info. Try pushing the following into a class, call it CTimer say, then you can make an instance somewhere global and just call .StartCounter and .TimeElapsed

Option Explicit

Private Type LARGE_INTEGER
    lowpart As Long
    highpart As Long
End Type

Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As LARGE_INTEGER) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As LARGE_INTEGER) As Long

Private m_CounterStart As LARGE_INTEGER
Private m_CounterEnd As LARGE_INTEGER
Private m_crFrequency As Double

Private Const TWO_32 = 4294967296# ' = 256# * 256# * 256# * 256#

Private Function LI2Double(LI As LARGE_INTEGER) As Double
Dim Low As Double
    Low = LI.lowpart
    If Low < 0 Then
        Low = Low + TWO_32
    End If
    LI2Double = LI.highpart * TWO_32 + Low
End Function

Private Sub Class_Initialize()
Dim PerfFrequency As LARGE_INTEGER
    QueryPerformanceFrequency PerfFrequency
    m_crFrequency = LI2Double(PerfFrequency)
End Sub

Public Sub StartCounter()
    QueryPerformanceCounter m_CounterStart
End Sub

Property Get TimeElapsed() As Double
Dim crStart As Double
Dim crStop As Double
    QueryPerformanceCounter m_CounterEnd
    crStart = LI2Double(m_CounterStart)
    crStop = LI2Double(m_CounterEnd)
    TimeElapsed = 1000# * (crStop - crStart) / m_crFrequency
End Property

Answer 2

The Timer function in VBA gives you the number of seconds elapsed since midnight, to 1/100 of a second.

Dim t as single
t = Timer
'code
MsgBox Timer - t

Answer 3

If you are trying to return the time like a stopwatch you could use the following API which returns the time in milliseconds since system startup:

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long
Sub testTimer()
Dim t As Long
t = GetTickCount

For i = 1 To 1000000
a = a + 1
Next

MsgBox GetTickCount - t, , "Milliseconds"
End Sub

after http://www.pcreview.co.uk/forums/grab-time-milliseconds-included-vba-t994765.html (as timeGetTime in winmm.dll was not working for me and QueryPerformanceCounter was too complicated for the task needed)

Answer 4

Sub Macro1()
    Dim StartTime As Double
    StartTime = Timer

        ''''''''''''''''''''
            'Your Code'
        ''''''''''''''''''''
    MsgBox "RunTime : " & Format((Timer - StartTime) / 86400, "hh:mm:ss")
End Sub

Output:

RunTime : 00:00:02

Answer 5

For newbees, these links explains how to do an automatic profiling of all the subs that you want to time monitor :

http://www.nullskull.com/a/1602/profiling-and-optimizing-vba.aspx

http://sites.mcpher.com/share/Home/excelquirks/optimizationlink see procProfiler.zip in http://sites.mcpher.com/share/Home/excelquirks/downlable-items

Answer 6

We've used a solution based on timeGetTime in winmm.dll for millisecond accuracy for many years. See http://www.aboutvb.de/kom/artikel/komstopwatch.htm

The article is in German, but the code in the download (a VBA class wrapping the dll function call) is simple enough to use and understand without being able to read the article.

Answer 7

As Mike Woodhouse answered the QueryPerformanceCounter function is the most accurate possible way to bench VBA code (when you don't want to use a custom made dll). I wrote a class (link https://github.com/jonadv/VBA-Benchmark) that makes that function easy to use:

1. only initialize the benchmark class
2. call the method in between your code.

There is no need to write code for substracting times, re-initializing times and writing to debug for example.

Sub TimerBenchmark()

Dim bm As New cBenchmark

'Some code here
bm.TrackByName "Some code"

End Sub

This would automatically print a readable table to the Immediate window:

IDnr  Name       Count  Sum of tics  Percentage  Time sum
0     Some code      1          163     100,00%     16 us
      TOTAL          1          163     100,00%     16 us

Total time recorded:             16 us

Ofcourse with only one piece of code the table isnt very usefull, but with multiple pieces of code, it instantly becomes clear where the bottleneck in your code is. The class includes a .Wait function, which does the same as Application.Wait, but requires only an input in seconds, instead of a time value (which takes a lot of characters to code).

Sub TimerBenchmark()

Dim bm As New cBenchmark

bm.Wait 0.0001 'Simulation of some code
bm.TrackByName "Some code"

bm.Wait 0.04 'Simulation of some (time consuming) code here
bm.TrackByName "Bottleneck code"


bm.Wait 0.00004 'Simulation of some code, with the same tag as above
bm.TrackByName "Some code"

End Sub

Prints a table with percentages and summarizes code with the same name/tag:

IDnr  Name             Count  Sum of tics  Percentage  Time sum
0     Some code            2       21.374       5,07%   2,14 ms
1     Bottleneck code      1      400.395      94,93%     40 ms
      TOTAL                3      421.769     100,00%     42 ms

Total time recorded:             42 ms

Answer 8

Seconds with 2 decimal spaces:

Dim startTime As Single 'start timer
MsgBox ("run time: " & Format((Timer - startTime) / 1000000, "#,##0.00") & " seconds") 'end timer

Milliseconds:

Dim startTime As Single 'start timer
MsgBox ("run time: " & Format((Timer - startTime), "#,##0.00") & " milliseconds") 'end timer

Milliseconds with comma seperator:

Dim startTime As Single 'start timer
MsgBox ("run time: " & Format((Timer - startTime) * 1000, "#,##0.00") & " milliseconds") 'end timer

Just leaving this here for anyone that was looking for a simple timer formatted with seconds to 2 decimal spaces like I was. These are short and sweet little timers I like to use. They only take up one line of code at the beginning of the sub or function and one line of code again at the end. These aren't meant to be crazy accurate, I generally don't care about anything less then 1/100th of a second personally, but the milliseconds timer will give you the most accurate run time of these 3. I've also read you can get the incorrect read out if it happens to run while crossing over midnight, a rare instance but just FYI.