Time Calculation - How long a macro takes to run

[cdb]

I am trying to add into my macro a bit of code that times how long it takes
to execute, but I can't seem to get it right.

As a test I have been using the following code:

Sub test2()

i = 1

starttime = TimeValue(Time)

While i < 100000000
i = i + 1
Wend

endtime = TimeValue(Time)
timecalc = TimeValue(starttime) - TimeValue(endtime)

MsgBox (starttime & endtime & timecalc)

End Sub

But when I output the result (timecalc) it gives a really wierd number
(-1.85185185185233E-04). I have tried it without the TimeValue statement too
and get the same sort of result.

Is there an easy way of substracting the start time from the end time??

 

[Tom Ogilvy]

Sub test2()

i = 1

starttime = Timer

While i < 100000000
i = i + 1
Wend

endtime = Timer
timecalc = EndTime - StartTime

MsgBox (starttime & endtime & timecalc)

End Sub

Measures the number of seconds.

(subtracting the endtime from starttime would give a negative number).

 

[cdb]

Cheers Tom, works a treat. Is there any way in displaying this in mm:ss
format (as the real code I will use will take several minutes to run)

 

[Tom Ogilvy]

from the immediate window:

numSeconds = 1021
? format(numSeconds/86400,"hh:mm:ss")
00:17:01

so dividing the number of seconds by 86400 and then using the format command
should work

MsgBox format( timecalc/86400,"hh:mm:ss")

 

[cdb]

Works a treat - many thanks again for the help.

One thing puzzles me though. Why do you have to divide by 84000? I know this
is the number of seconds in a day, but why do you have to divide the number
of seconds in the timer by the number of seconds in the day to convert to
hh:mm:ss format??

Any ideas?? I'm not in that much of a need to know, was just wondering the
logic behind it.

 

[Tom Ogilvy]

Time and dates are one format. Excel records dates (and time) as the
elapsed number of days since a base date. so 6 hours would be represented
as 0.25 and 30 hours as 1.25 (one day and 6 hours).

so you have to convert you time to the number of days (thus divide by
24*60*60)

Intrigued? See Chip Pearson's page on this topic:

http://www.cpearson.com/excel/datetime.htm

 

[cdb]

The timer will stop when it gets to 100000000 only - that is the only
criteria in the below code.