A while back, I wrote about creating a timer in Excel. In that post, I went into detail about my failed attempts to create one, and how I eventually solved the problem. You can catch up on the blog here.
If you'd rather not read all that detail, though, I'm going to give a short version here.
Why?
First, let's talk about why you might need a timer. Let's say that your Excel sheet has a macro that lists all files in a given folder. Every five minutes, you want to refresh that list. You'll need to write some code to check that directory, and schedule it to run every five minutes.
I've written a simple procedure that reads the files from the folder in question, which looks like this:
This procedure works great. Here are the contents of my folder:
When I run the CheckFiles procedure, the result is this:
Perfect.
Running It On A Timer
Now I need to get that CheckFiles procedure to run automatically on a schedule. In order to do that, I'll add code to the end of this procedure which will automatically re-run the procedure 5 minutes later:
Let's break that code down.
First I create a variable called "NextRunTime".
The I add 5 minutes to the current time (obtained with the "Now" function). This tells me when I next want the procedure to run.
Finally, I use the "Application.OnTime" method to set the timer. I tell it that it should run the "CheckFiles" procedure at the Next Run Time.
So, I've basically made this procedure recursive. Every time it runs, it'll set itself up to run again in 5 minutes.
Getting Things Started
There are two more things I need to do: the initial kick-off, and the final shut-down.
When the file is opened, I need to kick off this procedure. Since no timer is currently running, I'll need to run the procedure immediately on opening, which, of course, will start the cycle of 5-minute runs.
This is a simple matter of calling the CheckFiles procedure using the Workbook_Open event procedure, which is in the "ThisWorkbook" object:
Shutting it down
Lastly, you need to shut down the timer when the workbook is closed. You might think this is unnecessary, but as long as Excel remains open, this timer will stay active. If you've closed the workbook when the timer goes off, it'll be automatically re-opened.
To head this off, I'll need to add code to the Workbook_BeforeClose procedure. This code will stop the active timer. In order to do so, I need to know the exact parameters that were used to start the timer.
If I started the timer with: Application.OnTime "8/8/2023 14:25:03", "CheckFiles"
I shut it down with the exact same command, but with one extra argument:
Application.OnTime "8/8/2023 14:25:03", "CheckFiles", False
The "False" at the end indicates that the timer should be cancelled.
The trick here is to make sure that the start date/time is EXACTLY the same. If I don't use the exact same date and time, it won't find the existing timer.
That means that I have to save the NextRunTime variable in a place where the Workbook_BeforeClose procedure can read it. In my previous code example, I've declared that variable with a "Dim" statement in the "CheckFiles" procedure, so it can't be accessed outside of the "CheckFiles" procedure.
The fix is simple. I'll move the "dim" statement to the "General Declarations" section of the module, and make it Public. That way, it's accessible to any code module in the file, including the "Workbook_Open" and "Workbook_Close" procedures. Here's what that looks like:
Now I add the Workbook_BeforeClose procedure with the command to shut down the timer:
EDIT 9/14/2023: DOH! The above procedure was missing a couple of arguments. It should look like this:
See those last two arguments? You need them in order to shut the procedure down. The first is "Last Time", which is the latest time the timer will run. In my experience, that argument doesn't seem to do anything. You can read about my experience in a previous post, here.
The other argument, though, is necessary. Setting that last argument to "False" tells Excel to remove it from the schedule.
And there you have it. I now have a timer that is set to go off every five minutes, running my code at regular intervals.
If you work with Excel extensively, you'll probably come up with a good use of this procedure. As always, if you run into any issues with getting it implemented, give me a shout. I can supplement your efforts, taking your project over the finish line!
My next Excel topic will be issuing an alarm in Excel with an audible beep. Subscribe below to be notified when that post goes live.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2023. All rights reserved.