How to Use Timer Function in Excel VBA (3 Easy Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


VBA Timer Function Overview

  • 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

syntax of timer in excel VBA

  • Return Value

The Timer function in Windows returns fractions of a second. The Macintosh has a one-second timing resolution.

In this article, I will show 3 effective examples mentioned to show the use of the Timer function. However, the dataset has the least importance here as I will use the codes to run. Follow the following steps one by one.


1. Using the 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.

Using Timer Function in Excel VBA to Calculate the Total Time

  • Consequently, a new module will be opened. Now, you can write the code in this module.

Using Timer Function in Excel VBA to Calculate the Total Time

  • 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

Using Timer Function in Excel VBA to Calculate the Total Time

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.

Using Timer Function in Excel VBA to Calculate the Total Time

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


2. Applying the 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 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


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.

Download Practice Workbook

Please download the workbook to practice yourself.


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


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Souptik Roy
Souptik Roy

Souptik Roy, a BSc graduate in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, dedicated 1.5 years to the ExcelDemy project. During this time, he authored 50+ articles and reviewed 20+ for ExcelDemy. Presently, he is a designer and content developer at YouHaveGotThisMath and Brainor, sister concerns of ExcelDemy. His educational content spans science, mathematics, and grammar. Roy's interests include developing creative ideas, visualizing concepts with tools like Adobe Illustrator, and problem-solving within Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo