How to use Timer function in VBA?

In VBA (Visual Basic for Applications), the `Timer` function is used to calculate the number of seconds that have elapsed since midnight (00:00:00). It can be helpful when you need to track the execution time of code or create a simple delay within a procedure.

Here’s how you can use the Timer function in VBA:

Sub CalcElapsedTime()
    Dim startTime As Double
    Dim endTime As Double
    Dim elapsedTime As Double

    startTime = Timer ' Capture the start time
    
    ' Place the code whose execution time you want to measure here
    Application.Wait (Now + TimeValue("0:00:02")) ' Example: Wait for 2 seconds

    endTime = Timer ' Capture the end time
    
    elapsedTime = endTime - startTime ' Calculate elapsed time
    
    ' Display the elapsed time
    MsgBox "The code execution took " & elapsedTime & " seconds."
End Sub

Sub Delay(seconds As Single)
    Dim endTime As Double
    endTime = Timer + seconds ' Calculate end time based on the specified delay
    Do While Timer < endTime
        DoEvents ' Yield to the operating system (allow user interaction with Excel)
    Loop
End Sub

Directly using Timer to calculate elapsed time:
Creating a delay using Timer:

You can then call the `Delay` sub like this:

Sub ExampleUsingDelay()
    MsgBox "Wait for 5 seconds."
    Delay 5 ' Wait for 5 seconds
    MsgBox "5 seconds have passed."
End Sub

Remember that the `Timer` function doesn’t create a high-precision timer. It returns a Single representing the fractional number of seconds, which may vary in accuracy. Also, when using delays or measuring code execution time with the `Timer` function, be aware that if your code runs through midnight, you’ll need to account for the Timer value resetting to 0.

For more precise timing or when you require functionality beyond that which the `Timer` function provides (e.g., pausing execution for less than a second), you might need to use the Windows API functions such as `QueryPerformanceCounter` and `QueryPerformanceFrequency`. However, these functions are more complex and require declaring and using external functions with `Declare` statements.

facebooklinkedin