How can I time the speed of a macro?

[RyanH]

I would like to time the speed of a macro. I currently use this code, but
the StartTime and EndTime are the same, is that right? I don't think the
Time function is precise enough. Is there a accurate way of timing the speed
of a macro?

Sub TimeMacro()

Dim StartTime As Single
Dim EndTime As Single

StartTime = Time
Debug.Print "Start Time = " & StartTime

' my code here

EndTime = Time
Debug.Print "End Time = " & EndTime

Debug.Print "Elapsed Time = " & EndTime - StartTime

End Sub

 

[Mike H]

Try this

Sub TimeMacro()
Dim StartTime As Single
Dim EndTime As Single
StartTime = Time
Debug.Print "Start Time = " & Format(StartTime, "hh:mm:ss")
'dummy loop -Your code
For t = 1 To 100000000: Next
EndTime = Time
Debug.Print "End Time = " & Format(Time, "hh:mm:ss")
Debug.Print "Elapsed Time = " & Format(EndTime - StartTime, "hh:mm:ss")
End Sub


Mike

 

[Bernie Deitrick]

Insert a Class Module and name it

CHiResTimer

Put this code into that class module:


Option Explicit

'How many times per second is the counter updated?
Private Declare Function QueryFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" ( _
lpFrequency As Currency) As Long

'What is the counter's value
Private Declare Function QueryCounter Lib "kernel32" _
Alias "QueryPerformanceCounter" ( _
lpPerformanceCount As Currency) As Long

'Variables to store the counter information
Dim cFrequency As Currency
Dim cOverhead As Currency
Dim cStarted As Currency
Dim cStopped As Currency


Private Sub Class_Initialize()
Dim cCount1 As Currency, cCount2 As Currency

'Get the counter frequency
QueryFrequency cFrequency

'Call the hi-res counter twice, to check how long it takes
QueryCounter cCount1
QueryCounter cCount2

'Store the call overhead
cOverhead = cCount2 - cCount1

End Sub

Public Sub StartTimer()
'Get the time that we started
QueryCounter cStarted
End Sub

Public Sub StopTimer()
'Get the time that we stopped
QueryCounter cStopped
End Sub

Public Property Get Elapsed() As Double

Dim cTimer As Currency

'Have we stopped or not?
If cStopped = 0 Then
QueryCounter cTimer
Else
cTimer = cStopped
End If

'If we have a frequency, return the duration, in seconds
If cFrequency > 0 Then
Elapsed = (cTimer - cStarted - cOverhead) / cFrequency
End If

End Property




Then, in a regular code module, use the timer like this:

Sub TimeMacro()
Dim oTimer As New CHiResTimer

oTimer.StartTimer
MacroToBeTimed ' or other code
oTimer.StopTimer

MsgBox "That macro took " & Format(oTimer.Elapsed, "#.000000") & " seconds."

End Sub


HTH,
Bernie
MS Excel MVP

 

[Bob Phillips]

Sub TimeMacro()

Dim StartTime As Double
Dim EndTime As Double

StartTime = Timer
Debug.Print "Start Time = " & StartTime

' my code here

EndTime = Timer
Debug.Print "End Time = " & EndTime

Debug.Print "Elapsed Time = " & EndTime - StartTime

End Sub

 

[RyanH]

That is some niffty code there Bernie. Unfortunately, I am not very
comfortable with Class Modules yet and I am getting an error on line below
and don't know why. I'm not sure if it matters, but I put my code that I
want to test in a Worksheet Double Click Event.

ERROR>>Dim oTimer As New CHiResTimer
'Compile Error: User-defined type not defined
--
Cheers,
Ryan

Insert a Class Module and name it

CHiResTimer

Put this code into that class module:


Option Explicit

'How many times per second is the counter updated?
Private Declare Function QueryFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" ( _
lpFrequency As Currency) As Long

'What is the counter's value
Private Declare Function QueryCounter Lib "kernel32" _
Alias "QueryPerformanceCounter" ( _
lpPerformanceCount As Currency) As Long

'Variables to store the counter information
Dim cFrequency As Currency
Dim cOverhead As Currency
Dim cStarted As Currency
Dim cStopped As Currency


Private Sub Class_Initialize()
Dim cCount1 As Currency, cCount2 As Currency

'Get the counter frequency
QueryFrequency cFrequency

'Call the hi-res counter twice, to check how long it takes
QueryCounter cCount1
QueryCounter cCount2

'Store the call overhead
cOverhead = cCount2 - cCount1

End Sub

Public Sub StartTimer()
'Get the time that we started
QueryCounter cStarted
End Sub

Public Sub StopTimer()
'Get the time that we stopped
QueryCounter cStopped
End Sub

Public Property Get Elapsed() As Double

Dim cTimer As Currency

'Have we stopped or not?
If cStopped = 0 Then
QueryCounter cTimer
Else
cTimer = cStopped
End If

'If we have a frequency, return the duration, in seconds
If cFrequency > 0 Then
Elapsed = (cTimer - cStarted - cOverhead) / cFrequency
End If

End Property




Then, in a regular code module, use the timer like this:

