![](https://codky.com/wp-content/uploads/2024/01/How-to-use-Timer-function-in-VBA-1024x576.png)
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
Help us grow the project