Delay code execution and get elapsed time

 2009-09-04    Time    0    144

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