Running Excel Macro Automatically Are On Scheduler

Excel macros

One of the critical reasons why many including myself decide to use excel macros is that it can help make some processes much smoother and faster and it also gives more options.

A lesser known but also very useful function in excel macros is having a specific macro run automatically. There are many different ways to do this but you could have a macro start running at a specific time, on the opening of the file or even have a macro run every X minutes. Today, I wanted to give an example where I want a specific macro to be ran every 5 minutes on my computer.

Why would I need this? There are many different reasons and it could be changed depending on your specific need but I thought it would be interesting to look at this example. There are a few things to know about this before going further:

-This is a fairly advanced excel macro functionnality but I think it’s a good example of how powerful excel macros can be
-Obviously, both computer and the excel file need to be open for such a macro to work
-A macro that goes on automatically can be used to do a lot of different things. However, it does not work well if you are doing other things at the same time

If you are new to excel macros, I’d invite you to look at my introduction post here!

Step #1-Defining variables

Some built-in functions in excel make this macro work more easily, you simply need to “call them” with these variables:

Step #2-It can screw up things if you start several such macros at once. To avoid this, I create a cell that indicates if it’s already running and does not start if it is:

Step #3-When the auto macro is “off”, I reach the auto P&L:

The first line is the name of the macro that I want to execute (or you could even put the actual code)
The 2nd line helps me write down the exact time when the macro was last ran
The 3rd line starts the “timer”

Step #4-There isn’t much to do here, I simply start the timer, write down the time of the “next run”, and also write the status of the automatic macro (“on”):

Step #5-It’s important to also have a stop button:) Here is mine:

Here is what the file looks like:)

You can of course the download the spreadsheet here


Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss