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

VBA 中有没有可以包装函数的代码，让我知道它运行的时间，这样我就可以比较函数的不同运行时间？

149995 次浏览
小开
最佳答案

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 CTimer say, then you can make an instance somewhere global and just call .StartCounter and .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
小开

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

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.

小开

The Timer function in VBA gives you the number of seconds elapsed since midnight, to 1/100 of a second.

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

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:

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)

小开

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://sites.mcpher.com/share/Home/excelquirks/optimizationlink see procProfiler.zip in http://sites.mcpher.com/share/Home/excelquirks/downlable-items

小开 
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

小开

Seconds with 2 decimal spaces:

Dim startTime As Single 'start timer
MsgBox ("run time: " & Format((Timer - startTime) / 1000000, "#,##0.00") & " seconds") 'end timer

seconds format

Milliseconds:

Dim startTime As Single 'start timer
MsgBox ("run time: " & Format((Timer - startTime), "#,##0.00") & " milliseconds") 'end timer

milliseconds format

Milliseconds with comma seperator:

Dim startTime As Single 'start timer
MsgBox ("run time: " & Format((Timer - startTime) * 1000, "#,##0.00") & " milliseconds") 'end timer

Milliseconds with comma seperator

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.

小开

As Mike Woodhouse answered the QueryPerformanceCounter function is the most accurate possible way to bench VBA code (when you don't want to use a custom made dll). I wrote a class (link https://github.com/jonadv/VBA-Benchmark) that makes that function easy to use:

  1. only initialize the benchmark class
  2. call the method in between your code.

There is no need to write code for substracting times, re-initializing times and writing to debug for example.

Sub TimerBenchmark()


Dim bm As New cBenchmark


'Some code here
bm.TrackByName "Some code"


End Sub

This would automatically print a readable table to the Immediate window:

IDnr  Name       Count  Sum of tics  Percentage  Time sum
0     Some code      1          163     100,00%     16 us
TOTAL          1          163     100,00%     16 us


Total time recorded:             16 us

Ofcourse with only one piece of code the table isnt very usefull, but with multiple pieces of code, it instantly becomes clear where the bottleneck in your code is. The class includes a .Wait function, which does the same as Application.Wait, but requires only an input in seconds, instead of a time value (which takes a lot of characters to code).

Sub TimerBenchmark()


Dim bm As New cBenchmark


bm.Wait 0.0001 'Simulation of some code
bm.TrackByName "Some code"


bm.Wait 0.04 'Simulation of some (time consuming) code here
bm.TrackByName "Bottleneck code"




bm.Wait 0.00004 'Simulation of some code, with the same tag as above
bm.TrackByName "Some code"


End Sub

Prints a table with percentages and summarizes code with the same name/tag:

IDnr  Name             Count  Sum of tics  Percentage  Time sum
0     Some code            2       21.374       5,07%   2,14 ms
1     Bottleneck code      1      400.395      94,93%     40 ms
TOTAL                3      421.769     100,00%     42 ms


Total time recorded:             42 ms
小开

I read other answers in this thread and threw together my own class to handle things. It's more an exercise in making a class than anything, but it does work and offers precision at the level I need for my work... I'm just making office tools.

To use the class, you do a dim statement, instantiate a new object when you want to start the timer, and then call a method to stop the timer and get output. Taking a cue from Jonadv's example, there is an optional argument to allow you to label the output in instances where you use multiple timers at once.

Just put this in a class named cTimer:

Option Explicit


'This class allows you to easily see how long your code takes to run by encapsulating the Timer function, which returns the time since midnight in seconds.
'Instantiate the class to start the clock and use the StopTimer method to stop it and output to the Immediate window.  It will accept an optional argument to label the timer.
'If you want to use it multiple times in the same program, make sure to terminate it before creating another timer.


'EXAMPLE:
'Sub ExampleSub()
'Dim t As cTimer                'Declare t as a member of the cTimer class
'Set t = New cTimer             'Create a new cTimer class object called "t" and set the start time
'...
'...
'...
'some code
'...
'...
'...
't.StopTimer                    'Set the stop time and output elapsed time to the Immediate window.  This will output "Timed process took X.XXXXX seconds"
'Set t = Nothing                'Destroy the existing cTimer object called "t"
'Set t = New cTimer             'Create a new cTimer class object called "t" and set the start time again.
'...
'...
'...
'some code
'...
'...
'...
't.StopTimer ("Second section") 'Set the stop time once more and output elapsed time to the Immediate window.  The text output will read "Second section: X.XXXX seconds" for this line.
'End Sub




Private pStartTime As Single
Private pEndTime As Single


Private Sub Class_Initialize()
pStartTime = Timer
End Sub


Public Sub StopTimer(Optional SectionName As String)
pEndTime = Timer
If Not SectionName = "" Then                'If user defines the optional SectionName string
Debug.Print SectionName & ": " & (pEndTime - pStartTime) & " seconds"
Else
Debug.Print "Timed process took " & (pEndTime - pStartTime) & " seconds"
End If
End Sub

提交答案