vba loop until boolean is false - userform button

Question

I want to execute a sub over and over again while a button is set true. My first idea would be a while or do while loop but I cant quite figure out how its done properly. In my case Excel just stops running.

A Userform Button sets the boolean "status" true or false when pressed. If the "status" is true the sub should be executed in a loop. The sub itself triggers a calculation which adds an amount of money to the double "bank". If the same button is pressed again the loop should end.

Function aktivate(i As Integer)
    array(i).status = True
    call myLoop(i)
End Function


Function deaktivate(i As Integer)
    array(i).status = False
End Function


Function myLoop(i As Integer)
    Do While array(i).status = True
        Call calc(i)

        If array(i).status = False Then Exit Do
    Loop
End Function

Siddharth Rout Siddharth Rout · Accepted Answer · 2019-03-12T10:52:07

You need to use DoEvents inside the Do While loop so that you can press the "Stop" button. If you don't then the code will become unresponsive and crash excel.

See this example

Dim stopLoop As Boolean

Sub StartCode()
    stopLoop = False
    LoopCode
End Sub

Sub StopCode()
    stopLoop = True
End Sub

Sub LoopCode()
    Do While stopLoop = False
        Debug.Print "blah"
        DoEvents
    Loop
End Sub

vba loop until boolean is false - userform button

1 Answers