Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1950
  • Last Modified:

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?
0
doowell
Asked:
doowell
  • 3
  • 2
4 Solutions
 
zorvek (Kevin Jones)ConsultantCommented:
>Is there any way of querying Windows (or whatever mechanism VBA uses) to interrogate it for what tasks have been scheduled?

No.

You can use the registry or a local file. You can also use cells in the workbook itself.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
If I were faced with loss of state I would use a vary hidden worksheet to store the tasks and times. It's completely resilient to VBA errors and loss of state.

Initialize it when the workbook opens just as you would an array. Add to and delete from it just as you would an array. Since you are using it relatively rarely any performance hit will not be noticeable.

Kevin
0
 
Dave BrettVice President - Business EvaluationCommented:
> am in a situation where we need to be able to schedule tasks ad hoc.

 Without going off on much of a tangent what are you doing with this code?

You can use vbscript (a vbs file where the coding is very similar to VBA) with the Windows task scheduler, the vbs can open and automate Excel Workbooks.
 
 Cheers
 Dave
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
doowellAuthor Commented:
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.
0
 
zorvek (Kevin Jones)ConsultantCommented:
It resides in the internal application tables and does not use the Windows timers. It is not exposed except through the Application.OnTime method.

Everyone refers to the feature as OnTime or Application.OnTime.

That's about it.

Kevin
0
 
doowellAuthor Commented:
Seems this was as simple (and unexciting) as I thought.  Thanks to all for help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now