Benchmarking VBA Code
Solution 1
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
Solution 2
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.
Solution 3
Any measurement is going to be noisy, so if you want precision, repeat the measurement many times and average the result.
Solution 4
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
Solution 5
As RonnieDickson answered the QueryPerformanceCounter function is the most accurate possible way to bench VBA code (when you don't want to use a dll, like brettdj suggested). I wrote a class that makes that function easy to use: only initialize the benchmark class + call the method in between your code. 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.Wait 0.0001
bm.TrackByName "Some code"
'Some more (time consuming) code here
bm.Wait 0.04
bm.TrackByName "Bottleneck code"
End Sub
Which would automatically print a readable table to the Immediate window:
IDnr Name Count Sum of tics Percentage Time sum
0 Fast code 1 25.136 5,91% 2,51 ms
1 Bottleneck code 1 400.316 94,09% 40 ms
TOTAL 2 425.452 100,00% 43 ms
Total time recorded: 43 ms
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. It is used above to simulate some code.
aevanko
Professional translator (Japanese to English), video game producer, and Excel (VBA) enthusiast. I have been studying C# as a hobby. Programming for me is just a hobby and I enjoy contributing and learning here. http://careers.stackoverflow.com/users/info/122117@aevankogooglefacebooklinkedin
Updated on June 01, 2022Comments
-
aevanko almost 2 years
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
-
vmasule over 12 yearsThe author of that piece is a regular contributor to Stackoverflow. His own website has lots more great information about Excel and performance: decisionmodels.com
-
StockB over 10 yearsCould you please summarize the code in the link in your answer in order to prevent link rot? Also, the supplied microtimer code doesn't seem to work on 64-bit Excel. Are there any alternatives for 64-bit users?
-
Anastasiya-Romanova 秀 over 7 years@StockB FWIW, add
PtrSafe
betweenDeclare
andFunction
for declaring API functions in 64-bit MS Office. -
T.M. almost 3 yearsSeems to be very interesting; would be worth to add minimal methodical hints :+) @jonadv
-
jonadv almost 3 yearsWhat do you mean with methodical hints? That I should add the code of the written class to this comment?
-
T.M. almost 3 yearsNot the code, only a short description of the base logic (e.g. how you use API functions, why it's faster than other approaches, ...) and possibly some example features, too :-) @jonadv