Delay code execution and get elapsed time
2009-09-04 Time 0 753
If you need to delay something or want to get the elapsed time and don't need an accuracy better than 1 second, you can use the information in the example macro below:
Sub TimerExample1()
' accuracy in seconds
Dim s As Double, e As Double, i As Long
Dim dblElapsedTime As Double
s = Now ' start date and time
For i = 1 To 25
ActiveSheet.Calculate ' do something
Next i
e = Now ' end date and time
dblElapsedTime = e - s ' in seconds
MsgBox "Elapsed Time: " & Format(dblElapsedTime, "hh:mm:ss") & _
" (" & Format(dblElapsedTime * 86400, "0.00") & " seconds)", vbInformation
s = Now ' start date and time
Application.Wait Now + TimeValue("00:00:03") ' pause code execution for 3 seconds
e = Now ' end date and time
dblElapsedTime = e - s ' in seconds
MsgBox "You waited for " & Format(dblElapsedTime, "hh:mm:ss") & _
" (" & Format(dblElapsedTime * 86400, "0.00") & " seconds)", vbInformation
End Sub
If you need to delay something or want to get the elapsed time and you need an accuracy that is better than 1 second, you can use the functions and procedures below:
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) ' pause code execution
Declare Function GetTickCount Lib "kernel32" () As Long ' milliseconds elapsed since Windows was started (accuracy 10-16 ms)
' Declare Function GetTickCount64 Lib "kernel32" () As Long ' milliseconds elapsed since Windows was started (accuracy 10-16 ms)
Function GetTickCountDifference(lngStart As Long, lngEnd As Long) As Long
' assumes input times in milliseconds, returns the difference in milliseconds
If lngEnd < lngStart Then
' assumes that the tick count has been reset
' this happens when the tick count is greater than 2^32, approximately every 49.71 days
lngEnd = lngEnd + 2 ^ 32
End If
GetTickCountDifference = lngEnd - lngStart
End Function
Sub TimerExample2()
' accuracy in milliseconds
Dim s As Long, e As Long, i As Long
Dim lngElapsedTime As Long
s = GetTickCount ' start time
For i = 1 To 25
ActiveSheet.Calculate ' do something
Next i
e = GetTickCount ' end time
lngElapsedTime = GetTickCountDifference(s, e) ' in milliseconds
MsgBox "Elapsed Time: " & Format(lngElapsedTime / 1000 / 86400, "hh:mm:ss") & _
" (" & Format(lngElapsedTime / 1000, "0.00") & " seconds)", vbInformation
s = GetTickCount ' start time
Sleep 2750 ' pause code execution for 2.75 seconds
e = GetTickCount ' end time
lngElapsedTime = GetTickCountDifference(s, e) ' in milliseconds
MsgBox "You waited for " & Format(lngElapsedTime / 1000 / 86400, "hh:mm:ss") & _
" (" & Format(lngElapsedTime / 1000, "0.00") & " seconds)", vbInformation
End Sub