VBA Timer - 减去等待用户输入的时间

Question

我正在使用在 thespreadsheetguru.com 上找到的这种方法来计算运行宏所需的时间:

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

它对我来说非常有效，但在我的代码中，有多个用户输入提示。有些只是 OK 按钮，有些是带有多个按钮的用户窗体。

如何计算运行宏所需的时间，减去等待用户单击按钮所花费的时间？

Answer 1

根据您的代码，如下所示:

Sub CalculateRunTime_Minutes()
Dim StartTime1 As Double, StartTime2 As Double, StartTime3 As Double
Dim StopTime1 As Double, StopTime2 As Double
Dim MinutesElapsed As String

StartTime1 = Timer
'your code
StopTime1 = Timer
'user input1

StartTime2 = Timer
'your code
StopTime2 = Timer
'user input2

StartTime3 = Timer
'your code
MinutesElapsed = Format(((Timer - StartTime3) + (StopTime2 - StartTime2) + (StopTime1 - StartTime1)) / 86400, "hh:mm:ss")

MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation

End Sub