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

For VBA, I believe most people go with the simplest solution:

Dim start_time, end_time
start_time = Now()
' Do your stuff here '
end_time = Now()
MsgBox(DateDiff("s", start_time, end_time))

The DateDiff lets you know how many seconds something took. If you need more accuracy than seconds, you'll need a more advanced solution, which according to The Scripting Guy may not exist.

Answer 2

I would use the System.Diagnostics.Stopwatch class. Start the stopwatch, call your function, and stop the stopwatch. It will give you very accurate time differences.

I'm not sure if you have access to the stopwatch class in VBA, but it's worth a try.

Answer 3

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 4

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 5

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

If you need greater resolution, I would simply run the function 1,000 times and divide the total time by 1,000.