• 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:

  • Moving average in Excel thumbnail
    Moving Average in Excel
  • N Functions in Excel thumbnail
    N Function in Excel
  • Custom sort in Excel
    Custom Sort in Excel
  • SWITCH function feature
    Excel SWITCH Function - Nested IF Function Alternative

Reader Interactions

Leave a Reply Cancel reply

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

Primary Sidebar

Recent Posts

  • Excel SWITCH Function – Nested IF Function Alternative
  • Sum Formulas Only in Excel
  • How to Calculate Age in Excel
  • Custom Sort in Excel
  • Case Sensitive Lookup with XLOOKUP in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

LinkedIn Twitter YouTube

Useful Links

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

Course Topics

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

Join Our Mailing List

© Copyright 2009 Computergaga · All Rights Reserved ·