How can I time the speed of a macro?

[Bob Phillips]

Yeah I agree, it does seem that, which is quite good . One could argue all
night how fine a code timer needs to go, but one thing I think we all agree
on is that the help should state its resolution.

--
__________________________________
HTH

Bob

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

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

--
__________________________________
HTH

Bob

 

[Jim Thomlinson]

Note to the Ryan. I have found that run times can vary significantly. It
depends on what else your system might be doing at run time. I personally do
my tests in a looping procedure to get an average run time. To that end I
tend to just use the Timer function since the elapsed time for all the loops
is normally at least a couple of seconds...
--
HTH...

Jim Thomlinson

Yeah I agree, it does seem that, which is quite good . One could argue all
night how fine a code timer needs to go, but one thing I think we all agree
on is that the help should state its resolution.

--
__________________________________
HTH

Bob

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

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

--
__________________________________
HTH

Bob

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

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

--
__________________________________
HTH

Bob

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


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.


--
__________________________________
HTH

Bob

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


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


:

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?
--
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

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

 

[Bob Phillips]

That is, should be, a principle on all timings, Take multiple shots at it,
and discard the best and worst.

--
__________________________________
HTH

Bob

Note to the Ryan. I have found that run times can vary significantly. It
depends on what else your system might be doing at run time. I personally
do
my tests in a looping procedure to get an average run time. To that end I
tend to just use the Timer function since the elapsed time for all the
loops
is normally at least a couple of seconds...
--
HTH...

Jim Thomlinson

Yeah I agree, it does seem that, which is quite good . One could argue
all
night how fine a code timer needs to go, but one thing I think we all
agree
on is that the help should state its resolution.

--
__________________________________
HTH

Bob

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


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

--
__________________________________
HTH

Bob

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

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

--
__________________________________
HTH

Bob

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


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.


--
__________________________________
HTH

Bob

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


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


:

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?
--
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

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

 

[Bernd P]

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