vba 你如何测试VBA代码的运行时间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/198409/
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
How do you test running time of VBA code?
提问by Lance Roberts
Is there code in VBA I can wrap a function with that will let me know the time it took to run, so that I can compare the different running times of functions?
VBA 中是否有代码我可以用它包装一个函数，让我知道运行所需的时间，以便我可以比较函数的不同运行时间？
采纳答案by Mike Woodhouse
Unless your functions are very slow, you're going to need a very high-resolution timer. The most accurate one I know is QueryPerformanceCounter. Google it for more info. Try pushing the following into a class, call it CTimersay, then you can make an instance somewhere global and just call .StartCounterand .TimeElapsed
除非您的功能很慢，否则您将需要一个非常高分辨率的计时器。我所知道的最准确的一个是QueryPerformanceCounter。谷歌它以获取更多信息。尝试将以下内容推送到一个类中，称其为CTimersay，然后您可以在全局某个地方创建一个实例，然后调用.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
回答by dbb
The Timer function in VBA gives you the number of seconds elapsed since midnight, to 1/100 of a second.
VBA 中的 Timer 函数为您提供自午夜以来经过的秒数，到 1/100 秒。
Dim t as single
t = Timer
'code
MsgBox Timer - t
回答by Kodak
If you are trying to return the time like a stopwatch you could use the following API which returns the time in milliseconds since system startup:
如果你想像秒表一样返回时间，你可以使用以下 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
after http://www.pcreview.co.uk/forums/grab-time-milliseconds-included-vba-t994765.html(as timeGetTime in winmm.dll was not working for me and QueryPerformanceCounter was too complicated for the task needed)
在http://www.pcreview.co.uk/forums/grab-time-milliseconds-included-vba-t994765.html之后（因为 winmm.dll 中的 timeGetTime 对我不起作用并且 QueryPerformanceCounter 对于所需的任务来说太复杂了）
回答by miodf
For newbees, these links explains how to do an automatic profiling of all the subs that you want to time monitor :
对于新手，这些链接解释了如何对您想要计时的所有潜艇进行自动分析：
http://www.nullskull.com/a/1602/profiling-and-optimizing-vba.aspx
http://www.nullskull.com/a/1602/profiling-and-optimizing-vba.aspx
http://sites.mcpher.com/share/Home/excelquirks/optimizationlinksee procProfiler.zip in http://sites.mcpher.com/share/Home/excelquirks/downlable-items
http://sites.mcpher.com/share/Home/excelquirks/optimizationlink看到procProfiler.zip在http://sites.mcpher.com/share/Home/excelquirks/downlable-items
回答by Gajendra Santosh
Sub Macro1()
Dim StartTime As Double
StartTime = Timer
''''''''''''''''''''
'Your Code'
''''''''''''''''''''
MsgBox "RunTime : " & Format((Timer - StartTime) / 86400, "hh:mm:ss")
End Sub
Output:
输出：
RunTime : 00:00:02
运行时间：00:00:02
回答by Tom Juergens
We've used a solution based on timeGetTime in winmm.dll for millisecond accuracy for many years. See http://www.aboutvb.de/kom/artikel/komstopwatch.htm
多年来，我们一直使用基于 winmm.dll 中的 timeGetTime 的解决方案以实现毫秒精度。见http://www.aboutvb.de/kom/artikel/komstopwatch.htm
The article is in German, but the code in the download (a VBA class wrapping the dll function call) is simple enough to use and understand without being able to read the article.
这篇文章是德文的，但下载中的代码（一个包装了 dll 函数调用的 VBA 类）足够简单，即使无法阅读文章也能使用和理解。
回答by technoman23
Seconds with 2 decimal spaces:
带 2 个小数位的秒数：
Dim startTime As Single 'start timer
MsgBox ("run time: " & Format((Timer - startTime) / 1000000, "#,##0.00") & " seconds") 'end timer
Milliseconds:
毫秒：
Dim startTime As Single 'start timer
MsgBox ("run time: " & Format((Timer - startTime), "#,##0.00") & " milliseconds") 'end timer
Milliseconds with comma seperator:
带逗号分隔符的毫秒数：
Dim startTime As Single 'start timer
MsgBox ("run time: " & Format((Timer - startTime) * 1000, "#,##0.00") & " milliseconds") 'end timer
Just leaving this here for anyone that was looking for a simple timer formatted with seconds to 2 decimal spaces like I was. These are short and sweet little timers I like to use. They only take up one line of code at the beginning of the sub or function and one line of code again at the end. These aren't meant to be crazy accurate, I generally don't care about anything less then 1/100th of a second personally, but the milliseconds timer will give you the most accurate run time of these 3. I've also read you can get the incorrect read out if it happens to run while crossing over midnight, a rare instance but just FYI.
只是把这个留在这里给任何正在寻找一个像我一样用秒到 2 个小数位格式化的简单计时器的人。这些是我喜欢使用的简短而甜蜜的小计时器。它们只在 sub 或 function 的开头占用一行代码，在末尾再次占用一行代码。这些并不意味着非常准确，我个人通常不关心小于 1/100 秒的任何事情，但是毫秒计时器将为您提供这 3 个中最准确的运行时间。我也读过您如果它碰巧在跨越午夜时运行，可能会得到错误的读数，这是一个罕见的例子，但仅供参考。
