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

 

[JeffGrant]

I guess doing what you have describved, you have a maximum of 4 subs scheduled, which is a lot better than several hundred?

The approach I am taking at the moment is to schedule one at a time and if there is a double up of time to delay the second schedule by a Now + some TimeValue parameter

 

[offthelip]

I guess doing what you have describved, you have a maximum of 4 subs scheduled, which is a lot better than several hundred?

The approach I am taking at the moment is to schedule one at a time and if there is a double up of time to delay the second schedule by a Now + some TimeValue parameter

I only ever have one sub scheduled with application ontime which is the the "Master Scheduler" what changes is how subs the "MasterScheduler" calls on each trigger which can be any number , even a hundred provided they completed quickly there is no limit

 

[JeffGrant]

Hi offthelip,

Firstly, may I say that i really appreciate your help here. I have really been stretching my thinking on how to achieve what you are talking about. Unfortunatety, the approach I am using is not working. I, like you need, to run 4 separate subs based on the times in the table like this. This table updates everyday. The four subs that I need to run are called the same as the column headers (without spaces ofcoarse).

The approach I am using with a single list of times will often end up with the same time to run two or three times. And of coarse, in a single vector, I can only pick up the first instance and not the second or third because the time has already passed and so the other subs simply do not get get called.

I dont understand how you are using the Master Schedular to only call one instance of appliaction.ontime at a time. The best I can do is schedule 4 incidences of application.ontime, ie one for each column.

I appologise for being a bit slow off thr msrk, so if you can indulge me with some example code, that would be very appreciated.

This project is almost 3 years in the making and this is the last piece of the puzzle, so all guidance is very much appreciated.

Again, thankyou

Jeff

 

[offthelip]

Here is some example code, I have done some basic testing but it probably doesn't do exactly what you want and there are probably some bugs in it but it should show you what I am thinking

Sub RunAPIRaceTimer()
Dim TimeToRun As Date, LatestTimeToRun As Date
Dim strTimeToRun As String, strLatestTimeToRun As String
Dim lrow As Long
Sec5 = TimeValue("00:00:05") ' set a constant value of 5 seconds to allow for slight time difference between time now and the theoreticla time to trigger
    With Worksheets("Sheet10") 'Todays Field List
         irow = .Cells(Rows.Count, "I").End(xlUp).Row   ' Assume the last row of I is the same as the last row of columns J,K and L
'        irow = .Range("AI" & Rows.Count).End(xlUp).Row
         irowvalues = .Range("I1:L" & irow) ' load Columns I to L into ainto a variant array
 ' check if times have date included
         For i = 2 To irow
         For colno = 1 To 4
           irowvalues(i, colno) = irowvalues(i, colno) - Int(irowvalues(i, colno))
         Next colno
         Next i
        timenow = TimeValue(Now())
' find which subs to run
        For colno = 1 To 4
         For i = 2 To irow
           tdiff = timenow - irowvalues(i, colno) ' Find difference between time now and time to run a sub is each of the four columns
           If tdiff < Sec5 Then
             Select Case colno
              Case 1
                Call ApiDownload
              Case 2
                Call CopyRaceId
              Case 3
                Call Runanalysis
              Case 4
               Call ApiRunUpload
            End Select
            Exit For
            End If
         Next i
        Next colno
       
'        Call CycleThroughNextRaceMeetings
' find the time to ttrigger the next run
       TimeToRun = Now() + 1 ' set time to run plus one day just to start
        tt = TimeValue(timenow)
        For colno = 1 To 4
        For i = 2 To irow
           
            If irowvalues(i, colno) > TimeValue(timenow) Then
            If irowvalues(i, colno) < TimeToRun Then  ' find the minimum time that is in the future
            MsgBox ("i=" & i & " colno= " & colno)
           
            TimeToRun = irowvalues(i, colno)
            End If
            End If
        Next i
        Next colno
        If TimeToRun < Now() + 1 - 1 / (24 * 60) Then  ' check if there is anything left to run
             '-------------------------------------------------------------------------------
            '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
         End If
       
    End With
End Sub


Sub ApiDownload()
    MsgBox "ApiDownload " & Now()
End Sub
Sub CopyRaceId()
    MsgBox "CopyRaceId " & Now()
End Sub
Sub Runanalysis()
    MsgBox "Runanalysis " & Now()
End Sub
Sub ApiRunUpload()
    MsgBox "ApiRunUpload " & Now()
End Sub

 

[JeffGrant]

Thanks mate. I will have a look at this.

On Friday I got the code down to only runnng 3 macros instead of 4. I watched it for the entire day and it didn't miss a beat . It is still very clunky, but 3 macros is way better than the hundreds I had scheduled.

I get the jist of what you are doing here and it looks much neater than mine, so i look forward to giving it a go.

cheers

 

[JeffGrant]

would you please be so kind as to include the how you have dimensioned your variables.

I use Option Explicit on all modules, and there are several variables that I just cant make it work because I don't know enough about arrays and how excel treats time.

I keep getting type mis-match erros

thanks

 

[JeffGrant]

Hi Offthelip, please ignore last message - I worked it out. I like to keep at things until I get it.

 

[offthelip]

Hi Jeff, I never use option explicit I find it more trouble than it is worth, Excel defaults more or less everything to "Variant" type, so if that works I don't declare it, sometimes something does need to be declared e.g. objects, Range, etc. So when I see declaration at the top of my code I know it is there for a specific reason and not lost in a long list of "unnecessary" declarations.
By the way I spotted one error in my code, the calculation of Tdiff needs to be made absolute.

Tdiff < 0 then Tdiff= -Tdiff

put this just before the check that Tdiff is less than 5 seconds

 

[JeffGrant]

I tend to agree with you about Option Explicit, but considering myself to be an older advanced Novice, I am still very much guided by the expertese on forums like this. I did manage to make the code work with the correct Dim statments, and am now testing for the correct Select Case. It is not picking up teh select case at all.

Thanks for your help

 

[JeffGrant]

Hi Offthelip,

I have adopted some of your code in my model because it is very nicely written and is more efficient that me running 3 separate routines, generating 3 separate application.ontime commands, unfortunately though, I cannot make the Select Case work. I can't make the tdiff test work properly.

I have got the Dim statements working and have tried several TimeValues other than 5 seconds, all to no avail.

I'd appreciate any suggestions that you can offer.