Application.OnTime running at random intervals

[genepaton]

Hi All,

I've set up two macro's in my workbook to run at given times (when first opened, and then 15 min and 30 min respectively).
They run fine on open, but then they both seem to decide to run when ever they feel like it after the first occurrence, most often at sooner time intervals than specified eg. every 3 min or something.

This is the workbook open code:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next
Application.OnTime dTime1, "ImportData", , False
Application.OnTime dTime2, "Linear", , False

End Sub

Private Sub Workbook_Open()

dTime1 = Now + TimeValue("00:15:00")
Application.OnTime dTime1, "ImportData"

dTime2 = Now + TimeValue("00:30:00")
Application.OnTime dTime2, "Linear"

End Sub

And the start of both the other macro's;

Public dTime2 As Date

Sub Linear()
dTime2 = Now + TimeValue("00:30:00")
Application.OnTime dTime2, "Linear"

Application.ScreenUpdating = False

'MY MACRO HERE

Next example:

Public dTime1 As Date

Sub ImportData()
dTime1 = Now + TimeValue("00:15:00")
Application.OnTime dTime1, "ImportData"

' ImportData Macro
' Macro recorded by Me

On Error GoTo ErrorHandler

'MY MACRO HERE

The only thing i can think of (but im sure i'll have other errors pointed out) is that because i have web queries and Microsoft WebBrowser embedded content, it may be tripping the OpenWorkbook??? But that should make it loop continuously wouldn't it?

Also i have Application.Wait Now + TimeValue("00:00:05") entries in my macro, to make it pause before continuing, don't know if this effects it.

 

[Jaafar Tribak]

Not sure but you may want to include the LatestTime arg of the OnTime Method .

    dTime1 = Now + TimeValue("00:15:00")
    dLatesTime = dTime1 + TimeValue("00:00:01")
    
    Application.OnTime dTime1, "ImportData", dLatesTime

If excel ,because of the web queries or for wharever reason, is not ready within 1 Sec, the ImportData Macro won't run but that is probably worse than running at random intervals

 

[Ruddles]

A couple of observations:-

1) I normally reschedule the event at the end of the associated macro rather than at the beginning. That way the countdown starts only when all the processing has completed, although this is very much a matter of personal preference. Also if the macro fails or stops for any reason, there isn't a 'stray' event scheduled which will unexpectedly burst into life later.

2) You will always have two events scheduled to tale place at a future time and if Excel is active, they will run even if the workbook is not open. You should cancel these events in the workbook's BeforeClose event. (You may already do this but as you didn't post that code I thought it best to mention it.)

 

[genepaton]

Not sure but you may want to include the LatestTime arg of the OnTime Method .

    dTime1 = Now + TimeValue("00:15:00")
    dLatesTime = dTime1 + TimeValue("00:00:01")
    
    Application.OnTime dTime1, "ImportData", dLatesTime

If excel ,because of the web queries or for wharever reason, is not ready within 1 Sec, the ImportData Macro won't run but that is probably worse than running at random intervals

Hi Jaafar,

Tried using the dLatestTime argument, its not running randomly as often, but still seems to be doing it.
Thanks for the suggestion all the same.

 

[genepaton]

A couple of observations:-

1) I normally reschedule the event at the end of the associated macro rather than at the beginning. That way the countdown starts only when all the processing has completed, although this is very much a matter of personal preference. Also if the macro fails or stops for any reason, there isn't a 'stray' event scheduled which will unexpectedly burst into life later.

2) You will always have two events scheduled to tale place at a future time and if Excel is active, they will run even if the workbook is not open. You should cancel these events in the workbook's BeforeClose event. (You may already do this but as you didn't post that code I thought it best to mention it.)

Hi Ruddles,

By scheduling the event at the end of the macro, what would initiate the macro in the first place? The workbook im doing needs to run fully automated. Or what would the scripting look like to have it run when opened, run through the macro and then start the timer?

