基准VBA代码 [英] Benchmarking VBA Code
问题描述
这里有两种流行的方法。 p>
首先:计时器
Sub TimerBenchmark()
Dim benchmark As Double
benchmark = Timer
'你的代码在这里
MsgBox定时器 - 基准
End Sub
和勾选 (我认为这是最准确的):
Option Explicit
私有声明函数GetTickCount Libkernel32( )As Long
Sub TickBenchmark()
Dim Start As Long
Dim完成长
Start = GetTickCount()
'你的代码在这里
Finish = GetTickCount()
MsgBox CStr((完成 - 开始)/ 1000)
End Sub
以下代码使用Windows功能比Excel更准确。取自 http://msdn.microsoft.com/en-我们/库/ aa730921.aspx#Office2007excelPerf_MakingWorkbooksCalculateFaster 。相同的页面还包含一些有关改进Excel 2007中性能的很好的提示。
私有声明函数getFrequency Libkernel32_
别名QueryPerformanceFrequency(cyFrequency As Currency)As Long
私有声明函数getTickCount Libkernel32_
别名QueryPerformanceCounter(cyTickCount as Currency)As Long
函数MicroTimer()As Double
'返回秒。
Dim cyTicks1作为货币
静态cyfrequency作为货币
MicroTimer = 0
'获取频率。
如果cyFrequency = 0然后getFrequency cyFrequency
'获取蜱虫。
getTickCount cyTicks1
'Seconds
如果cyFrequency然后MicroTimer = cyTicks1 / cyFrequency
结束功能
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
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
这篇关于基准VBA代码的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!