Re: How can I time the speed of a macro?

Tech-Archive recommends: Fix windows errors by optimizing your registry

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


"Bob Phillips" wrote:

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

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:OKfvMR1EJHA.5224@xxxxxxxxxxxxxxxxxxxxxxx
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


"Bob Phillips" <BobNGs@xxxxxxxxxxxxx> wrote in message
news:OdtcCt0EJHA.612@xxxxxxxxxxxxxxxxxxxxxxx
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

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:Oq1%23wC0EJHA.5224@xxxxxxxxxxxxxxxxxxxxxxx
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" <BobNGs@xxxxxxxxxxxxx> wrote in message
news:e9FIu8xEJHA.4260@xxxxxxxxxxxxxxxxxxxxxxx
I think it is a lot better resolution than 1/10th, have just got a
result of .015625

--
__________________________________
HTH

Bob

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%2348LFwsEJHA.612@xxxxxxxxxxxxxxxxxxxxxxx
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" <BobNGs@xxxxxxxxxxxxx> wrote in message
news:%23GYcJdsEJHA.4504@xxxxxxxxxxxxxxxxxxxxxxx
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

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:eR0T3gqEJHA.5104@xxxxxxxxxxxxxxxxxxxxxxx
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" <RyanH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0F86C014-2F80-45F9-A181-993E77DA87F9@xxxxxxxxxxxxxxxx
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" <RyanH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DED11A1C-8981-425D-BC8D-1C9835046A0C@xxxxxxxxxxxxxxxx
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" <RyanH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E272FFDE-B7D3-4473-B32C-BF66572099AD@xxxxxxxxxxxxxxxx
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























.

Quantcast