Elapsed time to run code

[Otto Moehrbach]

Excel 2002, WinXP
I have the following code to give me the elapsed time for a macro to run.

Time1 = Time
'My code
Time2 = Time
MsgBox Time1 & " " & Time2
MsgBox Format(Time2 - Time1, "00:00:00")

The first MsgBox gives me 2 times that are some 17 seconds apart. That's
accurate.
The second MsgBox shows 00:00:00 no matter what.

What is wrong with the second MsgBox line?

Thanks for all your help. Otto

 

[keepITcool]

you'll never get reliable timings like that


for "reasonable" timers use s't like:

dim lTime&
lTime = application.timer *1000
'code
lTime = -lTime+application.timer *1000
messagebox ltime & "msecs"

I believe that application.timer ticks each 35 or 55 milliseconds
(or sometihng)

So for benchmarking you;ll need to run your codes a few times
(or a few 1000 if your testing functions

For far more reliable timings you'll need API functions.
I always use CStopWatch from KarlPeterson.
http://www.mvps.org/vb/samples.htm






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Otto Moehrbach wrote :

 

[pfsardella]

Otto,

Substitute this statement for "MsgBox Format(Time2 - Time1,
"00:00:00")"

MsgBox Format(Time2 - Time1, "hh:mm:ss")

HTH
Paul

 

[Tom Ogilvy]

From help

Returns a Single representing the number of seconds elapsed since midnight.

so you need to divide this number by the number of seconds in a day


MsgBox Format((Time2 - Time1)/86400, "hh:mm:ss")

But see KeepItCool's post on using other methods for timing.

 

[Chip Pearson]

Otto,

Your Format string is wrong. Change "00:00:00" to "hh:mm:ss".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com