Executing a Macro Every 15 Minutes

Written by Allen Wyatt (last updated June 25, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021


Tony wonders if there is a way to automatically execute a macro every 15 minutes. He would like to do this without impacting other operations in Excel, meaning he doesn't want to be hanging in a loop waiting for a certain time.

The key to doing this type of operation is to use the .OnTime method; it allows you to run a particular macro after a specified period of time. So, for instance, let's say you wanted to run a macro called "MyMacro" every 15 minutes. You could achieve this simply by using the following single-line macro:

Sub StartTimer()
    Application.OnTime Now() + TimeValue("00:15:00"), "MyMacro"
End Sub

Exactly 15 minutes after executing this macro, the MyMacro macro will be automatically executed, regardless of what you are doing elsewhere in Excel. Once the time you specified for the .OnTime method is expired and MyMacro is started, MyMacro will not be run again until such point as you again run the StartTimer macro and .OnTime is again invoked. All this means is that you may want to make a call to StartTimer either the first line of MyMacro or the last line, depending on when you want the timer restarted.

You could, if you desire, modify StartTimer just a bit to allow the passing of a parameter, in this manner:

Sub StartTimer(dMinutes As Double)
    Dim dAdjust As Double

    dAdjust = Now() + (dMinutes / (24 * 60))
    Application.OnTime dAdjust, "MyMacro"
End Sub

This approach provides a bit more flexibility because you can pass, to StartTimer, the number of minutes you want to use with the .OnTime method. For instance, if you wanted to start MyMacro after 12.5 minutes, you could add the following line to your existing code:

Call StartTimer(12.5)

If you add such a line in your MyMacro code, then MyMacro will run every 12.5 minutes forever, as long as Excel is running. If you want to stop the timer and you don't want to stop Excel, then you will need to make a few changes to your macro setup:

Public dTimeStore As Double

Sub StartTimer(dMinutes As Double)
    dTimeStore = Now() + (dMinutes / (24 * 60))
    Application.OnTime dTimeStore, "MyMacro"
End Sub
Sub EndTimer()
    Application.OnTime dTimeStore, "MyMacro", , False
End Sub

Note the declaration of the dTimeStore variable, which is used to store the last time used with the .OnTime method. This allows you to, at some point, call the EndTimer macro and, in turn, the .OnTime method properly to turn off the timer. Note, as well, in the EndTimer macro the presence of two sequential commas in the invocation of .OnTime. This is necessary in order to make sure the parameters are in the proper place as required by the method.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13790) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Inserting Text with a Shortcut Key

The AutoText capabilities of Word are quite powerful, allowing you to insert all sorts of "boilerplate" information in ...

Discover More

Stopping Smart Tags from Being Saved

Don't want Smart Tag information saved with your document? It's easy to make sure that Word doesn't save it, as described ...

Discover More

Finding Based on Displayed Results

Want to use Excel's Find feature to locate cells based on what those cells display? It's easy if you know how to adjust ...

Discover More

Program Successfully in Excel! This guide will provide you with all the information you need to automate any task in Excel and save time and effort. Learn how to extend Excel's functionality with VBA to create solutions not possible with the standard features. Includes latest information for Excel 2024 and Microsoft 365. Check out Mastering Excel VBA Programming today!

More ExcelTips (ribbon)

Relative References when Recording Macros

When you record a macro, make sure that you know how Excel is recording your cell movements. This tip explains the ...

Discover More

Using Macros in Protected Workbooks

Having problems with using macros in a protected workbook? There could be any number of causes (and solutions) as ...

Discover More

Creating a Floating Macro Button

Macros can make your use of Excel much more powerful. If you have a macro that is triggered by an on-screen button, you ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine more than 9?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.