excel vba question on Application.OnTime TimeValue

[travis]

Here they say:
http://msdn.microsoft.com/en-us/library/office/ff196165(v=office.15).aspx

This example runs my_Procedure at 5 P.M.

Application.OnTime TimeValue("17:00:00"), "my_Procedure"

This example cancels the OnTime setting from the previous example

Application.OnTime EarliestTime:=TimeValue("17:00:00"), _ 
 Procedure:="my_Procedure", Schedule:=False

My question is about this scope of the Application.OnTime method, which I use in my automated systems, where I am going from a daily schedule (starting them once a day) to a weekly schedule (starting them once a week).

When they say that

Application.OnTime TimeValue("17:00:00"), "my_Procedure"

will run a procedure at 5 P.M. does it mean the next instance of the clock being 17:00:00, whether today or tomorrow, or does it mean all future instances of 17:00:00?

The fact that they have a method to cancel the Application.OnTime method doesn't help, because initially I thought: you have to cancel it because it means all future instances of the clock at 5 P.M., but it could also mean you use it to cancel the procedure before it happens for the first (and only) time.

And if

Application.OnTime TimeValue("17:00:00"), "my_Procedure"

will only run the procedure once, at the next instance of 5 P.M., which method could I use to run a procedure not just once but at 5 P.M. of every day of the week?

 

[travis]

OK, solved.

Here they confirm that the "Application.OnTime TimeValue..." method only runs once. So it's just the next 5 PM and not all future instances of 5 PM. And they also explain how to execute a procedure at every future instance of 5 PM.

http://www.mrexcel.com/forum/excel-...on-ontime-stops-executing-after-two-days.html

Redart, Application.OnTime only schedules a single event as you have discovered. The solution is as follows: when the scheduled event calls CompleteSQLReportRefresh, the last thing you do in there is schedule another event for 24 hours time.