你如何测试VBA代码的运行时间？

在VBA中是否有代码可以包装一个函数,让我知道它运行的时间,以便我可以比较函数的不同运行时间？

1> Mike Woodhou..：

---

除非你的功能很慢,否则你需要一个非常高分辨率的计时器.我所知道的最准确的是QueryPerformanceCounter.谷歌更多信息.尝试推下到一个类,把它CTimer说,那么你可以让一个实例全球某处,只是打电话.StartCounter和.TimeElapsed

Option Explicit

Private Type LARGE_INTEGER
    lowpart As Long
    highpart As Long
End Type

Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As LARGE_INTEGER) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As LARGE_INTEGER) As Long

Private m_CounterStart As LARGE_INTEGER
Private m_CounterEnd As LARGE_INTEGER
Private m_crFrequency As Double

Private Const TWO_32 = 4294967296# ' = 256# * 256# * 256# * 256#

Private Function LI2Double(LI As LARGE_INTEGER) As Double
Dim Low As Double
    Low = LI.lowpart
    If Low < 0 Then
        Low = Low + TWO_32
    End If
    LI2Double = LI.highpart * TWO_32 + Low
End Function

Private Sub Class_Initialize()
Dim PerfFrequency As LARGE_INTEGER
    QueryPerformanceFrequency PerfFrequency
    m_crFrequency = LI2Double(PerfFrequency)
End Sub

Public Sub StartCounter()
    QueryPerformanceCounter m_CounterStart
End Sub

Property Get TimeElapsed() As Double
Dim crStart As Double
Dim crStop As Double
    QueryPerformanceCounter m_CounterEnd
    crStart = LI2Double(m_CounterStart)
    crStop = LI2Double(m_CounterEnd)
    TimeElapsed = 1000# * (crStop - crStart) / m_crFrequency
End Property

---

我在Excel VBA中实现了这一点(如本知识库文章中提到的那样添加了开销:http://support.microsoft.com/kb/172338.它运行得很好.谢谢.

---

谢谢，这对我也很好。TimeElapsed（）以毫秒为单位给出结果。我没有实施任何开销补偿，因为我更担心开销计算中的口吃而不是完美的准确性。

---

这是很多听闻的内容（在要管理的代码行中）-如果您可以以〜10ms的精度生存，则下面的@Kodak答案将在一行代码中给出相同的内容（从kernel32导入`GetTickCount`）。

2> dbb..：

---

VBA中的定时器功能为您提供从午夜到1/100秒的经过秒数.

Dim t as single
t = Timer
'code
MsgBox Timer - t

如果您需要更高的分辨率,我只需运行该功能1,000次并将总时间除以1,000.

---

那不行 - 你无法获得更多的分辨率.

---

不过,如果你在VBA中测量性能,那么获得1/100秒的分辨率也不错. - 单独调用定时调用可能需要几毫秒.如果呼叫速度太快以至于需要那么多分辨率来计时,那么您可能不需要有关该呼叫的性能数据.

3> Kodak..：

---

如果您尝试像秒表一样返回时间,则可以使用以下API返回自系统启动以来的毫秒时间:

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long
Sub testTimer()
Dim t As Long
t = GetTickCount

For i = 1 To 1000000
a = a + 1
Next

MsgBox GetTickCount - t, , "Milliseconds"
End Sub

在http://www.pcreview.co.uk/forums/grab-time-milliseconds-included-vba-t994765.html之后(因为winmm.dll中的timeGetTime对我不起作用而且QueryPerformanceCounter对于所需的任务来说太复杂了)