Looking for a VBA loop suggestion.

[JeffGrant]

Hi All,

I have a dynamic table that holds time values, from the first value to the last is no more than 20 minutes. There could be one row in the table, there could be 6. This table gets updated many times per day.
The time values are read in via a calling sub with a For loop (have also tried a Do While) and the time values are then passed to a processing sub that containes an Application.Ontime statement.
All of the Apllication.Ontime statements are set up with "Latest Time To Run" parameter, because there are other operations that may be going on that may delay the Applications.Ontime starting.
The processing sub then calls the Calling sub to create a repetative loop.
All good here, works like a treat.

However, where I am getting stuck, is this....

On the first reading of the time loop values, the Application.Ontime statements are set up. No Drama.
When the first Application.Ontime runs from the first time value, everything is ok.
On the second running of the Application.Ontime, the first time value has passed and so Application.Ontime ignores the first time value in the second loop.
But future time values are from subsequent rows in the table are read in and Application.Ontime sets up another occurance to run.

Because of the "Latest Time to Run" parameter, I end up with multiple scheduled events.

At the moment I am running an If statment such that if the read in time value from the table is greater than current time + 5 minutes, then exit the For Loop. This has substantuially reduced the number of scheduled events that are stacked one on top of the other.

Ultimately, I would like the Application.Ontime to only run once for the read in time value, but I am at a bit of a loss how to do it in this scenario.

Maybe there is a way, that on subsequent For Loop iterations, I can cancel previously scheduled events?????

All suggestions and ideas are very much appreciated.

 

[AlphaFrog]

You can cancel a previously scheduled OnTime event by setting the Schedule argument = False. Something like this where t is the scheduled time value...

    On Error Resume Next 'Ignore error
    Application.OnTime t, "MyMacro", Schedule:=False
    On Error GoTo 0 'Resume error checking

If there was no OnTime event for the time value t, the code would normally throw an error but for the On Error Resume Next line. So before scheduling any OnTime event, cancel the OnTime event for that time value t whether it was previously scheduled or not and ignore the error.

 

[JeffGrant]

Thanks AlphaFrog. I have built this suggestion into my code. Fingers crossed. Will let you know.