You can use OnTime to schedule a macro to be kicked off at a specific time during the day. By default, this is True.Įxample 1: Schedule a macro to run 30 seconds from now Application.OnTime Now + TimeValue("00:00:30"), "myMacro"Įxample 2: Schedule a macro to run at 9 pm today Setting this optional parameter to False can cancel a previously scheduled task. If the current time exceeds this time, the procedure won’t be run (and the OnTime instruction will be considered as expired). The latest time at which the procedure can be run. Name of the macro to run at the EarliestTime. (See also the parameter LatestTime below.) The procedure will be kicked off as soon as Excel is ready. any macro is running) at the time of EarliestTime. Sometimes Excel could be busy with other tasks (e.g. The time when you want the Procedure to be run. Only EarliestTime and Procedure are madatory inputs. The OnTime method has 4 input parameters.
This allows you to, at some point, call the EndTimer macro and, in turn, the. Note the declaration of the dTimeStore variable, which is used to store the last time used with the. 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:ĭTimeStore = Now() + (dMinutes / (24 * 60))Īpplication.OnTime dTimeStore, "MyMacro",, False 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. For instance, if you wanted to start MyMacro after 12.5 minutes, you could add the following line to your existing code: This approach provides a bit more flexibility because you can pass, to StartTimer, the number of minutes you want to use with the. You could, if you desire, modify StartTimer just a bit to allow the passing of a parameter, in this manner: 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. 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. You could achieve this simply by using the following single-line macro:Īpplication.OnTime Now() + TimeValue("00:15:00"), "MyMacro"Įxactly 15 minutes after executing this macro, the MyMacro macro will be automatically executed, regardless of what you are doing elsewhere in Excel. So, for instance, let's say you wanted to run a macro called "MyMacro" every 15 minutes. OnTime method it allows you to run a particular macro after a specified period of time. The key to doing this type of operation is to use the. 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.
Tony wonders if there is a way to automatically execute a macro every 15 minutes.