OnTime queue entries

[vesc]

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

 

[Legacy 98055]

"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]

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]

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

Global DoNotRunThisMacro As Boolean

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

Sub StopRunningOnTimeMacro()
DoNotRunThisMacro = True
End Sub

kind regards,
Erik

 

[vesc]

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]

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

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

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]

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]

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 ...

 

[Legacy 98055]

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.

 

[Legacy 98055]

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