OnTime queue entries

vesc

Board Regular
Joined
Mar 17, 2006
Messages
83
Hi, is there any way of getting a list of entries that have been posted to OnTime()? One of the things I'd like to do is selectively cancel some of the items, but this is nearly impossible unless you track the details of every call to OnTime and somehow know how to delete entries from your own list. Any ideas?

Thanks,

Vesc
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
L

Legacy 98055

Guest
"is there any way of getting a list of entries that have been posted to OnTime"

I don't understand and perhaps others do not as well. Are entries synonomous with "Calls"?
 

vesc

Board Regular
Joined
Mar 17, 2006
Messages
83
Calls = Entries

Yes, Calls = Entries. Essentially, any time you call OnTime with a set of parameters you can cancel the request by passing the exact same set of parameters and an additional False. Problem is: if you have a number of these you can't say things like "remove all OnTime requests which invoke/call macro X" because you have to match all the parameters exactly as you entered them when you called OnTime originally. Hope this clarifies,

Vesc
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

you're in good hands with Tom, so my reply will only be two little ideas
1. you could write all calls to a list on a sheet and use that for future reference (so when you need to cancel them)
2. talking about specific macro: you could define a global variable: when TRUE code exits

Say "OnTimeMacro" has been scheduled
when you run "StopRunningOnTimeMacro" then "OnTimeMacro" will not be "deleted" from the schedule, but exit without further operations
Code:
Global DoNotRunThisMacro As Boolean

Sub OnTimeMacro()
If DoNotRunThisMacro Then Exit Sub
'code
End Sub

Sub StopRunningOnTimeMacro()
DoNotRunThisMacro = True
End Sub
kind regards,
Erik
 

vesc

Board Regular
Joined
Mar 17, 2006
Messages
83

ADVERTISEMENT

Thanks Erik

Great ideas. I did use the global boolean when I only had one macro being passed to OnTime. Now that I want to expand on the idea... looks like I'd need a boolean for each macro.

That'll work...

but I guess the answer to the question "is there a way of getting the list of 'entries' OnTime is currently keeping track of?" is no. Didn't think so, but thought I'd check with you all, who are more experienced and knowledgable.

Thanks for the help Erik, I'll probably use both techniques.

Regards,

Vesc
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
but I guess the answer to the question "is there a way of getting the list of 'entries' OnTime is currently keeping track of?" is no. Didn't think so, but thought I'd check with you all, who are more experienced and knowledgable.
I didn't tell you that :unsure:

the answer is clearly YES
one technique would be
1. you could write all calls to a list on a sheet and use that for future reference (so when you need to cancel them)
I would prefer this to the Boolean-technique
remember when a file is closed while a procedure it still scheduled, your file will open again
nice example for this
Code:
Sub GoodbyeAndHello()
    Application.OnTime earliesttime:=Now(), procedure:="OpenMe"
    ThisWorkbook.Close savechanges:=False
End Sub

Sub OpenMe()
    MsgBox "Hello again"
End Sub
another technique: use variables for macroname + scheduletime

surely Tom is more experienced in programming and he can learn us something new ...

best regards,
Erik
 

vesc

Board Regular
Joined
Mar 17, 2006
Messages
83

ADVERTISEMENT

no inherent list in Excel

Hi Erik,

What I was after was an Excel list (Excel function returning an array or something like that) of the parameters. IOW, Excel should be able to tell me what it's keeping track of, I shouldn't have to keep a copy myself.

Now I know why the file keeps re-opening after closing!

Thanks again,

Vesc
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Re: no inherent list in Excel

What I was after was an Excel list (Excel function returning an array or something like that) of the parameters. IOW, Excel should be able to tell me what it's keeping track of, I shouldn't have to keep a copy myself.
OOH, I see what you mean now.
perhaps Tom knows how to get there ? Tom, do you ?
else I'll call some guys ...
 
L

Legacy 98055

Guest
Along the same lines as Eric's suggestions (storing the config info for you OnTime calls), you could create a custom object to wrap your OnTime procedures. I do not know what you intend to do beyond what you mentioned as far as the procedure name being called.
 
L

Legacy 98055

Guest
To pass arguments, see this great writeup at Tusharm's website. I am working on a custom wrapper for the OnTime method that will utilize the methods Tusharm mentions along with some others. It seems like it might be of good use to a lot of folks out there...

Using procedures with arguments in non-obvious instances: macros associated with shapes, forms, and commandbar elements, and called by the OnTime and OnKey methods

As far as Excel returning information about what OnTime processes are currently active and the parameters they currently contain, I know of nothing. Like Eric stated, you would have to maintain a custom list of your various parameters. I'll post the OnTime addin when (if) I ever get it done. :)

Eric. These are the properties that the class currently contains. Any suggestion for more that you think might be useful?

Private pEarliestTime As Date
Private pProcedureName As String
Private pLatestTime As Date
Private pSchedule As Boolean
Private pLoopIntervalSeconds As Long
Private pLoopUntil As Date

Private pNextScheduledRunTime As Date
Private pActivated As Boolean
Private pItemName As String
Private pGroupName As String
Private pTag As String
 

Forum statistics

Threads
1,136,369
Messages
5,675,362
Members
419,565
Latest member
Phil57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top