Excel VBA: interrupt code execution (not by hitting 'escape')

Question

I have a Do .. While loop in which two For.. loops are nested; the first For.. loop counts up from 1 to X, the second loop counts down from X to 1. This is currently repeated ad infinitum.

Now, I want the user to be able to 'interrupt' this infinite loop, and that the program, upon interruption of the loop, executes XYZ.

I've tried using a toggle button in combination with the Do.. While loop, but while the aforementioned loop is running, no input is accepted. The state of the button does not change when clicked if the code is running the loop.

Any suggestions are highly appreciated.

Answer 1

The key is to include a DoEvents in the loops. This allows Excel to process things like a button click while running the code

Here's an outline. Assign macro ButtonClick to the button. Main will run indefinately until the button is clicked.

Option Explicit
Dim bBreak As Boolean

Sub ButtonClick()
    bBreak = True

End Sub

Sub Main()
    Dim X As Long
    Dim i As Long
    bBreak = False

    Do While True
        X = 1000
        For i = 1 To X
            If bBreak Then
                Exit Do
            End If
            DoEvents
        Next

        For i = X To 1 Step -1
            If bBreak Then
                Exit Do
            End If
            DoEvents
        Next
    Loop
    If bBreak Then XYZ
End Sub