?
Solved

Listing scheduled events with VBA's OnTime method

Posted on 2010-01-13
6
Medium Priority
?
1,788 Views
Last Modified: 2013-11-25
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
Comment
Question by:doowell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 1125 total points
ID: 26301623
>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
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 1125 total points
ID: 26301638
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
 
LVL 50

Assisted Solution

by:Dave Brett
Dave Brett earned 375 total points
ID: 26301641
> 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:doowell
ID: 26301700
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
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 1125 total points
ID: 26312247
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
 

Author Closing Comment

by:doowell
ID: 31676554
Seems this was as simple (and unexciting) as I thought.  Thanks to all for help.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Six Sigma Control Plans

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question