Timing a macro

D

ducttape

How am I able to tell the length of time it takes to run a macro? I have
previously used the =now() function, followed by a paste special
(values) at the start and stop of the macro, but this function only is
as precise as a second. I will need something that can measure to the
millisecond.

Also, is there a way to copy a macro saved in one file, to all of the
files in a folder without using a personal.xls?
 
D

Dave O

Try this code- it assigns system time to variables called Start and
Fin, then multiplies by 86400 (the number of seconds in a day). The
final message box shows many significant digits, which should satisfy
your milliseconds requirement.

Sub Count_to_10()
Dim CountTo As Long
Dim Start As Double, Fin As Double

Start = Now()
For CountTo = 1 To 10
MsgBox "Loop #" & CountTo
Next CountTo

Fin = Now()

MsgBox "Elapsed time = " & (Fin - Start) * 24 * 60 * 60 & " seconds."
End Sub
 
J

joeu2004

ducttape said:
How am I able to tell the length of time it takes to run
a macro? I have previously used the =now() function
[...] but this function only is as precise as a second.
Click to expand...

Hmm, I use the VBA Timer() function, following an example
in Walkenbach's book. On Windows (not Mac), Timer()
returns fractional seconds with at least 10-msec resolution,
and I believe it has 1-msec resolution.
I will need something that can measure to the millisecond.
Click to expand...

I use the API GetTickCount(), following a recent suggestion by
"Peter T". Not only does it provide better resolution, but also
seems to be 5X faster(!). Usage:

Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub timeit()
Dim startTime as Long, elapsedTime as Long
startTime = GetTimeCount()
[... your code here ...]
elapsedTime = GetTimeCount() - startTime
end Sub

Notes:

1. "Peter T" uses Public instead of Private. Public makes more
sense to me. But Walkenbach uses Private, and he is a
self-described expert and generally truly regarded as such,
I believe. I used Private, and it worked.

2. VBA execution is surprising fast. For example, on my system,
a tight loop around GetTimeCount() averages 0.055 microsec.
The point is: even millisec resolution might not be good enough.
Depending on the total elapsed time for the code you want to
measure, you might need to implement a loop and compute an
average in order to accurately measure the time.
 
Top