vba - 对 VBA 代码进行计时返回负时间

Question

我按照 https://www.thespreadsheetguru.com/the-code-vault/2015/1/28/vba-calculate-macro-run-time 在 VBA 中运行了一些代码并返回负值:

-20439秒

有人知道为什么吗？它实际上运行了约 18 小时(1500 - 第二天 0900)

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

Answer 1

代码使用定时器。

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

因此，如果您在 15:00 开始运行，如果您最终在 23:59 结束，代码将返回一些有意义的内容。如果您在第二天09:00结束，它将返回负值。

您可以重建代码，以便也获取帐户中的日期。使用 Now，它返回日期和时间 - 21.02.2018 10:33:55

这看起来是一个不错的替代方案:

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