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.

chris neilsen · Accepted Answer · 2012-03-06 09:05:25Z

11

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

answered Mar 6, 2012 at 9:05

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pelton · Accepted Answer · 2023-03-14 09:20:47Z

Doevents does the job, indeed. But it slows down the execution considerably. I usually implement doevents while programming and take them out once debugged.

When trapped in an infinite loop, no other solution than to shut down Excel by force (e.g. task manager), and recover the remains - which can by quite clumsy and frustrating.

NOW: By invoking Task Manger for this purpose using the keys Ctrl-Shift-Escape, I found out that Excel itself traps this key combination beforehand and, indeed, interrupts the code ! EUREKA !!! You CAN interrupt the never ending loop using Ctrl-Shift-Escape.

ONLY: Once you moved the focus OUT of the looping Excel VBA or worksheet, it won't work any more, probably because the looping program is too busy to get the focus back and take over the key handling.

My Office Version is 18.2301.1131.0, Windows 11

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related