对 VBA 代码进行基准测试 [英] Benchmarking VBA Code

问题描述

什么被认为是对 VBA 代码进行基准测试的最准确方法(就我而言，我正在 Excel 中测试代码)?除了下面的 2 种技术之外，是否还有其他用于对代码进行基准测试的技术，如果有，该方法的优缺点是什么?

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.

第一:定时器

Sub TimerBenchmark()

Dim benchmark As Double
benchmark = Timer

'Do your code here

MsgBox Timer - benchmark

End Sub

和Tick(我认为这是最准确的):

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

推荐答案

以下代码使用了比 Excel 更准确的 windows 函数.它取自 http://msdn.microsoft.com/en-us/library/aa730921.aspx#Office2007excelPerf_MakingWorkbooksCalculateFaster.同一页面还包含一些有关提高 Excel 2007 性能的重要提示.

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屋！