Macro restart if stops between fixed times

[Vbalearner85]

Hi,

I have couple of macros which repeat every second/Minute based upon application on time method. Problem is sometimes some/all macros get interrupted and stop(while I think they are running), while I work in the workbook simultaneously.

Is there a way I can keep tab on the running status?? Auto restart if they stop within the timeframe( 10am-3PM).


Regards,

PK

 

[Anthony47]

Schedule, in addition to the 1 sec macro, a macro to run every minute and another to run every 15 minutes and another to run every hour.
Define 4 shared variables (on top of the vba module that hosts the 4 macros), and compile each variable with the next scheduled time for the macro to run.
Within the 1-sec-macro, check that the 1-minute-timer is not older than Now-5 secs; if it is older then restart the 1-minute-macro
Within the 1-minute-macro, check that the 1-sec-timer is compiled but isn't older than Now-5 sec; if it's older then restart the 1-sec-macro. Check that the 15-minutes-timeris compiled but isn't older than Now-1 min; if it is older then restart the 15-minutes-macro.
Within the 15-minutes-macro, check that the 1-minute-timeris compiled but isn't older than 3 minutes from Now; if it's older then restart the 1-minute-macro. Check also that the 1-hour-timeris compiled but isn't older than Now-5 mins; if it is older then restart the 1-hour-macro.

This multilevel schedule should make much more difficult the process to stop

Bye

 

[Vbalearner85]

Thanks Anthony47 for the conceptual solution........but I am not sure how exactly to implement it in the file

Also I normally reschedule ontime the macro run every 5-10-15min in workbook open event..but problem lies in same macro running 2 instances simultaneously....can we do something that if one instance of macro is srunning..it should not run duplicate instance of same macro( for example copying one range of 1 worksheet to another twice instead of once )

 

[Anthony47]

Only the 1-sec-macro is responsible for the job that it was intended to do. The added macros only check that the schedule goes on normally, and if necessary restarts it.
In terms of vba code, here are some suggestions:

'ON TOP OF THE MODULE:
Dim Next1Sec As Date, NextOneMin As Date, Next15Min As Date, Next1H As Date
Dim StopAll As Boolean      'Halt all the schedules

'
Sub OneSecondMacro()                           'This is your 1 second macro with amendments
If StopAll = False Then                  'See text for StopAll
    If Next1Min < (Now - TimeSerial(0, 0, 10)) Then
        Next1Min = Now + TimeSerial(0, 1, 0)
        Application.OnTime Next1Min, "OneMinuteMacro"
    End If
'
'  >>>> Here your code to do what every second should be done<<<<
'
'Then Reschedue myself:
    Next1Sec = Now + TimeSerial(0, 0, 1)
    Application.OnTime NextOneMin, "OneSecondMacro"
Else
'Stop all schedules:
    On Error Resume Next
        Application.OnTime NextOneMin, "OneSecMacro", , False
        Application.OnTime NextOneMin, "OneMinuteMacro", , False
        Application.OnTime NextOneMin, "Macro15Min", , False
        Application.OnTime NextOneMin, "OneHourMacro", , False
    On Error GoTo 0
End If
End Sub


Sub OneMinuteMacro()
If StopAll = False Then
    If Next1Sec < (Now - TimeSerial(0, 0, 10)) Then
        Next1Sec = Now + TimeSerial(0, 0, 1)
        Application.OnTime Next1Sec, "OneSecMacro"
    End If
    If Next15Min < (Now - TimeSerial(0, 5, 0)) Then
        Next15Min = Now + TimeSerial(0, 15, 0)
        Application.OnTime Next15Min, "Macro15Min"
    End If
'Reschedue myself:
    NextOneMin = Now + TimeSerial(0, 1, 0)
    Application.OnTime NextOneMin, "OneMinuteMacro"
Else
'Stop all schedules:
    On Error Resume Next
        Application.OnTime NextOneMin, "OneSecMacro", , False
        Application.OnTime NextOneMin, "OneMinuteMacro", , False
        Application.OnTime NextOneMin, "Macro15Min", , False
        Application.OnTime NextOneMin, "OneHourMacro", , False
    On Error GoTo 0
End If
End Sub

The variables Dimmed on top of the module, before any procedure code, are visible to all the macros in the module, so they can be compiled by one sub anche checked from another one.

A Sub Macro15Min and a Sub OneHourMacro should also be inserted, by copying the concepts of Sub OneMinuteMacro

Note that with the above approach you need to schedule only the OneSecMacro, and (when the macro is first excecuted) it will schedule the 1-minute-macro, that in turns will schedule the 15-min-macro, etc
I have added a StopAll flag that could help in stopping all the schedules (I dont know how you stop the schedule, maybe this flag would make things simpler)

As I stated, the sheduler time variables are visible to every macro in the same vba module; if you wish that they be visible to every module then you have to use Public instead of Dim: this way the variable will be, for example, visible from ThisWorkbook module and for example used in a BeforeClose macro, to stop all the schedulings before closing the workbook.

Bye