VBA Timer

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA TIMER Function

VBA Timer is an inbuilt function that gives us the fractional value of seconds. It is a very useful function used sometimes to pause any set of codes running or resume them based on the time provided by the user. In addition, one may use the Timer function as a statement in VBA with time input.

In simple terms, the TIMER gives the total number of seconds gone from midnight of the current day. Right from line one of the code, we can track the time consumed by our code to complete the process specified in the subprocedure.

Sometimes when you write a code. You want to test the code duration, i.e., the total time your code takes to complete the subprocedure. By testing the actual duration taken by your code, you can make your code efficient and eliminate the time-consuming process by deleting unwanted or long codes from your module.

VBA Timer

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Timer (wallstreetmojo.com)

How to use the TIMER Function in VBA?

As we told you, the TIMER function returns the total seconds passed from midnight to the current date. So, when we write this article, the time is 13:50:45 in India.

We have created a Macro name and assigned the value of TIMER in the VBA message boxThe VBA Message BoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

Code:

Sub Timer_Example1()

MsgBox Timer

End Sub
vba timer example 1

When we ran this code, we got the result of 50480.08.

vba timer example 1.1

The total seconds went past today’s midnight, i.e., from 12:00:00 AM.

So, from midnight to the current time, 14:01:20, a total of 14 hours, 1 minute, and 20 seconds have passed. Therefore, in seconds it is equal to 50480.08, given by our TIMER function.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Examples

You can download this VBA Timer Excel Template here – VBA Timer Excel Template

Example #1 – Calculate the Total Time Taken by your Code

Now, we will perform some simple coding to test the time the VBA takes to execute the procedure. But, first, we have written some code, as shown in the below image.

Code:

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
vba timer example 2.1

If we run this code now, it will show me the total time the VBA takes to execute.

vba timer example 2.2

It says 3.058594. The result given by this function is in seconds, i.e., the total time taken by this code is 3.058 seconds.

For you to use the code, we have written the below code for you.

Code:

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
vba timer example 2.3

Use the above and type your code after the code StartingTime = Timer, but before the code MsgBox Timer – StartingTime, i.e., in a green area, you need to enter your code.

Explanation: Firstly, the variable StartingTime = Timer means that the time of running the code equals the time elapsed from midnight to code running time.

Timer – StartingTime: After running the code, what is the time elapsed minus time recorded at the beginning of the code through the variable starting time?

It 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 a proper format. However, we can apply a VBA timeVBA TimeThe VBA time function returns the current time. This function has no arguments and returns the current system time. Thus, using this function, we can find the actual time taken by the line of codes to complete the process.read more format to the result using the FORMAT function.

Use the below code to see the result in the correct time format, i.e., “hh: mm: ss” format.

example 3.1

We have used the FORMAT function here. The result is given by (Timer – starting time). First, we divided it by the number 86400 to convert it to seconds as per time format rules. Then, we applied the time format in an hour, minute, and second format.

Now, if we run the code, it will give the result like this.

example 3.2

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. So, we paused my code running for a minute (using Ctrl + Break) and saw the result.

example 3.3

So, the total time this code takes is 1 minute 2 seconds.

Example #3 – Alternative Code to Timer

There is an alternative to TIMER by using the NOW () function. Below is the alternative code.

example 4

Things to Remember

  • The TIMER function will rest the value at the end of the day, i.e., at 11:59:59 PM.
  • The NOW function returns the current date and current time.
  • The TIMER function shows the total seconds gone past from the current date of midnight.

Recommended Articles

This article has been a guide to VBA Timer. Here, we learned how to use the Timer function in Excel VBA, its alternative function, and some simple to advanced examples. Below are some useful Excel articles related to VBA: –