The OnTime method can be used to schedule a procedure to run at a later time. The syntax of this method, from help, is
Application.OnTime(EarliestTime, Procedure, LatestTime, Schedule)
Generally, the EarliestTime argument is set to a certain number of minutes or seconds from now. This line causes the procedure named MyProc to run in 5 minutes.
Application.OnTime Now + TimeSerial(0, 5, 0), "MyProc"
Notice how the argument isn’t called ExactTime. MyProc will run no earlier than EarliestTime, but that’s no assurance that it will start then, such as when another macro is running. That’s actually beneficial, because you can use OnTime to run directly after the current procedure, like this:
Application.OnTime Now, "MyProc"
Now is the earliest it will run, so as soon as the current procedure is finished, it will run. Why not just call it directly? Well, sometimes you have to trick Excel into doing what you want, like when you need to get around a bug. Other times, like hooking commandbarbutton events, you need the user to do whatever it is they’re going to do before your procedure runs.
The Schedule argument determines whether you’re setting up a procedure to run or cancelling a previously scheduled procedure. The default is True (setting up a procedure to run) so it’s usually omitted in that instance. To cancel a scheduled procedure, you have to know the exact time that it’s scheduled (its EarliestTime argument). For that reason, if you will potentially need to cancel an OnTime procedure, it’s best to store the EarliestTime in a variable that you can access to cancel it.
dEtime = Now + TimeSerial(1, 1, 5)
Application.OnTime dEtime, “MyProc”
‘If something happens and you need to cancel
Application.OnTime dEtime, “MyProc”, , False