The other day I had a client ask me to create a reminder in an Excel document that would pop up several times a day. It had to run at specific times, and the interval wasn’t always the same. For example, it might need to be displayed at 8:00 am, 9:00 am, 10:30 am, and 2:45 pm. The times were listed on a worksheet and were subject to change. They only needed to be displayed when one particular document was opened. At first I told the client that this would be an easy job, but it turned out to be more challenging than I expected.
I’ve used timers before in Access and VB, but never in Excel. I thought it would be a simple matter of creating a form and using its Timer event, similar to the way Access works. But I was once again reminded that the similarities between Excel forms and Access forms are only superficial. Excel forms don’t have a Timer event.
My next thought was that there is probably a timer event procedure that I could take advantage of on a worksheet object or on the workbook object. But I struck out again. No such event exists on either.
Finally I thought I’d just add a timer ActiveX control to the Excel worksheet. I looked over the list of ActiveX controls in Excel, and didn’t find anything that sounded like a timer. I’m sure I could find something on the Internet if I looked hard enough. But since I was looking for a quick and easy solution, I had hoped there’d be a built-in control for that. Strike three.
It seemed unlikely that there was no timer feature in Excel, so I fished around on the Internet for suggestions, and learned about the “OnTime” method of Excel’s “Application” object. Once again, I thought I’d found the easy solution to my problem. Once again, I was wrong.
With the Application.OnTime method, you can schedule a procedure to run at a specific time. It’s a simple method with four arguments, but it was surprisingly troublesome. Let me start by explaining the basics of how to use this method.
The syntax is: Application.OnTime(EarliestTime, Procedure, LatestTime, Schedule).
The four arguments are:
EarliestTime: the time when you want Excel to try to fire off the procedure
Procedure: the sub procedure that will execute at the specified time
LatestTime: If Excel can’t fire the procedure at the “EarliestTime” because Excel is busy with other tasks, it will keep trying up until this time.
Schedule: True (or blank) to schedule the procedure; False to unschedule it.
Seems pretty straightforward, right?
The first problem I ran into is that the “EarliestTime” is defined as a variant, which left me wondering what format to use. Should I used a date/time value, or just a string with a time in it? Should I specify AM/PM, or use military time? The examples I found on this method weren’t very enlightening. They mostly used a function called “TimeValue”, which I wasn’t familiar with. After a little research, I learned that this function returns a serial value for a time, so that gave me a hint. Evidently the OnTime method was looking for a serialized time.
At that point I had enough information to put together a test, and sure enough, the timer fired as expected. Here’s what my test code looked like:
Sub StartTime()
Application.OnTime Now + TimeValue("00:00:15"), "MyTimer"
End Sub
Sub MyTimer()
MsgBox "It's Time!"
End Sub
I ran the “StartTime” procedure and sat back to wait. 15 seconds later, voila! My message box popped up. So far so good.
Next I wondered how I could set the timer for a specific time, instead of setting it for 15 seconds from now. That worked too, using this code:
Sub StartTime()
Application.OnTime TimeValue("15:16:00"), "MyTimer"
End Sub
Next I wondered if I actually needed the “TimeValue” function. This was really just academic, since I would have built the TimeValue’s argument based on info on the worksheet, but I was curious. Turns out my hunch was correct. The TimeValue function wasn’t really necessary. Here’s how this one worked:
Sub StartTime()
Application.OnTime "15:18", "MyTimer"
End Sub
As expected, the timer fired off at 3:18 pm. I also wondered what would happen if the argument included both a date and a time. After pulling the data off of the worksheet, I had it stored in a variable using a Date datatype. This came in handy for some other code I was using, so I wondered if I could just reuse the variable I’d already created. I thought it would just ignore the date portion, but I was wrong. Here’s what I tried to test my theory:
Sub StartTime()
Dim StartTime As Date
StartTime = "1/1/1980 3:25 PM"
Application.OnTime StartTime, "MyTimer"
End Sub
To my surprise, it fired off immediately, even though it was only 3:22 when I ran this. I thought it was going to ignore the date and fire at 3:25, or that it would never run since the date had already passed. Nope. It fired immediately. To continue to test my theory, I change the code to this:
Sub StartTime()
Dim StartTime As Date
StartTime = "2/16/2017 3:24 PM"
Application.OnTime StartTime, "MyTimer"
End Sub
Since this code used today’s date, the timer fired up as expected at 3:24 (I ran this at 3:23). Just to be 100% sure, I tried it again setting the date to tomorrow. And, as expected, it didn’t fire. I won’t wait around to find out if it fires off tomorrow. We’ll just assume it does.
So the lesson from all of this is: if you specify a date and time, the timer won’t go off until that date/time. But if you only specify a time, it’ll assume you mean today.
The third argument is the LatestTime. This sets a boundary on when the timer will fire if Excel is busy when it tries to run it at the designated time. For example, let’s say you’ve got a timer set to trigger at 3:38 pm. At 3:37 pm you start up another procedure which takes 5 minutes to run, meaning it’ll be running until 3:42 pm. So at 3:38 when the timer should fire, Excel is too busy with your other procedure. At 3:42 when the second procedure finishes, Excel will then try to execute the timer procedure. That is, unless you’ve specified a “LatestTime” for it, and that LatestTime has already come and gone. Consider this example:
Application.OnTime "3:41 PM", "MyTimer", "3:45 pm"
Excel will try to fire off “MyTimer” at 3:41 pm. But if it’s too busy to run it at 3:41, it’ll try again if it frees up before 3:45. After 3:45 it’ll give up on on this timer.
The final argument is called “Schedule” and you set it to True to schedule your procedure. Since “True” is the default value, leaving it blank has the same effect. After you’ve scheduled it, if you decide to cancel the timer, you can do so by setting this argument to false. Note that you must specify the first and second arguments exactly as you did when you scheduled them. So, for example, this works:
Sub StartAndStop()
' Start the timer:
Application.OnTime "3:50 pm", "MyTimer", "3:59 pm"
' Stop the timer:
Application.OnTime "3:50 pm", "MyTimer", "3:59 pm", False
End Sub
But this doesn’t work:
Sub StartAndStop()
' Start the timer:
Application.OnTime "3:50 pm", "MyTimer", "3:59 pm"
' Stop the timer:
Application.OnTime "3:51 pm", "MyTimer", "3:59 pm", False
End Sub
Interestingly, it doesn’t care about the third argument, meaning that this works:
Sub StartAndStop()
' Start the timer:
Application.OnTime "3:50 pm", "MyTimer", "3:59 pm"
' Stop the timer:
Application.OnTime "3:50 pm", "MyTimer", "1:23 pm", False
End Sub
It apparently only uses the EarliestTime and Procedure arguments to locate the timer and cancel it.
FYI, if you try to stop a timer that doesn’t exist, you get the dreaded error 1004:
I learned pretty quickly to use an “On Error Resume Next” statement when I am trying to shut down my timers. Unfortunately, there doesn’t seem to be any way to interrogate Excel to find out what timers are currently set. I thought I’d just set up a loop to cancel all timers if necessary, but no luck with that. So instead I keep an array of which timers are set, and then loop through the array to shut them all down.
The final bit of confusion came from the fact that the timers continued to fire after the Excel workbook that created them is closed. It turns out that because we are using the “Application” object, those timers stay in memory as long as Excel is open. If you shut down Excel, the timers will go away. But as long as you leave Excel running, it’ll keep firing your timers. If the document that contains the designated procedure is closed, Excel will open it again. Interesting, right?
This was a fun project, and I’m happy to report that this application is up and running without any issues. I’d love to hear about your experiences with Excel timers, either via Application.OnTime or some other method, so please leave comments if you’ve done this.
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of business 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, 2017. All rights reserved.
References:
"Application.OnTime Method (Excel)." Application.OnTime Method (Excel). Microsoft, n.d. Web. 16
Feb. 2017. <https://msdn.microsoft.com/en-us/library/office/ff196165.aspx>.
Photo Credit
Timer: CC Image "Timer at 0" courtesy of nicholas will on Flickr (CC BY-SA 2.0)