OnTime Method problem near midnight

[Geoff Lambert]

I have an Excel macro to download a web-based Excel sheet . I run this macro once per hour, over a 24 hour period, using the OnTime Method, thus

Start macro
Inputbox to Call for original start time,
Inputbox to Call for # of times to run (N)

The Macro runs N times.... at the end of each run of the macro, the OnTime method is called again with the new start time = original start time + N * 1 hour.

This works (and I have been using it for weeks), except under one condition... when the new start time is exactly midnight or "close to" midnight (plus or minus approx a minute, I think). In this case, the macro runs TWICE, the second instance about 15 seconds after the first. For reasons associated with the downloaded data being identical, this then causes the macro to bomb out.

I know that the macro has selected its new Start time correctly one hour hence (i.e. approx 1 AM), because I drop that value into a cell on my own spreadsheet for double-check purposes. Thus it seems to me that another OnTime Instance out of my control, but with my macro as its object, has been run.

I gather that the OnTime Method uses the VBA timer() function and that the VBA timer is reset at midnight each day.

This would be the explanation except for the fact that the macro will repeat happily all through the day and night if the original start time is (say) xx:30:00. The problem only occurs when the original start time is near the "top of the hour" at (say) xx:59:40 and then only at the call at time 23:59:40 and not at any other "top of the hour"

How can I fix this?

 

[AlphaFrog]

When you schedule a new OnTime event, are you cancelling the previous one? Say you schedule an OnTime at "23:00:00". It will run each day until you cancel it.

I'm guessing you are not cancelling the previous OnTime events. After 24 hours, you may run it a situation where you have new OnTime events overlapping the previous day's OnTime scheduled events.

 

[Geoff Lambert]

No, I am not cancelling any previous OnTime event. That could be the problem. Do you need to know the exact time of an OnTime event to cancel it?

I received mixed messages about whether two or more OnTime Events could co-exist simultaneously. I was also led to believe that terminating Excel would terminate any OnTime event that it had started. On a related matter, can two instances of Excel run their own OnTime events independently of one another?

 

[AlphaFrog]

Do you need to know the exact time of an OnTime event to cancel it?

Yes. The previous Time can be stored in a Public or Static variable.

I received mixed messages about whether two or more OnTime Events could co-exist simultaneously.

You can have multiple scheduled OnTime events. I don't know what the limit is, if any, on how many events can be scheduled.

I was also led to believe that terminating Excel would terminate any OnTime event that it had started.

Closing Excel should terminate the OnTime events. Closing a workbook but leaving the Excel app running does not.

On a related matter, can two instances of Excel run their own OnTime events independently of one another?

My guess would be yes, but I don't really know and have not tried it.

 

[Geoff Lambert]

Last night I rebooted the computer, restarted Excel and made a single start of the download macro. I am pretty confident there could not possibly have been any OnTime instances lurking in the memory anywhere.

The macro ran perfectly from 9 PM to 11 PM, on the exact hour xx:00:00 without a problem. The macro takes about 15 sec to execute.

At midnight it ran at 00:00:00 and then again at 00:00:16 and consequently stopped.

Back to square 1. Tonight I will test it with the OnTime Method set to 30 minutes past the hour and see what happens.

 

[AlphaFrog]

Can you show all the code used to set the OnTime events?

For testing purposes, you can set your system clock to 11:59 PM and test what happens.

 

[Geoff Lambert]

This is the code that matters, I think:

<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:RelyOnVML/> <o:AllowPNG/> <o:TargetScreenSize>1024x768</o:TargetScreenSize> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Dim icount As Integer, icalls As Integer, TimeIncr As Date, StartTime As Date, NextTime As Date

Sub A1_Run_Later()

icount = 0
Start_Time = InputBox("Enter the time to run the first download as hh:mm:ss")
icalls = InputBox("Enter total # of calls to run")
StartTime = TimeValue(Start_Time)
NextTime = TimeValue(Start_Time)
TimeIncr = TimeValue("01:00:00")
MsgBox StartTime
Application.OnTime StartTime, "Hourly_Train_Catcher"


End Sub

Sub A2_Run_Once_Only()

