Delay code execution and get elapsed time
2009-09-04 Time 0 335
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 SubIf 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