Timing a VBA code returned a negative time

Question

I ran some code in VBA as per https://www.thespreadsheetguru.com/the-code-vault/2015/1/28/vba-calculate-macro-run-time and had a return of a negative value:

-20439 seconds

Does anyone know why? It actually ran for ~ 18hrs (1500 - 0900 next day)

Option Explicit

Sub CalculateRunTime_Minutes()
'PURPOSE: Determine how many minutes it took for code to completely run
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim StartTime As Double
Dim MinutesElapsed As String

'Remember time when macro starts
  StartTime = Timer

'*****************************
'Insert Your Code Here...
'*****************************

'Determine how many seconds code took to run
  MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")

'Notify user in seconds
  MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation

End Sub

If your code takes 18 hours to run -- perhaps it is in need of [codereview.se] — John Coleman, Feb 21 '18 at 10:01

score 4 · Accepted Answer · answered Feb 21 '18 at 09:34

The code uses Timer.

The Timer returns a Single representing the number of seconds elapsed since midnight. SyntaxTimerRemarks In Microsoft Windows the Timer function returns fractional portions of a second. On the Macintosh, timer resolution is one second. MSDN

Thus, if you start running at 15:00, the code would return something meaningful, if you end up to 23:59. If you end the next day at 09:00, it would return negative value.

You can rebuild the code, in order to get the date in account as well. Use Now, which returns the date and the time - 21.02.2018 10:33:55

This looks like a good possible alternative:

Sub WorkstAtMidnight()

    Dim StartTime As Date
    StartTime = Now()
    'Do something incredible
    MsgBox Round((Now() - StartTime) * 24 * 60 * 60, 0)
    '24 hours times 60 minutes times 60 seconds (usually I just do 24*3600)

End Sub

Nice answer (+1 from me).. also noticed you recently picked up your VBA gold badge... congrats! — CallumDA, Feb 21 '18 at 14:34

score 2 · Answer 2 · answered Feb 21 '18 at 09:54

Another alternative:

MinutesElapsed = Format((Timer - StartTime) / 86400 + IIf(Timer < StartTime, 1, 0), "hh:mm:ss")

This keeps track of the hours and minutes accurately up to a whole day (i.e. it resets at 24 hours of runtime). After which the real question is why does your code take so long!

Timing a VBA code returned a negative time

2 Answers2