VBA TIMER

VBA TIMER

Excel VBA TImer is used for detecting and calculating the time passed for completing any activity. In Excel VBA TIMER function can be used in many ways. We can calculate the time to complete any activity, we can calculate the time required to reach from start to end phase of any activity or even we can calculate the time required to click on any function as well.

This function cannot be performed in excel. In excel we can only see the time and add it to any cell but cannot measure it as Timer.

Example #1

In this example we will, how to count or measure time to run the completed code. For this go to insert menu of VBA and select a Module as shown below.

Once we do that, it will open a new module as shown below. Now in that write the Subcategory of current function name or any other name as per your choice.

Code:

Sub Timer1()

End Sub

Now define two dimensions as Seconds1 and Seconds2 as a SINGLE function, which means there were two individual single number data (Without decimal).

Code:

Sub Timer1()

    Dim Seconds1 As Single
    Dim Seconds2 As Single

End Sub

Now to Start the timer first select defined dimension Seconds1 and assign the function TIMER. And do the same thing for other dimension Seconds2 as shown below. The purpose of doing is to measure the start and end the time.

Code:

Sub Timer1()

    Dim Seconds1 As Single
    Dim Seconds2 As Single

    Seconds1 = Timer()
    Seconds2 = Timer()

End Sub

This completes the timer portion of the code. Now we need to see the time lapse in running the code. For this, we need to print the output in the message box as shown below.

In below screenshot, we printed a text “Time taken:” and the difference between Seconds2 and Seconds1 with unit seconds.

Code:

Sub Timer1()

    Dim Seconds1 As Single
    Dim Seconds2 As Single

    Seconds1 = Timer()
    Seconds2 = Timer()

    MsgBox ("Time taken:" & vbNewLine & Seconds2 - Seconds1 & " seconds")

End Sub

Once done, run the complete code by using F5 key or clicking on the play button as shown below.

We will see a message box with a counted time of 0 Seconds as the time required to run the complete code was 0.

We may see slightly more difference if the written code is huge.

Example #2

There is another method where we can choose a time lapse of any small amount as waiting time and let user or operator wait till the process gets completed. This can be used where we create a tool or macro with a huge line of code structure. By doing this we will allow the user to wait time whole code get to run, and operation is completed. Because doing something when the code is running may crash the file.

For this open a new module and write the Subcategory in the name of the required function or any name as shown below.

Code:

Sub Timer2()

End Sub

Now to make this code small and simple we will use the inbuilt functions of VBA. And for this type Application followed a dot (.) and then from the list search and select Wait function as shown below.

This Wait function will allow us to add waiting time till the complete code may get run. This wait time is Boolean. After that consider the time when we run is 0 seconds with plus time we want to show as waiting time will be shown by TimeValue as shown below.

Code:

Sub Timer2()

  Application.Wait Now + TimeValue("00:00:10")

End Sub

Here we consider 10 seconds as waiting time to complete the code run.

Now to print the waiting time we need to print the message in the message box with the help of command MsgBox as shown below.

Code:

Sub Timer2()

  Application.Wait Now + TimeValue("00:00:10")

  MsgBox ("Waiting Time - 10 Seconds")

End Sub

As we can see, in message box we are added the text of “Waiting time – 10 Seconds” to be printed.

Now run code using F5 key or manually. We will see after we waited for 10 seconds, a message box will appear with the message used in the code.

Example #3 – VBA Timer

There is another easy way to see and show the current time in VBA. For this, we will directly use MsgBox and the rest of the code there only. For this open a new module in VBA and write Subcategory in the name of the used function or any other name as shown below.

Code:

Sub Timer3()

End Sub

Now write MsgBox which is a command to print the message. In the bracket, we can write any message to be printed in the message box. Here we have chosen “Time is:” as our text and along with it “& Now ()” is used. It will display the current time with date in the pop-up message box.

Code:

Sub Timer3()

   MsgBox ("Time is : " & Now())

End Sub

Now in another message box, we will count the number of seconds passed in the whole day till current time by the clock. For this write MsgBox and between the brackets write the text “Timer is:” along with “& Timer ()” as shown below.

Code:

Sub Timer3()

   MsgBox ("Time is : " & Now())
   MsgBox ("Timer is: " & Timer())

End Sub

Once done, run the code using the F5 key or manually. We will get two separate message boxes as shown below.

In the first message box, we will get the current date and time in DD/MM/YYYY and hh:mm:ss AM/PM format which is the default format Excel. In the second message box, we will see time lapsed in that day in seconds.

The second message box shows Timer is 59953.62 Seconds. If we divide that with 60 Seconds and 60 Minutes we will get the exact timer in hours lapsed in that day which is 16.65 Hours.

Things to Remember About VBA TIMER

• Always save the file in Marco Enabled Workbook to avoid loss of written VBA Code.
• Always compile the complete code step by step to make sure every code is incorrect.
• In example#1 SINGLE function is used to show the number as a whole. Even if we use DOUBLE it will give the result in decimal.

Recommended Articles

This has been a guide to Excel VBA TIMER. Here we discussed how to use VBA TIMER function along with some practical examples and downloadable excel template. You can also go through our other suggested articles–

1. How to use VBA On Error Statement?
2. Number Format in VBA
3. How to use VBA Find Function?
4. Tutorials on VBA TRIM Function