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]

Uh
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.

You could recalc NextTime = Now + TimeIncr at the top of the "Hourly_Train_Catcher" macro. That should eliminate or minimize the jitters.

I don't have any new insight on the other "stuff".

 

[Geoff Lambert]

Problem solved

It was to do with the fact that "day1 23:00:00" + "01:00:00" produces "day1 00:00:00", not "Day2 00:00:00", so the macro fires off a second time ajust s you surmised. Changing the start time to be 1 second past the hour fixes this. This is an advantage in another way too because the database I download itself takes a fraction of a second to tick over at midnight (at precisely midnight it is empty). The macro with NextTime = Now + TimeIncr placed at the head of the macro also works, but still with a bit of creep.

Now to see what happens when DST starts here on Saturday night

Thanks for guiding me on this.