Timer running past midnight

[ernieL]

I use the following code to determine the minutes a program runs. It works as long as the program does not run past midnight when the Timer resets to zero and gives an invalid ElapsedTime. How do I modify the code so that the correct ElapsedTime is returned?

StartTime = Timer 'Get starting time
...program goes here...
EndTime = Timer 'Get ending time
ElapsedTime = (EndTime - StartTime) / 60

Regards,
ernieL

 

[phantom1975]

Have you considered having StartTime = Now and EndTime = Now and then calculate ElapsedTime = (EndTime - StartTime) *86400? I'm not sure what Timer is doing. Basically, you will capture the date and time and set that value to StartTime and then do the same thing for EndTime. What I don't know about your code is how you are capturing the StartTime and the EndTime.

 

[joeu2004]

How do I modify the code so that the correct ElapsedTime is returned?

StartTime = Timer 'Get starting time
...program goes here...
EndTime = Timer 'Get ending time
ElapsedTime = (EndTime - StartTime) / 60

StartTime = Timer 'Get starting time
...program goes here...
EndTime = Timer 'Get ending time
ElapsedTime = EndTime - StartTime
If ElapsedTime < 0 Then ElapsedTime = ElapsedTime + 86400
ElapsedTime = ElapsedTime / 60

 

[joeu2004]

Have you considered having StartTime = Now and EndTime = Now and then calculate ElapsedTime = (EndTime - StartTime) *86400?

FYI, VBA Now is accurate to only 1 second. Generally not sufficient for measuring elapsed time. [1]

We might be tempted to do StartTime = Date + Timer/86400.

But near midnight, the date might change between the calls to Date and Timer, and Timer might reset to zero.

So to be reliable, we should do something like the following:

StartTime = Date + Timer/86400
If Date <> Int(StartTime) Then Starttime = Date + Timer/86400

Seems a bit tedious. See my previous response for a simpler alternative, IMHO.


-----
[1] Excel Now is accurate to 0.01 seconds. Actual time is truncated to the 0.01 second. Consequently, delta time is understated. VBA Timer and system time is accurate to 0.015625 seconds, with a precision of 1 microsecond.

Of course, QueryPerformanceCounter is more accurate.