Run a Macro at Set Intervals in Excel

Add to Favorites
Author:

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.

Sections:

The Code

Stop an Interval Macro

Notes

The Code

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.

Macro that sets the interval

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.

Macro that does something

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.

Stop an Interval 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:

96c95df267b24db656932d6dd4e1e0fe.jpg

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.

One extra line at the top

Public interval As Double this makes the variable "interval" a variable that can be accessed across different macros.

Interval variable

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.

Stop macro

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.

Notes

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.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
Macro: Automatically run an Excel macro at a certain time. This allows you to not have to worry a...
Run Macros at Set Intervals in Excel
Tutorial: [files in side column or below here]...
Automatically Run a Macro so Many Seconds, Minutes, or Hours After an Excel Workbook has been Opened
Macro: Run a macro after a certain amount of time has passed since the Excel workbook was ope...
Stop Excel Events from Triggering when Running a Macro
Tutorial: Create a macro that will run without triggering events, such as the Change event, or Activ...
Disable Calculation of Cells when Running a Macro in Excel
Tutorial: How to stop formulas and functions from updating in Excel when running a macro. This can s...
Run a Macro when a User Does Something in the Workbook in Excel
Tutorial: How to run a macro when a user does something within the Workbook in Excel, such as openi...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course