EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Date & Time Functions VBA TIMER
Secondary Sidebar
VBA Date & Time Functions
  • VBA Date and Time
    • VBA Month
    • VBA DateSerial
    • VBA Date Format
    • VBA Time
    • VBA TIMER
    • VBA Date
    • VBA Format Number
    • VBA DateAdd
    • VBA DateValue
    • VBA DatePart
    • VBA DateDiff
    • VBA Format Date

VBA TIMER

By Ashwani JaiswalAshwani Jaiswal

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.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,142 ratings)

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

Examples of VBA TIMER

Following are the different examples:

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.

VBA Timer - Insert Module

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

VBA Timer

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

VBA Timer

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

VBA TImer

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

Seconds 1 Seconds 2

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

Run the Code

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

Subcategory Name

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.

Wait Function

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

Time Value

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

Application Wait Now

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.

Waiting Time

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

Timer Code

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

MsgBox - Code 1

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

MsgBox Code

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

Message Box

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. VBA On Error
  2. VBA Number Format
  3. VBA Find
  4. VBA TRIM
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more