In relation to the BeforeClose, i thought i had that sorted by the inclusion of the following in the ThisWorkbook sheet;

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next
Application.OnTime dTime1, "ImportData", , False
Application.OnTime dTime2, "Linear", , False

Cheers

G

 

[Ruddles]

By scheduling the event at the end of the macro, what would initiate the macro in the first place?

You're doing that already: the very first scheduling of ImportData and Linear is done in Workbook_Open. That's how it's usually done.

What I'm saying is, when ImportData and Linear run, I would place the .OnTime command - the one which reschedules them to run on subsequent occasion - at the end of those macros rather than at the beginning.

If you reschedule them to run as soon as the macro is called and it crashes for any reason, then whilst you're debugging your code or doing whatever else you'd be doing at that point, there's a scheduled run of the macro sitting there quietly, not detectable by you, and that will burst into life exactly when you originally told it to. When it does that, it will promptly reschedule itself to run again.

If you reschedule them to run as their final act, they will only do so if they ran cleanly and won't leave a stray invocation scheduled to run at a later time.

For example, let's say you have a procedure called TestSub set to run every 15 minutes and there's a problem which crashes the code. This happens:-

09:00 You open the workbook. The Workbook_Open macro schedules TestSub to run in 15 minutes time.

09:15 TestSub is called on schedule and reschedules itself to run in another 15 minutes (09:30). It then crashes. You start investigating and fix the problem in, let's say, 5 minutes.

09:20 You re-open the workbook and TestSub is scheduled to run in 15 minutes (09:35).

09:30 TestSub is called on schedule and reschedules itself to run in 15 minutes (09:45).

09:35 TestSub is called on schedule and reschedules itself to run in 15 minutes (09:50).

09:45 TestSub is called on schedule and reschedules itself to run in 15 minutes (10:00).

Get the idea? (I've colour-coded them to make it clearer what's happening.)

It's quite possible to create two or more 'threads' so when you think your macro should only be running at 09:20, 09:35 and 09:50, the first thread you created is still active and intent on running at 09:30, 09:45 and 10:00, etc, as you originally told it to. That could easily look as though the macro was running 'randomly' because it's running when you expected it to run and also at times in between. I know because it's happened to me!

I'm not saying that's definitely happening in your case but I wanted to make you aware that this sort of thing was possible.

If you think you might have events which were scheduled in an earlier run still 'hanging around' waiting to execute, you should not only close the workbook but you should close Excel completely, because these events belong to the application rather than the workbook and are only destroyed when the application closes. If you merely close the workbook, Excel will quite happily re-open it in order to run the event.

 

[Ruddles]

In relation to the BeforeClose, i thought i had that sorted by the inclusion of the following in the ThisWorkbook sheet

Sorry, yes, I missed that. It was manic here yesterday and I was trying to do three things at once, none of them very well!

 

[genepaton]

Wow, thanks for the explanation. I didn't know that it was possible for the events to 'queue' as such.

So essentially i should move the 3 lines of code to the end of the macro? Or just the Application.OnTime line?;


dTime1 = Now + TimeValue("00:15:00") dLatesTime = dTime1 + TimeValue("00:00:01") Application.OnTime dTime1, "ImportData", dLatestTime</pre>
Error handling before hand (would that make it still reschedule if it bombs out?)? Or just after prior to End Sub?
At the moment when it calls error handling it skips the multitude of other macros and sits there until the next event is called.

Sorry, yes, I missed that. It was manic here yesterday and I was trying to do three things at once, none of them very well!

 

[Ruddles]

So essentially i should move the 3 lines of code to the end of the macro? Or just the Application.OnTime line?

Just the .OnTime one will do, but I think the three lines belong together so move them all.

Error handling before hand (would that make it still reschedule if it bombs out?)?

You'd have to decide what to do if an error occurred. If you can reliably trap the error and carry on running, then you should decide whether to stop the processing with a message or carry on and schedule the next event.

If your program is going to crash in the event of an error, you definitely don't want to reschedule the event to run again.