Benchmarking VBA Code

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 LongPrivate Declare Function getTickCount Lib "kernel32" _Alias "QueryPerformanceCounter" (cyTickCount As Currency) As LongFunction 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

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 ExplicitPublic Time As DoubleSub Chrono()If Time = 0 Then    Time = Now()Else    MsgBox "Total time :" & Application.Text(Now() - _        Time, "mm:ss") & "."  'or whatever if not a msgbox    Time = 0End IfEnd 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.

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