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

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

"RyanH" wrote:

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

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

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.

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


"Bernie Deitrick" wrote:

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

Ryan,

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

HTH,
Bernie
MS Excel MVP


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

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


"Bernie Deitrick" wrote:

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

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?
--
Cheers,
Ryan


"Bernie Deitrick" wrote:

Ryan,

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

HTH,
Bernie
MS Excel MVP


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

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


"Bernie Deitrick" wrote:

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

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" wrote in message
...
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?
--
Cheers,
Ryan


"Bernie Deitrick" wrote:

Ryan,

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

HTH,
Bernie
MS Excel MVP


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

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


"Bernie Deitrick" wrote:

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

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


--
__________________________________
HTH

Bob

"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

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


"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"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

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

--
__________________________________
HTH

Bob

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


"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"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

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

--
Cheers,
Ryan


"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

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


"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"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

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


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

--
Cheers,
Ryan


"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

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


"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"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

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


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

--
Cheers,
Ryan


"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

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


"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"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

Hello Ryan,

If you have plenty of time and if you like to test all macros which
are suggested here - do it.

If you want to use an efficient tool which Excel unfortunately is
lacking: Buy FastExcel (£44 or $79 per license)
http://www.decisionmodels.com/index.htm

Its creator Charles Williams also published some timing macros but I
went for FastExcel - without any regret.

Regards,
Bernd