icount = 0
icalls = 1
Application.OnTime Now, "Hourly_Train_Catcher"

End Sub

Sub Hourly_Train_Catcher()

Application.DisplayAlerts = False
icount = icount + 1

Then the main body of the macro…
……………….
Download macro ends here

'If needed again, restart the timer
If icount < icalls Then
NextTime = NextTime + TimeIncr
Application.OnTime NextTime, "Hourly_Train_Catcher"
End If

Some more code that renames the sheet and Activates the next

End Sub


Works fine except when NextTime is at or close to midnight. At midnight, the macro sets NextTime correctly as 01:00:00, but immediately runs it again instead of waiting until that time.

 

[AlphaFrog]

This is what I think is happening. You can set the start of an OnTime event with time-only or with a Date and Time.

If you set it with Time-only, it will trigger when that time occurs on the current day. If you set it with Time-only and that Time has already passed for the current day, it will trigger immediately.

If you set it with a Date-Time, it will trigger when that Date-Time occurs. If you set it with a Date-Time and that Date-Time is in the past, it will trigger immediately.

What you are doing in your current code is adding time to the NextTime variable. When the NextTime value crosses over midnight (say from 11:45 PM to 12:45 AM), it becomes a Date-Time value. You are adding one hour to 11:45 PM and the result time in not just a time value 12:45 AM. The vartiable NextTime is really 24 hours and 45 minutes. Excel interprets that as Day-1 at 12:45 AM. Day-1 is the date 1/1/1900.

Because that date 1/1/1900 12:45 AM has already passed, the OnTime Event is triggered immediately. Any remaining calls in the loop will also be triggered immediately as well.

I think the simple fix is to give the initial StartTime and NextTime variables the current date as well as the time. Then when you add a time value to the NextTime variable and it crosses midnight, the date will be tomorrow (not day one) and that's what you want.

Sub A1_Run_Later()

    icount = 0
Retry:
    StartTime = Application.InputBox("Enter the time to run the first download as hh:mm:ss", "Start Time", Type:=1)
    If StartTime = 0 Then MsgBox "Process canceled.": Exit Sub    'user canceled
    icalls = Application.InputBox("Enter total # of calls to run", "Number of Calls", Type:=1)
    If icalls = 0 Then MsgBox "Process canceled.": Exit Sub    'user canceled
    
    StartTime = Date + StartTime
    ' If user entered a time previous to now then make StartTime tomorrow
    If StartTime < Now Then StartTime = StartTime + 1
    NextTime = StartTime
    TimeIncr = TimeValue("01:00:00")
    'MsgBox StartTime
    Application.OnTime StartTime, "Hourly_Train_Catcher"

End Sub

 

[Geoff Lambert]

Good thinking Maynard. I will try that.

It's just that I am usually running two or three of these things simultaneously on different computers. Only on the machines where the calls are made at the top of the hour is the fault occurring.

We shall see... thanks for the insightful analysis.

 

[Geoff Lambert]

Uh

I set up two instances of the new macro, running on two different machines with two different Windows and Excel versions on two different networks on either side of town.

They both fell over in (almost) the same way, but at 11PM, not midnight.

Machine 1 (XP Excel 2003) ran a second instance immediately after the 11PM download and then hung. When I accessed it at 0615, it restarted the macro and did a download correctly. The NextTime was set to 00:00:00 of the starting day, not the next day.

Machine 2 (Win7 64-bit, Excel 2007) ran a second AND a third instance of the macro and then fell over completely. The two NextTime values associated with these extra instances were 00:00:00 of the starting day and 01:00:00 of the new day.

I take it that the Day function does not tick over to a new day until the first clock tick (1 second?) after midnight? [BTW I know I can enter midnight times into an Excel cell by entering "24:00:00", and later times by (say) "27:13:54" for 03:13:54]

Old versions of this macro used the following function to calculate NextTime
NextTime = Now + TimeIncr
but this introduced timing jitter caused by variable time lengths that the macro took to run. This macro always runs all night without a hitch, but it is always set to the "bottom of the hour" and so never runs at midnight.

Testing of the possible variations shall continue.