How do you test running time of VBA code?

Optimization Testing Vba Profiling Performance

Optimization Problem Overview

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?

Optimization Solutions

Solution 1 - Optimization

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

Solution 2 - Optimization

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

Solution 3 - Optimization

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)

Solution 4 - Optimization

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

Solution 5 - Optimization

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

Solution 6 - Optimization

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.

Solution 7 - Optimization

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:

only initialize the benchmark class
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

Solution 8 - Optimization

I read other answers in this thread and threw together my own class to handle things. It's more an exercise in making a class than anything, but it does work and offers precision at the level I need for my work... I'm just making office tools.

To use the class, you do a dim statement, instantiate a new object when you want to start the timer, and then call a method to stop the timer and get output. Taking a cue from Jonadv's example, there is an optional argument to allow you to label the output in instances where you use multiple timers at once.

Just put this in a class named cTimer:

Option Explicit

'This class allows you to easily see how long your code takes to run by encapsulating the Timer function, which returns the time since midnight in seconds.
'Instantiate the class to start the clock and use the StopTimer method to stop it and output to the Immediate window.  It will accept an optional argument to label the timer.
'If you want to use it multiple times in the same program, make sure to terminate it before creating another timer.

'EXAMPLE:
'Sub ExampleSub()
'Dim t As cTimer                'Declare t as a member of the cTimer class
'Set t = New cTimer             'Create a new cTimer class object called "t" and set the start time
'...
'...
'...
'some code
'...
'...
'...
't.StopTimer                    'Set the stop time and output elapsed time to the Immediate window.  This will output "Timed process took X.XXXXX seconds"
'Set t = Nothing                'Destroy the existing cTimer object called "t"
'Set t = New cTimer             'Create a new cTimer class object called "t" and set the start time again.
'...
'...
'...
'some code
'...
'...
'...
't.StopTimer ("Second section") 'Set the stop time once more and output elapsed time to the Immediate window.  The text output will read "Second section: X.XXXX seconds" for this line.
'End Sub


Private pStartTime As Single
Private pEndTime As Single

Private Sub Class_Initialize()
pStartTime = Timer
End Sub

Public Sub StopTimer(Optional SectionName As String)
pEndTime = Timer
If Not SectionName = "" Then                'If user defines the optional SectionName string
    Debug.Print SectionName & ": " & (pEndTime - pStartTime) & " seconds"
Else
    Debug.Print "Timed process took " & (pEndTime - pStartTime) & " seconds"
End If
End Sub

Solution 9 - Optimization

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.

Content Type	Original Author	Original Content on Stackoverflow
Question	Lance Roberts	View Question on Stackoverflow
Solution 1 - Optimization	Mike Woodhouse	View Answer on Stackoverflow
Solution 2 - Optimization	dbb	View Answer on Stackoverflow
Solution 3 - Optimization	Kodak	View Answer on Stackoverflow
Solution 4 - Optimization	Gajendra Santosh	View Answer on Stackoverflow
Solution 5 - Optimization	miodf	View Answer on Stackoverflow
Solution 6 - Optimization	Tom Juergens	View Answer on Stackoverflow
Solution 7 - Optimization	jonadv	View Answer on Stackoverflow
Solution 8 - Optimization	Daekar	View Answer on Stackoverflow
Solution 9 - Optimization	SendETHToThisAddress	View Answer on Stackoverflow