基准VBA代码 [英] Benchmarking VBA Code

问题描述

什么被认为是最准确的基准VBA代码的方法（在我的情况下，我在Excel中测试代码）？有没有其他的技术，除了下面的2以外的基准代码，如果是，该方法的利弊是什么？

这里有两种流行的方法。 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屋！