Time Calculation - How long a macro takes to run

[Guest]

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]

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

 

[Guest]

Tom,
This is an interesting thread. I do not understand what causes the timer to
stop except if i reaches 100000000.

What will tell it to stop?

 

[Guest]

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