Excel VBA Macro: How to Run a Macro at a Set Time

Complete Excel Excel Training Course. Instant Buy/Download, 30 Day Money Back Guarantee

Got any Excel Questions? Free Excel Help

There are many times when it would be great to have any macro run at a predetermined time or run it at specified intervals. Fortunately Excel has made this a relatively simple task, when you know how.

Application.OnTime

This Method is what we can use to achieve the automatically running of Excel Macros. Let's suppose we have a macro that we wish to Run each day at 15:00 (3:00 PM). The first problem will be how to kick-off the OnTime Method. This we can do via the Workbook Open Event. The fastest way to get to the Private Module of the Workbook Object (ThisWorkbook) is to right click on the Excel icon next to "File" and select "View Code"

Private Sub Workbook_Open()
     Application.OnTime TimeValue("15:00:00"), "MyMacro"

End Sub

Where MyMacro is the name of the macro you wish to run and resides in a Standard Module and has the OnTime Method again like below

Sub MyMacro()

    Application.OnTime 

  TimeValue("15:00:00"), "MyMacro"
'YOUR CODE
End Sub

This will run the Procedure MyMacro at 15:00 each day.

Let's now suppose you want to run this macro (MyMacro) at 15 minute intervals after opening your Workbook. Again we will kick it off as soon as the Workbook Opens so right click on the Excel icon next to "File" and select "View Code". In here put;

Private Sub

  Workbook_BeforeClose(Cancel As Boolean)
   Application.OnTime dTime, "MyMacro", , False

End Sub

Private Sub Workbook_Open()
  Application.OnTime Now + TimeValue("00:15:00"), "MyMacro"

End Sub

Now in any Standard Module (Insert>Module) place this;

Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:15:00")
Application.OnTime dTime, "MyMacro"


'YOUR CODE
End Sub

Note how we pass the time of 15 minutes to the Public Variable dTime This is so we can have the OnTime Method cancelled in the Workbook_BeforeClose Event by setting the optional Schedule argument set to False. If we didn't pass the time to a variable Excel would not know which OnTime Method to cancel as Now + TimeValue("00:15:00")is NOT static, but is when passed to a variable. If we didn't set the optional Schedule argument set to False the Workbook would automatically open every 15 minutes after closing it and run MyMacro

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft Ž and Microsoft Excel Ž are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS

Watch the latest videos on YouTube.com