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]

Can you post the code that you have got, because the select case worked perfectly Ok on my computer. Comparing times can be a bit of a problem because it can depend on what format the times stored on the spreadsheet are. That is why I put the code in to remove the date if it was present. I was certainly getting some calls to the dummy subroutines that I put in.

 

[JeffGrant]

Hi OffTheLip,

I have reduced the workbook to only the code and data that we are working on. Please use the link below to get a copy of the workbook. You guidance is very much appreciated. With your help, I am running the module with only three application.ontime commands at a time Which is much better than what I was doing before. But if we can get your idea to only run one instance of application.ontime that would be fantastic as I have other applications usingthe same syntax.

New System 80E-99-OffTheLip.xlsm

 

[offthelip]

I have had a look at your workbook and I think I have found the problem. Basically the problem is OPTION EXPLICIT!!!!! You defined Sec5 as long, unfortunately a long doesn't have the the precision to define it as anything but zero it needs to be defined as a double to work. However if you define as a variant, it ends up as 00:00:05, which excel will automativally type convert when you do a comparison

 

[JeffGrant]

Hi Offthelip,

I can see your reasoning why you don't like Option Explicit. After having reviewing the types of data statements VBA Supports, I have to say that I am more at the Novice end of being an Advanced Novice.I should have realised that. I will change that today and see how the test works. If the issue is as simple as that, then I owe you an appology for being as slow as a wet whistle

 

[offthelip]

Best of luck. Option explicit only really helps to find typos, which I find very quickly by putting a breakpoint in my code near the end of it, then the first time it runs through I look at the locals window and check down the list of variables, if there are two which look the same when there should only be one; there is the typo. I find this takes far less time that trying to type everything correctly using dim statements. I realised my comment above is not quite right anyway, long type is an integer only type so it can't possible show any time which is a fraction of a day.
It would be interesting to add up how much time it takes to write in all the dim statements and also sort out all the "type" problems that occur because of incorrectly defined tpyes and compare that to how much time you spend sorting out errors due to typos. ( with or with out option explicit)
Note I have been coding a very long time and I learnt to write code ( not VBa) when there was absolutely No help in finding typos or undefined variables, so I have got used to checking names carefully