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
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