by Allen Wyatt
(last updated October 17, 2020)
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:
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13790) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
One common type of workbook used in offices is one that contains a single worksheet for each month of the year. If you ...Discover More
Want to know how much of a time difference there is between your machine and a different machine? This tip provides some ...Discover More
Does your macro need to get some input from a user? Here are the ways that Excel provides for that input to be solicited.Discover More
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.