How to prevent a running Loop from stopping when editing the worksheet ?

[Jaafar Tribak]

Hi all . Consider the simple code below :

Private bStopMacro As Boolean
 
Sub StartLoop()
 
    bStopMacro = False
 
    Do
        Range("a1") = Format(Time, "hh:mm:ss")
        DoEvents
    Loop Until bStopMacro
 
End Sub
 
Sub ExitLoop()
 
    bStopMacro = True
 
End Sub

The code just updates Cell A1 with the current time. The Doevents is there so the application doesn't freeze and the user is able to edit the sheet while the loop is running.

The problem ,as we know, is that if the user edits a cell the loop is aborted.

Is there a way to have a loop that continues even after edting the worksheet ?

I know one could for example use the OnTime Method in the above scenario to overcome the issue in this particular situation but I am looking for a more generic solution that always works whenever using a Do Loop structure.

Any ideas ?

 

[Jaafar Tribak]

Ok at last I seem to have found a trick to overcome the problem of not being able to maintain a running DoLoop after editing the worksheet.

I have created a Public Boolean Property which I have named MaintainLoopAfterEditing and which you can just plug it at the top of the code (just before the start of the loop) and set it to True. This is to make it very easy and intuitive to use.

Unlike the above method involving the Worksheet_Change event, this is, I believe a cleaner approach and more importantly it allows the user to abort the loop at any time by running the ExitLoop Sub.

Here is an example (Put in a Standard Module )

Option Explicit
 
Private bStopMacro As Boolean
Private dRunWhen As Double
 
Sub StartLoop()
 
    [COLOR=red][B]MaintainLoopAfterEditing = True[/B][/COLOR]
    
    bStopMacro = False
    Do
        Range("a1") = Format(Time, "hh:mm:ss")
        Range("B1") = Range("B1") + 1
        DoEvents
    Loop Until bStopMacro
 
End Sub
 
Sub ExitLoop()
 
    On Error Resume Next
    bStopMacro = True
    Application.OnTime dRunWhen, "StartLoop", schedule:=False
 
End Sub
 
Public Property Let MaintainLoopAfterEditing _
(ByVal MaintainLoop As Boolean)
 
    If MaintainLoop Then
        On Error Resume Next
        Application.OnTime dRunWhen, "StartLoop", schedule:=False
        dRunWhen = Now + TimeSerial(0, 0, 1)
        Application.OnTime dRunWhen, "StartLoop", schedule:=True
    End If
 
End Property

Although the switching on/off of the Ontime Procedure will no doubt have a slight performance hit,the above code worked for me just fine.

 

[mikerickson]

Thanks Mik.

But as I mentioned above, I also want to be able to exit/abort the loop by setting the bStopMacro Variable to TRUE when calling the following Macro :

Sub ExitLoop()
 
    bStopMacro = True
 
End Sub

It doesn't work. Try it and you will see.

One can not run two macros at the same time.
The sub ExitLoop can not run while code execution is Doing the Loop.
Instead of a loop putting values in A1 and B1, waiting for ExitLoop, perhaps you could put the formula =NOW() in A1 and have ExitLoop change the formula into a constant.

 

[Jaafar Tribak]

One can not run two macros at the same time.
The sub ExitLoop can not run while code execution is Doing the Loop.
Instead of a loop putting values in A1 and B1, waiting for ExitLoop, perhaps you could put the formula =NOW() in A1 and have ExitLoop change the formula into a constant.

Hi Mik.

You CAN by using DoEvents

 

[mikerickson]

Do events pauses the code, checks Excel's events, and then returns to the code. DoEvents does not admit entry from the user interface.

In the below code, the DoEvents in infiniteLoop will not allow you to call test until the Loop is done.

Sub almostInfiniteLoop()
Dim counter as Double
counter = 0
Do
    DoEvents
    counter = counter + 1
Loop Until counter>10^6
End Sub

Sub test()
    MsgBox "x"
End Sub

 

[Jaafar Tribak]

In the below code, the DoEvents in infiniteLoop will not allow you to call test until the Loop is done.

It does Mik.

Are you trying to call the test routine from the VBIDE ?

In fact the main purpose of VBA including the DoEvents Statement is to be able to do this kind of things.

 

[Andrew Poulsom]

I was able to call test from a Forms CommandButton while almostInfiniteloop was running.

 

[Jaafar Tribak]

I was able to call test from a Forms CommandButton while almostInfiniteloop was running.

Yes.

AFAIK, You can call it from all other venues too such as KeyBoard shortcuts, Controls , worksheet and workbook events etc except from the VBIDE.

This is common knowledge and practice when coding.