Benchmarking VBA Code

Question

What is considered the most accurate way to benchmark VBA code (in my case, I am testing code in Excel)? Are there any other techniques for benchmarking code besides the 2 below, and if so, what are the pros/cons of the method?

Here are 2 popular methods.

First: Timer

Sub TimerBenchmark()

Dim benchmark As Double
benchmark = Timer

'Do your code here

MsgBox Timer - benchmark

End Sub

And Tick (which I see argued as the most accurate):

Option Explicit
Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub TickBenchmark()

Dim Start As Long
Dim Finish As Long

Start = GetTickCount()

'Do your code here

Finish = GetTickCount()
MsgBox CStr((Finish - Start) / 1000)

End Sub

Answer 1

Professional Excel Development contains a dll utility PerfMon that I prefer, for its accuracy, and as it can be easily inserted throughout code with a couple of menu clicks

Answer 2

The following code uses a windows function that is more accurate than Excel. It is taken from http://msdn.microsoft.com/en-us/library/aa730921.aspx#Office2007excelPerf_MakingWorkbooksCalculateFaster. The same page also contains some great tips on improving performance in Excel 2007.

Private Declare Function getFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" _
Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long

Function MicroTimer() As Double

  'Returns seconds.

  Dim cyTicks1 As Currency
  Static cyFrequency As Currency
  MicroTimer = 0

  ' Get frequency.
  If cyFrequency = 0 Then getFrequency cyFrequency

  ' Get ticks.
  getTickCount cyTicks1                            

  ' Seconds
  If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency 
End Function

Answer 3

Any measurement is going to be noisy, so if you want precision, repeat the measurement many times and average the result.

Answer 4

Interesting question. This is not really a full answer but this is too long to be posted as a comment.
What i use is this kind of procedure:

Option Explicit
Public Time As Double
Sub Chrono()
If Time = 0 Then
    Time = Now()
Else
    MsgBox "Total time :" & Application.Text(Now() - _
        Time, "mm:ss") & "."  'or whatever if not a msgbox
    Time = 0
End If
End Sub

That way, you can put your code wherever you want and only have to call it twice (for instance):

If C_DEBUG Then Call Chrono

At the beginning and at the end of the part of code you want to test.

Yet, i would say there is no real "accurate" method because it also depends on what is running on your computer. I'd say these methods would mostly help telling which code is better than another.