Run a macro every 30 seconds, 1 minute, 10 minutes, 1 hour, etc.; this method allows you to run a macro at any set interval.
Sub macro_timer()
'Tells Excel when to next run the macro.
Application.OnTime Now + TimeValue("00:00:10"), "my_macro"
End Sub
Sub my_macro()
'Macro code that you want to run.
MsgBox "This is my sample macro output."
'Calls the timer macro so it can be run again at the next interval.
Call macro_timer
End Sub
This code requires two separate macros in order to run.
This specific setup gets the interval going. In order to be able to stop the interval when you want, look to the next section below.
Below, I'll separate the macros and talk about each one.
Sub macro_timer()
'Tells Excel when to next run the macro.
Application.OnTime Now + TimeValue("00:00:10"), "my_macro"
End Sub
This is the timer macro. It is currently set to run another macro, called my_macro, 10 seconds after this timer macro is run.
my_macro is the name of the macro that you want to run each interval.
TimeValue("00:00:10") is the part of the code that says how long to wait before running the macro; this is the interval. Currently, this is set to run the macro every 10 seconds. The first set of zeros is for hours, the second for minutes, and the third for seconds.
Interval time examples:
TimeValue("05:00:00") run every 5 hours.
TimeValue("00:10:00") run every 10 minutes.
TimeValue("00:00:30") run every 30 seconds.
TimeValue("01:30:00") run every 1.5 hours.
Sub my_macro()
'Macro code that you want to run.
MsgBox "This is my sample macro output."
'Calls the timer macro so it can be run again at the next interval.
Call macro_timer
End Sub
This is the regular macro that has the code that you want executed at every interval.
You only need to make sure that, at the end of this macro, it calls the original timer macro.
Call macro_timer is the line that must be at the end of your macro. macro_timer is simply the name of the first macro in this example and Call is what tells the macro to "call" or run the other macro.
The easiest way to stop a macro that runs on an interval is to hit Ctrl + Break on the keyboard.
Here is our tutorial on stopping a macro from running.
You can also use this setup:
Full code:
Public interval As Double
Sub macro_timer()
interval = Now + TimeValue("00:00:10")
'Tells Excel when to next run the macro.
Application.OnTime interval, "my_macro"
End Sub
Sub my_macro()
'Macro code that you want to run.
MsgBox "This is my sample macro output."
'Calls the timer macro so it can be run again at the next interval.
Call macro_timer
End Sub
Sub stop_macro()
Application.OnTime earliesttime:=interval, procedure:="my_macro", schedule:=False
End Sub
To stop the macro using this setup, just run the stop_macro.
This setup is almost exactly the same as the first setup above except that you need one line of code above everything else, need to set the interval as a variable, and need a macro to stop the interval.
Public interval As Double this makes the variable "interval" a variable that can be accessed across different macros.
interval = Now + TimeValue("00:00:10") the time value is stored in the interval variable instead of hard-coding it into the Application.OnTime method. This new variable is then used within the Application.OnTime method for the first parameter.
Sub stop_macro()
Application.OnTime Earliesttime:=interval, Procedure:="my_macro", Schedule:=False
End Sub
This macro simply contains another Application.OnTime method and it must have the same parameters as the initial Application.OnTime method, except here, the Schedule parameter must be included and set to false.
In this macro, the parameters are explicitly named, which is why you see Earliesttime:= and Procedure:= and Schedule:= written out, whereas you don't see that in the initial macro setup in the first section above.
The macros in the attached workbook are from the last example since that is the most versatile method to use.
Download the sample file to get these examples in Excel.