Listing scheduled events with VBA's OnTime method
Posted on 2010-01-13
Here's one that I think could be really quite tough - however there are many bright minds here so it may get a decent answer...
In Excel VBA, one can schedule events using the Application.OnTime method. This method can also be used to de-schedule a task having a known schedule time. Recording these times in an array can then be used to deschedule any batch of tasks.
However, what does one do when one does NOT know the time of the scheduled tasks? I am in a situation where we need to be able to schedule tasks ad hoc. And in the case of an error condition, VBA's stack memory is wiped so the contents of an array of times evaporates.
So what's to do? Is there any way of querying Windows (or whatever mechanism VBA uses) to interrogate it for what tasks have been scheduled?
e.g. if I schedule a VBA macro called "myTimedMacro" multiple times, can I comb through Windows' list of scheduled tasks and search for occurrences of "myTimedMacro" - direcftly and in a a way that doesn't rely on me keeping a local record of scheduled times? If so, how?