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: 1. Directly using Timer to calculate elapsed time:

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
2. Creating a delay using Timer:

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
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.

Unlock Your Potential

Excel

Basic - Advanced

Access

Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *