Run-Time Error 1004 when Looping Application.OnTime Procedure

[cykwokad]

I wrote a macro AutoPing() that allows to loop itself every 5 minutes. It works between before 23:50. But it gets error when it passes to next day (00:00).
Please help me solve this error. Thank you.

Sub AutoPing()

Dim NextSchedule As String


If TimeSerial(Hour(Now), Application.WorksheetFunction.Floor(Minute(Time), 5) + 5, 0) <> TimeSerial(23, 55, 0) Then

NextSchedule = TimeSerial(Hour(Now), Application.WorksheetFunction.Floor(Minute(Time), 5) + 5, 0)

Else

NextSchedule = Date + 1 + TimeSerial(0, 0, 0)

End If



Call cmbPingSystem_Click

Application.OnTime EarliestTime:=NextSchedule, Procedure:="AutoPing", Schedule:=True




End Sub

 

[BSALV]

in thisworkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Reset_AutoPing
End Sub

in your module

Global dNextSchedule                                            'Global variable, is in memory, so you can recall him -> on top of the module !!!

Sub Reset_AutoPing()
     On Error Resume Next                                       'normally this goes wrong, thus use the OnError to proceed
     Application.OnTime dNextSchedule, "AutoPing", , 0          'reset your schedule because of that parameter 0
End Sub

Sub AutoPing()

     Reset_AutoPing                                             'normally you're here at moment dNextSchedule, but for safety, do a Reset_AutoPing

     dNextSchedule = Date + TimeSerial(Hour(Now), Application.WorksheetFunction.Floor_Math(Minute(Time) + 5, 5), 0)
     Application.OnTime dNextSchedule, "AutoPing", , 1
     Beep
     'Call cmbPingSystem_Click

End Sub

 

[cykwokad]

in thisworkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Reset_AutoPing
End Sub

in your module

Global dNextSchedule                                            'Global variable, is in memory, so you can recall him -> on top of the module !!!

Sub Reset_AutoPing()
     On Error Resume Next                                       'normally this goes wrong, thus use the OnError to proceed
     Application.OnTime dNextSchedule, "AutoPing", , 0          'reset your schedule because of that parameter 0
End Sub

Sub AutoPing()

     Reset_AutoPing                                             'normally you're here at moment dNextSchedule, but for safety, do a Reset_AutoPing

     dNextSchedule = Date + TimeSerial(Hour(Now), Application.WorksheetFunction.Floor_Math(Minute(Time) + 5, 5), 0)
     Application.OnTime dNextSchedule, "AutoPing", , 1
     Beep
     'Call cmbPingSystem_Click

End Sub

Thank you. It works.
But why can't I use Dim instead of Global?

 

[BSALV]

ww.youtube.com/watch?v=33JmyY83IpA
Dim is for variable within a procedure, while public and global are for variables valid within a whole procedure or even the whole project.

I wanted to memorise "dNextSchedule", the timestamp of the next time to restart the macro "AutoPing".
That timestamp has to be accessable for several procedures (macros) and remain in memory even after execution of the macro. So you have to declare it outside a macro and on top of a module.
I only used dNextSchedule within this module, so Public as enough, Global wasn't necessary.

dNextSchedule is created and refreshed every time "AutoPing" runs and remains its value even during the 4 minutes and 59 seconds of waiting for the next execution of "AutoPing".

Suppose you want to close the workbook, the annoying thing with "OnTime" is that it reopens the workbook at moment dNextSchedule. So, by calling "Reset_AutoPing" in the "Workbook_BeforeClose"-event, you prevent this.

Here you execute at every multiple of "5 minutes - 0 seconds" but suppose that was free and you run the macro "Auto_ping" now and a 2nd time 1 minute later. Unless that was the intention, but you have at that moment simultanious 2 moments of refreshing.
Suppose 1st run is moment 0, 2nd run is moment +1 minute, then the recalls 'll be at moment +5,+10, +15, +20, ... for the 1st and +6, +11, +16, +21, ... for the 2nd. That's why, as routine, as safety, the 1st line in "AutoPing"-macro is "Reset_AutoPing" to avoid multiple such simultanious runs.

 

[cykwokad]

ww.youtube.com/watch?v=33JmyY83IpA
Dim is for variable within a procedure, while public and global are for variables valid within a whole procedure or even the whole project.

I wanted to memorise "dNextSchedule", the timestamp of the next time to restart the macro "AutoPing".
That timestamp has to be accessable for several procedures (macros) and remain in memory even after execution of the macro. So you have to declare it outside a macro and on top of a module.
I only used dNextSchedule within this module, so Public as enough, Global wasn't necessary.

dNextSchedule is created and refreshed every time "AutoPing" runs and remains its value even during the 4 minutes and 59 seconds of waiting for the next execution of "AutoPing".

Suppose you want to close the workbook, the annoying thing with "OnTime" is that it reopens the workbook at moment dNextSchedule. So, by calling "Reset_AutoPing" in the "Workbook_BeforeClose"-event, you prevent this.

Here you execute at every multiple of "5 minutes - 0 seconds" but suppose that was free and you run the macro "Auto_ping" now and a 2nd time 1 minute later. Unless that was the intention, but you have at that moment simultanious 2 moments of refreshing.
Suppose 1st run is moment 0, 2nd run is moment +1 minute, then the recalls 'll be at moment +5,+10, +15, +20, ... for the 1st and +6, +11, +16, +21, ... for the 2nd. That's why, as routine, as safety, the 1st line in "AutoPing"-macro is "Reset_AutoPing" to avoid multiple such simultanious runs.

Thank you very much. It is really clear and useful to me.?