Excel VBA TIMER Function
VBA Timer is an inbuilt function used to give us the fractional value of seconds, it is a very useful function which is used to sometimes pause any set of codes running or resume them on the basis of the time provided by the user, timer is simply used as a statement in VBA with the input of time.
In simple terms, the TIMER gives the total number of seconds gone from the midnight of the current day. Right from line one of the code, we can actually track the time consumed by our code to complete the process specified in the subprocedure.
Sometimes when you write a code and you want to test the code duration i.e. what is the total time taken by your code to complete the subprocedure. By testing the actual duration taken by your code you can make your code efficient and eliminate time-consuming process by deleting unwanted or long codes from your module.
How to use the TIMER Function in VBA?
As I told TIMER function returns the total seconds gone past from the midnight of the current date. When I am writing this article time is 13:50:45 in India.
Sub Timer_Example1() MsgBox Timer End Sub
When I run this code I got the result as 50480.08.
This is the total seconds went past from today’s midnight i.e. from 12:00:00 AM
So from midnight 12 to current time 14:01:20, a totally 14 hours 1 minute 20 seconds gone past. In seconds it is equal to 50480.08 which is given by our TIMER function.
Example #1 – Calculate the Total Time Taken by your Code
Now we will perform some simple coding to test the time taken by the VBA to execute the procedure. I have written some code as shown in the below image.
Sub Do_Until_Example1() Dim ST As Single ST = Timer Dim x As Long x = 1 Do Until x = 100000 Cells(x, 1).Value = x x = x + 1 Loop MsgBox Timer - ST End Sub
If I run this code now it will show me total time taken by the VBA to execute.
It says 3.058594, the result given by this function is in seconds i.e. total time taken by this code is 3.058 seconds.
For you to use the code I have written the below code for you.
Sub Timer_Example1() Dim StartingTime As Single StartingTime = Timer 'Enter your code here 'Enter your code here 'Enter your code here 'Enter your code here MsgBox Timer - StartingTime End Sub
Use the above and type your code after the code StartingTime = Timer but before the code MsgBox Timer – StartingTime i.e. in green area you need to enter your code.
Explanation: Firstly the variable StartingTime = Timer means at the time of running the code is equal to the time elapsed from midnight to code running time.
Timer – StartingTime: It means after running the code what is the time elapsed minus time recorded at the beginning of the code in through variable StartingTime.
This will give the difference between start and end time and return the result.
Example #2 – Show the Result in the Correct Time Format
As we have seen the result given by the function is in seconds but not in the accurate format. However, we can apply a VBA time format to the end result by using the FORMAT function.
Use the below code to see the result in the correct time format i.e. “hh: mm: ss” format.
I have used FORMAT function here. The result is given by (Timer – StartingTime). I have divided it by the number 86400 to convert it to seconds as per time format rules, then I have applied the time format in an hour, minute, and second format.
Now if I run the code it will give the result like this.
So, the total time taken by the code is 3 Seconds.
The beauty of this code is the moment it crosses 60 seconds it will show the result in minutes. I have paused my code running for a minute (using Ctrl + Break) and see the result.
So the total time taken by this code is now 1 minute 2 seconds.
Example #3 – Alternative Code to Timer
There is an alternative to TIMER by using NOW () function. Below is the alternative code.
Things to Remember
- TIMER function will rest the value at the end of the day i.e. at 11:59:59 PM.
- NOW function returns the current date and current time.
- TIMER shows the total seconds gone past from the current date midnight.
This has been a guide to VBA Timer. Here we learned how to use the Timer function in Excel VBA and its alternative Function along with some simple to advanced examples. Below are some useful excel articles related to VBA –