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


"RyanH" wrote in message
...
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

--
Cheers,
Ryan