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 ?

 

[Trebor76]

Hi Jaafar,

What about calling the code via the relevant tab's Worksheet_Change event, i.e.:

Private Sub Worksheet_Change(ByVal Target As Range)

    Call StartLoop

End Sub

So that your code will fire up again after any change is made.

HTH

Robert

 

[Jaafar Tribak]

Hi Robert.

Interesting idea.

There are two problems with your approach though:

1- Calling the StartLoop routine fires the loop recursively (Solved by Disabling the ApplicationEvents as shown in the code below)

2- Can't exit the loop by setting bStopMacro = True in the ExitLoop Routine. For some weird reason the Boolean Var bStopMacro always evaluates to True ! (Unsolved)

Here is the code I have :

In the Standard module :

Public bStopMacro As Boolean
 
Sub StartLoop()
 
    Do
       Range("a1") = Format(Time, "hh:mm:ss")
       Application.EnableEvents = True
       DoEvents
    Loop Until bStopMacro = True

    bStopMacro = False
 
End Sub
 
Sub ExitLoop()
 
    bStopMacro = True
 
End Sub

Code in the Worksheet module :

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not bStopMacro Then
        Application.EnableEvents = False
        Call StartLoop
        Application.EnableEvents = True
    End If

End Sub

 

[Jaafar Tribak]

Correction of the above.

2- Can't exit the loop by setting bStopMacro = True in the ExitLoop Routine. For some weird reason the Boolean Var bStopMacro always evaluates to False ! (Unsolved)

 

[Trebor76]

Hi Jaafar,

Not too sure (you're a better programmer than me!!)

The default setting for a boolean variable is False so obviously you're code isn't getting to the ExitLoop macro to change the variable to True. I can't see from the snippet of your code where the ExitLoop code is being called but at a guess I'd say this is where it's falling over (i.e. the code never gets there).

HTH

Robert

 

[Jaafar Tribak]

Hi Jaafar,

Not too sure (you're a better programmer than me!!)

The default setting for a boolean variable is False so obviously you're code isn't getting to the ExitLoop macro to change the variable to True. I can't see from the snippet of your code where the ExitLoop code is being called but at a guess I'd say this is where it's falling over (i.e. the code never gets there).

HTH

Robert

Thanks Robert for the prompt reply.

I am not sure I understand correctly.

Yes the Boolean bStopMacro Variable defaults to False but when the user runs the ExitLoop Sub, the bStopMacro variable is supposed to be set to True. Now , because it is a Public variable, it should evaluate to True in the loop code as well as in the Worksheet_Change code and therfore stop all running code.

 

[Trebor76]

but when the user runs the ExitLoop Sub

but how is this happening in practice i.e. if the user never manually runs the macro the variable won't change.

 

[Jaafar Tribak]

but how is this happening in practice i.e. if the user never manually runs the macro the variable won't change.

The ExitLoop Sub is assigned to a Button on the worksheet.

 

[mikerickson]

The code never escapes from this loop. bStopMacro is not changed inside the loop.

    Do
       Range("a1") = Format(Time, "hh:mm:ss")
       Application.EnableEvents = True
       DoEvents
    Loop Until bStopMacro = True

 

[Jaafar Tribak]

The code never escapes from this loop. bStopMacro is not changed inside the loop.

    Do
       Range("a1") = Format(Time, "hh:mm:ss")
       Application.EnableEvents = True
       DoEvents
    Loop Until bStopMacro = True

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.