Use sleep function but allow for input to end macro?

Question

i have a macro that loops and uses the sleep function for a random interval between 5 seconds and 120 seconds after each loop. When the macro goes to sleep after carrying operations i am unable to end the macro without resorting to using control break where control break seems to work intermittently. So my question is this. How would i go about putting the macro to sleep for any amount of time but to also be able to terminate the macro at any time during sleep without control break?

Answer 1

A different approach to pausing in line would be to use Application.OnTime ...

Public Sub PauseInBackground()
    Dim lngSeconds As Long, strNextTime As String
    
    Randomize (Second(Time))
    lngSeconds = Rnd() * 120
    
    strNextTime = Format(DateAdd("s", lngSeconds, DateValue("01/01/1800")), "hh:mm:ss")

    Debug.Print "Next Time = " & strNextTime
    
    Application.OnTime Now + TimeValue(strNextTime), "RoutineToRun"
End Sub

Public Sub RoutineToRun()
    Debug.Print "Now = " & Now()
    
    ' Your code/logic
    
    PauseInBackground
End Sub

... it won't pause the macro in place.

https://learn.microsoft.com/en-us/office/vba/api/excel.application.ontime