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?

Solution 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

Solution 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

Solution 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)

Solution 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

Solution 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

View more solutions

Comments

[Lance Roberts]

I implemented this in Excel VBA (adding in the Overhead as mentioned in this KB article: support.microsoft.com/kb/172338. It worked great. Thanks.

[Andrew Scagnelli]

That wouldn't work -- you can't get more resolution out of taking the average like that.

[Justin]

Thanks, this works well for me, too. TimeElapsed() gives the result in milliseconds. I did not implement any overhead compensation because I was more worried about the effect of a stutter in the overhead calculation than perfect accuracy.

[BrainSlugs83]

That's a lot of overheard (in lines of code to manage) -- if you can live with ~10ms accuracy, @Kodak's answer below gives the same thing in one line of code (importing GetTickCount from kernel32).

[BrainSlugs83]

This is a great answer. Of note: the precision of the returned data is in milliseconds, however, the counter is only accurate to about 1/100th of a second (that is, it could be off by 10 to 16 ms) via MSDN: msdn.microsoft.com/en-us/library/windows/desktop/…

[BrainSlugs83]

Still, if you're measuring performance in VBA, getting 1/100th of a second resolution is not bad. -- Invoking the timing calls alone could take a couple of ms. If the call is so fast that you need that much resolution to time it, you probably don't need performance data about that call.

[Kodak]

hmm, if the resolution is the same here as with the Timer then I would go with the Timer

[TmTron]

notes: on Mac the Timer is only accurate to one second - and this might get negative numbers if it starts before midnight and ends after midnight

[Revolucion for Monica]

What is the Public Declare Function ... part ? It creates an error when adding your code at the bottom of mine

[Revolucion for Monica]

How do you use StartCounter And TimeElapsed ? I did an instance Timer of CTimer at the beginning and With StartCounter I just wrote .StartCounter after my sub began and .TimeElapsed and it answered me Invalid use of property. When I let .StartCounter alone it tells me an object is not set.

[Kodak]

You need to move this public declaration to the top of your module

[user3226167]

For excel 2010: Declare PtrSafe Function stackoverflow.com/questions/21611744/…

[Gustav]

Only the first is useful, as Timer has a resolution of 10ms.

[jonadv]

"Google it for more info." :) I went down that rabbit hole and the result is below.

[vbAdder]

On my mac vba.timer gives ms as well now? VBA 7.1