对 VBA 代码进行基准测试
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7103552/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Benchmarking VBA Code
提问by aevanko
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?
什么被认为是对 VBA 代码进行基准测试的最准确方法（就我而言，我正在 Excel 中测试代码）？除了下面的 2 项之外，是否还有其他用于对代码进行基准测试的技术，如果有，该方法的优缺点是什么？
Here are 2 popular methods.
这里有2种流行的方法。
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):
和Tick（我认为这是最准确的）：
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
回答by RonnieDickson
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.
以下代码使用了比 Excel 更准确的 windows 函数。它取自http://msdn.microsoft.com/en-us/library/aa730921.aspx#Office2007excelPerf_MakingWorkbooksCalculateFaster。同一页面还包含一些有关提高 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
回答by JMax
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 betterthan another.
然而，我会说没有真正“准确”的方法，因为它还取决于您的计算机上运行的内容。我会说这些方法主要有助于判断哪个代码比另一个更好。
回答by Mike Dunlavey
Any measurement is going to be noisy, so if you want precision, repeat the measurement many times and average the result.
任何测量都会产生噪音，因此如果您想要精确，请多次重复测量并平均结果。
回答by brettdj
Professional Excel Developmentcontains a dll utility PerfMonthat I prefer, for its accuracy, and as it can be easily inserted throughout code with a couple of menu clicks
Professional Excel Development包含一个PerfMon我更喜欢的 dll 实用程序，因为它的准确性，并且可以通过单击几次菜单轻松插入到整个代码中
