Application.Ontime misses scheduled time

[JeffGrant]

Hi All,
I have this sub:

Sub RunAPIRaceTimer()
Dim TimeToRun As Date, LatestTimeToRun As Date
Dim strTimeToRun As String, strLatestTimeToRun As String
Dim lrow As Long

    With Sheet10 'Todays Field List
        irow = .Range("AI" & Rows.Count).End(xlUp).Row
        For i = 2 To irow
            TimeToRun = .Range("AI" & i).Value
            '-------------------------------------------------------------------------------
            'add 5 minutes to earliest time to run and see if this picks up the missing tracks
            '-------------------------------------------------------------------------------
            LatestTimeToRun = TimeToRun + TimeValue("00:05:00")
            strTimeToRun = Format(TimeToRun, "hh:mm")
            strLatestTimeToRun = Format(LatestTimeToRun, "hh:mm")
            Application.OnTime strTimeToRun, "CycleThroughNextRaceMeetings", strLatestTimeToRun, True
        Next i
    End With
End Sub

and I have this table:

The sub cycles through For Loop at column AI (Column Copy RaceID Time) and sets up all of the times for the day that the CycleThroughNextRaceMeetings sub needs to run.

On the whole the loops work fine.

However, everyday there is 1 or 2 race application.ontime calls that are simply missed. Today for example, the application.ontime for Ballarat Synthetic, Race 1, which was supposed to run at 12:43, simply did not run.

I understand that if other code is running or the workbook is in edit mode, that the application.ontime will not run. However, I have had many many attempts and changing delay time factors. or slowing things down or making sure that the workbook is not in edit mode, and is does not seem to make any difference.

I have spent hours on this and all I see on the WWW is the same information over and over.

I appreciate any guidance as to why members of this forum can suggest while the application.ontime scheduled times are missed.

thanks in advance

 

[offthelip]

I have had a exactly the same problem when setting multiple ontime calls. My solution was to use only a single ontime call which called my "Master Scheduler" this program looked down the list to find out which subroutine to call at the current time and then looked down the list to find out what was the next time that a subroutine needed to run and set the time to call itself to that time. So I only had one ontime call in the system at any one time.

 

[JeffGrant]

Hi Offthelip, thank you for your quick response.

I am interested in this comment:
"looked down the list to find out which subroutine to call at the current time and then looked down the list to find out what was the next time that a subroutine needed to run"

because if you look at my table above, you will see that I have four columns of times that run through loops.

There is one set of API Download Time, then Copy RaceID Time, Run Analysis Time and then finally API Run upload time, so there is a lot of scheduled Application.ontime events.

I understand that if we move from running For Next Loops to a single increment of an i = i+1 loop to run a single event at a time that makes sense. But how did you achieve determining what sub to call at what the appropriate time?

can you give me an example of your code to achieve this?

 

[offthelip]

my code was a bit more complicated because I was calling different routines at different intervals ( some every 5 minutes some every hour), but you call the same routine every time, so I have modified your code to do something similar: try this:

Sub RunAPIRaceTimer()
Dim TimeToRun As Date, LatestTimeToRun As Date
Dim strTimeToRun As String, strLatestTimeToRun As String
Dim lrow As Long

    With Worksheets("Sheet10") 'Todays Field List
         irow = .Cells(Rows.Count, "I").End(xlUp).Row
'        irow = .Range("AI" & Rows.Count).End(xlUp).Row
         irowValues = .Range("I1:I" & irow) ' load it into a variant array
        timenow = Now()
        Call CycleThroughNextRaceMeetings
        For i = 2 To irow
            TimeToRun = irowValues(i, 1)
            If TimeToRun > timenow Then
            '-------------------------------------------------------------------------------
            'add 5 minutes to earliest time to run and see if this picks up the missing tracks
            '-------------------------------------------------------------------------------
            LatestTimeToRun = TimeToRun + TimeValue("00:05:00")
            strTimeToRun = Format(TimeToRun, "hh:mm")
            strLatestTimeToRun = Format(LatestTimeToRun, "hh:mm")
            Application.OnTime strTimeToRun, "runapiracetimer", strLatestTimeToRun, True
            Exit For
            End If
        Next i
    End With
End Sub

Sub CycleThroughNextRaceMeetings()
 MsgBox ("Time " & Now())
End Sub

Note I have change column AI to I for testing, and created the simple cycle sub

 

[JeffGrant]

Hi OfftheHip,

because there is a For Next loop, how does this work that only one ontime is scheduled at a time? I am of the understanding that the For Next loop will loop until irow is reached in one go load the ontime events into memory??

 

[offthelip]

The times that you put in list of start times is in chronological order, the for loop picks up that list of times and then finds the first time that is in the future ( i.e "If TimeToRun > timenow Then") it then goes and sets an application ontime event and then excecutes the "EXIT FOR". This means it doesn't set any more application ontime events. When application ontime event triggers it runs CycleThroughNextRaceMeetings() and picks up the list of times agains and search for the first one which is in the future again, which now is the next one in the list
Try running it with the simple code that I put in for CycleThroughNextRaceMeetings() in a copy of your workbook

 

[JeffGrant]

Hi Offthelip,

my sincere appologies for being as blind as a bat. I did not see the Exit For in the construction of the For Next Loop. Time for some new glasses or another cup of coffee. Thanks for the explanation.

 

[JeffGrant]

Hi OffTheLip,

I look forward to trying your suggestion today. Because the application.ontime function I use, actually uses the for right hand columns in the table above to generate incstances, I have had to transpose that table into a two column list. With a two column list, I feel confident that your suggestion will solve a problem I have had for months.

I'll let you know how I go.

 

[JeffGrant]

Hi offthelip,
an interesting situation has no popped up. The code to transpose the data from a 4 column table of times into a single column list works fine.
However, after sorting into chronoligcal order, what has appeared is the same time value can occur two or three times.
The issue with this is that these same time triggers the running of different macros.
So on the first instance of the time value, the first macro will run, but on subsequent instances of the same time value, thise macros will not run because the time value is now > Now .

Any suggestions. Might need a bit of grey matter on this one

 

[offthelip]

This is where your "master scheduler" has to sort this out, I had the same problem I had more than one routine to run at any given trigger. The way to solve it is when the Master scheduler gets triggered, it looks down the list of times and then calls each of the subroutines that needs to be run . i.e the master scheduler has two sections first section is "what needs to be run this trigger" and the second section is "what time do I need to trigger again" . You might find it easier not to sort the 4 columns into one column but to search all the columns separately find the earliest in each column, then compare them and find the earliest, and set that trigger, then when the time come you search all four columns again and find which "columns" need to be run at this time, if they are all the same then you run all for subs