Souptik Roy
In this article, I will show you how to use the VBA Timer function in Excel with 3 easy methods. While working on an Excel sheet, we have to do various kinds of jobs. With Excel VBA, we can solve many real-life problems. Using the Timer function in Excel VBA is one of them. I will show you the use of the Timer function here. I will use three easy methods with step-by-step procedures. Hopefully, you will enjoy the procedures.
Download Practice Workbook
Please download the workbook to practice yourself.
Introduction to VBA Timer Function
- Description
The built-in function called VBA Timer provides us with the fractional value of a second. It is a very helpful function that is occasionally used to halt any collection of executing scripts or to resume them based on the user-provided time. Additionally, the VBA Timer function may be used as a statement with time input.
- Syntax
Timer
- Return Value
The Timer function in Windows returns fractions of a second. The Macintosh has a one-second timing resolution.
3 Effective Examples to Use Timer Function in Excel VBA
In this article, I will show 3 effective examples mentioned following to show the use of the Timer function. Though the dataset has the least importance here as I will use the codes to run. Follow the following steps to one by one.
1. Using Timer Function to Calculate the Total Time
In this method of this article, I will use the Timer function in Excel VBA to calculate the total time. The time shown here may be within a fraction of a second. I will use the Timer function here in the VBA code. Hopefully, you will understand the whole procedure quickly. Follow the following steps one by one.
Steps:
- First, press the Alt + F11 buttons simultaneously. As a result, the Microsoft Visual Basic for Applications window will open.
- Then, select the Insert tab.
- After that, click on the Module option.
- Consequently, a new module will be opened. Now, you can write the code in this module.
- Meanwhile, copy the following code and paste it into the module.
Sub Do_Until_Etample1()
Dim ss_ST As Single
ss_ST = Timer
Dim t As Long
t = 1
Do Until t = 100000
Cells(t, 1).Value = t
t = t + 1
Loop
MsgBox Timer - ss_ST
End Sub
Here, we declared some variables in the VBA code to show the time of the code running in a message box and applied some operators to run the code.
- After running the code, you will find the message box showing the code running time that I have indicated in the next picture.
This is how you can use the Timer function to get the total time a code needs to run.
Read More: Excel VBA to Create Timer Loop (3 Unique Examples)
2. Applying VBA Timer to Get the Actual Time
In this portion of this article, I will show you how to get the actual time by applying VBA Timer. This is a very easy method. The method is also short and handy. Hopefully, you will understand the whole procedure easily. I have added images with every step for your convenience. Follow the steps one by one.
Steps:
- First, open a new module just like the former method.
- Then, write down the following code in the module.
Sub Gettingss_theActualTime()
Dim ss_secondsSince As Single
Dim ss_cTime As Double
Dim ss_theActualTime As Variant
ss_secondsSince = Timer()
ss_cTime = ss_secondsSince / (86400)
ss_theActualTime = Format(ss_cTime, "hh:mm:ss")
MsgBox "The time elapsed since midnight is (in seconds)" & " " & ss_secondsSince & vbNewLine & _
"The time in actual format:" & " " & ss_theActualTime
End Sub
In this code, we declared some variables in the VBA code to show the time of the code running in a message box and applied some operators to run the code. The code will show the time in a message box calculated from midnight. It also shows the seconds and time in hh:mm:ss format.
- After running the code, you will get the time in seconds and in hh:mm:ss format in the message box.
This is how you can get the actual time by applying Timer in Excel VBA.
Read More: How to Create a Timer with Milliseconds in Excel VBA
3. Testing Processor Speed by VBA Timer Function
In this portion of this article, I will insert another VBA code to test processor speed by using the Timer function in Excel VBA. In this method, the speed test is done by changing the variable ss_count last number. This will give you different times for a different processor. This approach is really simple. The procedure is also quick and useful. Hopefully, you’ll find it simple to comprehend the entire process. This process will show the time code needs to run in an actual format. For your convenience, I have included pictures with each step. Observe each step individually.
Steps:
- Open a new Module initially using the same procedure as before.
- Then, enter the subsequent code into the module.
Sub ss_BenchMark()
Dim ss_Count As Long
Dim ss_BenchMark As Double
ss_BenchMark = Timer
For ss_Count = 1 To 500000
Sheet1.Cells(1, 1) = "test run"
Next ss_Count
MsgBox Format((Timer - ss_BenchMark) / 86400, "hh:mm:ss")
End Sub
Here, In this code, we declared some variables in the VBA code to show the time of the code running in a message box and applied some operators to run the code. Here, the Sheet1.Cells(1,1) means the reference is in sheet 1 the cell A1. The code will show the time in a message box. It also shows the seconds and time in hh:mm:ss format.
- At last, after running the code, the command will show the time in a message box in hh:mm:ss format.
This is how you can get time in actual format by using the Timer function.
Read More: How to Create a Countdown Timer in Excel VBA (with Easy Steps)
Things to Remember
- You should bear in mind that every method in this article has VBA codes. So, be careful with the syntax of every code.
- The Timer function can be used only in VBA.
Conclusion
In this article, I have tried to explain how to use the Timer function in Excel VBA. I hope you have learned something new from this article. Now, extend your skill by following the steps of these methods. You will find such interesting blogs on our website Exceldemy.com. I hope you have enjoyed the whole tutorial. If you have any queries, feel free to ask me in the comment section. Don’t forget to give us your feedback.
