• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / VBA / Use the OnTime Method in Excel VBA

Use the OnTime Method in Excel VBA

The OnTime method of the Application object in Excel VBA occurs at a specific time of the day.

It can be used to schedule procedures to be run at specified times of the day, or at specific time intervals.

OnTime Method Parameters

The OnTime method requires the following parameters.

.OnTime(EarliestTime, Procedure, LatestTime, Schedule)

EarliestTime – The time that you want the procedure to run (required)

Procedure – The name of the procedure to be run, entered as a string (required)

LatestTime – The latest time that the procedure should be run

Schedule – Set to True to schedule a new OnTime procedure, or False to cancel the OnTime procedure. The default is set to True

Use the OnTime Method to Run a Procedure

Let’s use the OnTime method to run a procedure every day at 9 am. The procedure will display a message box to let the user know that it is time for their morning coffee.

The first thing we need is a way of initiating the method. The best way to do this is to use the Workbook Open event.

  1. Press Alt + F11 to open the Visual Basic Editor window.
  2. Double click on the ThisWorkbook object in the Project Explorer to open its Module Window.
  3. Click on the Object list arrow and select Workbook.
  4. Click on the Procedure list arrow and select Open.
  5. Enter the code Application.OnTime TimeValue(“09:00:00”), “message” within the procedure as shown below.
OnTime method in Excel VBA to run a procedure at a specific time
  1. This code will initiate the message procedure and schedule it to run at 9 am. The TimeValue function is used to convert the text string to a time value.
  2. The message procedure resides in a general module and looks like below.
Sub message()
MsgBox "Time for your morning coffee!!"
End Sub

Cancel the OnTime Method

Procedures scheduled by the OnTime method will run at their specified time every day, even if the workbook is closed.

If you only want a procedure to be run if the workbook is open, then the OnTime method can be cancelled by using the Workbook BeforeClose event.

The following code can be entered in the BeforeClose event to cancel the

OnTime method.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime TimeValue("09:00:00"), "message", , False
End Sub

Related Posts:

  • Excel FILTER Function – The Best Function in Excel
    Excel FILTER Function – The Best Function in Excel
  • Advanced Excel Skills
    Advanced Excel Skills
  • Entering Fractions in Excel
    Entering Fractions in Excel
  • Lookup Multiple Values in Excel
    Lookup Multiple Values in Excel

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • How to Hyperlink to a Hidden Worksheet in Excel
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·