Link to home
Start Free TrialLog in
Avatar of doowell
doowellFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Listing scheduled events with VBA's OnTime method

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?
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of doowell

ASKER

Out of curiosity, does anyone know about the actual mechanism behind Application.OnTime?
- what's it called?
- does it reside within application memory rather than Windows?
- etc.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of doowell

ASKER

Seems this was as simple (and unexciting) as I thought.  Thanks to all for help.