Sub TimeMacro()
Dim oTimer As New CHiResTimer

oTimer.StartTimer
MacroToBeTimed ' or other code
oTimer.StopTimer

MsgBox "That macro took " & Format(oTimer.Elapsed, "#.000000") & " seconds."

End Sub


HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Ryan,

Do you have the class module in the same workbook, and is it named CHiResTimer?

HTH,
Bernie
MS Excel MVP

That is some niffty code there Bernie. Unfortunately, I am not very
comfortable with Class Modules yet and I am getting an error on line below
and don't know why. I'm not sure if it matters, but I put my code that I
want to test in a Worksheet Double Click Event.

ERROR>>Dim oTimer As New CHiResTimer
'Compile Error: User-defined type not defined

 

[RyanH]

Got it to work! Just out of curiousity, why do you need to do this in a
Class Module? Plus, did you have any website or resource that would explain
what is happening in your code?

I'm not sure how the code gets its time, what "kernal32" means, and I think
I see how you are passing the cCourt1 & 2 arguments to the QueryCounter but
what is the QueryCounter doing with it?

 

[Bernie Deitrick]

Ryan,

Here is a good explanation on Chip Pearson's site:

http://www.cpearson.com/excel/Classes.aspx

as for kernel32

http://en.wikipedia.org/wiki/Kernel32.dll


HTH,
Bernie
MS Excel MVP

 

[RyanH]

Thanks for the reply Bob. How accurate is the Time Function? Is it in
milliseconds? Because I can't seem to get this to work properly. Do I need
to multiply the Elapsed Time by 1000?
--
Cheers,
Ryan

Sub TimeMacro()

Dim StartTime As Double
Dim EndTime As Double

StartTime = Timer
Debug.Print "Start Time = " & StartTime

' my code here

EndTime = Timer
Debug.Print "End Time = " & EndTime

Debug.Print "Elapsed Time = " & EndTime - StartTime

End Sub

 

[Bob Phillips]

No, it is the number of seconds, although it will give decimal parts of a
second as well.

--
__________________________________
HTH

Bob

Thanks for the reply Bob. How accurate is the Time Function? Is it in
milliseconds? Because I can't seem to get this to work properly. Do I
need
to multiply the Elapsed Time by 1000?

 

[RyanH]

I am getting Elapsed Time = 0 using the code below. The macro runs pretty
fast. The code that I got from Bernie reads around .124560. Is the time
function not detailed enough.

Sub TimeMacro()

Dim StartTime As Double
Dim EndTime As Double

StartTime = Time
Debug.Print "Start Time = " & StartTime

' my code here

EndTime = Time
Debug.Print "End Time = " & EndTime

Debug.Print "Elapsed Time = " & EndTime - StartTime

End Sub

 

[Bernie Deitrick]

Ryan,

The time function is not detailed enough - it only returns H:M:S, with resolution of 1 second. That
is why you need to use a High Resolution Timer....

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Run this little snippet:

MsgBox Format(Time * 24 * 60 * 60, "0.00000000000")

to show that seconds ( 24 * 60 * 60 seconds in one day) are the highest resolution returned...

HTH,
Bernie
MS Excel MVP

 

[Bob Phillips]

I am sorry, but my solution was Timer NOT Time. As help says ...
In Microsoft Windows the Timer function returns fractional portions of a
second. On the Macintosh, timer resolution is one second.

 

[Bernie Deitrick]

My apologies. Timer returns time to the nearest tenth of a second, so the
maximum error in two calls is one tenth of a second.

Bernie

 

[Bob Phillips]

I think it is a lot better resolution than 1/10th, have just got a result
of .015625

 

[Bernie Deitrick]

Bob,

Poor testing and bad luck on my part - I ran this twice:

MsgBox Format(Timer, "0.0000")

and each time it round tenths, so I jumped with both feet.

But I haven't seen any thing past hundreths....

Bernie

 

[Bob Phillips]

Wat do you get if you run this?

Dim nTime As Double
Dim i As Long

nTime = Timer
For i = 1 To 1000000

Next i
MsgBox Timer - nTime

 

[Bernie Deitrick]

Bob,

Thanks. Now it looks like the resolution is in 1/8 of 1000ths of a second - i get a lot of
0.XXX125, 0.XXX875, etc, where the last three digits look like 1/8 2/8 3/8 etc:

Sub ShowTimeValues()
Dim nTime As Double
Dim i As Long
Dim j As Integer
Dim myStr As String

myStr = ""

For j = 1 To 20

nTime = Timer
For i = 1 To 1000000

Next i
myStr = myStr & Chr(10) & Format(Timer - nTime, "0.000000")
Next j
MsgBox myStr
End Sub

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Bob,

It looks like 1/64 of a second is the resolution of Timer:

Sub ShowValues3()
Dim j As Integer

Range("A1:A1000").NumberFormat = "0.000000"

For j = 1 To 1000
Cells(j, 1).Value = Timer
Next j

With Range("B2:B1000")
..FormulaR1C1 = "=RC[-1]-R[-1]C[-1]"
..NumberFormat = "# ???/???"
End With
End Sub

HTH,
Bernie
MS Excel MVP