To stop a running Excel VBA loop on Windows, press Ctrl+Break (on some keyboards, use Ctrl+Fn+Pause), then choose End or Debug in the interruption dialog. On a Mac, try Command+Period (⌘+.). If Excel responds, you can then inspect the code and correct the loop so it does not get stuck again.
What happens when you interrupt a VBA loop?
VBA normally runs a macro on Excel’s main thread. A loop that never reaches its stopping condition can therefore make the workbook appear busy or unresponsive: you may not be able to select cells, use the ribbon, or close the window normally. The macro may still be running even when the screen is not changing.
An interrupt asks VBA to pause execution; it does not automatically repair the code or roll back what the macro has already done. If Excel displays an interruption dialog, End stops the current macro. Debug enters break mode at the statement where execution was interrupted, which is useful when you want to investigate. The exact dialog wording can vary by Excel version and platform.
In break mode, VBA is paused rather than finished. The editor usually highlights the next statement to run. You can inspect variables, step through code, continue with F5, or reset execution. Do not press F5 blindly: if the loop condition is still unchanged, the macro may immediately run into the same problem.
Ways to break a running macro
- Windows: Press Ctrl+Break. If your keyboard has no dedicated Break or Pause key, the combination may require Fn, or you can try the equivalent key on an external keyboard.
- Visual Basic Editor: If you can open it, choose Run > Break to interrupt execution. To show the editor from Excel, press Alt+F11 on Windows.
- Mac: Try Command+Period (⌘+.). Keyboard layouts and Mac hardware vary, so the shortcut may not be available in the same way on every setup.
- If the interruption dialog appears: Choose End to stop, or Debug to pause at the code and inspect it.
Press the interrupt once and give Excel a moment to respond. If the procedure is doing intensive work or is not yielding control, there may be a delay. Repeated key presses do not make the underlying loop safer. If you can reach the editor, use its Break command rather than clicking around the workbook while the macro is busy.
Why an endless loop happens
A loop is endless when its exit condition never becomes true, or when the code never reaches the statement that changes the condition. A common cause is a counter that is tested but never incremented:
Sub ExampleOfAnEndlessLoop()
Dim count As Long
count = 1
Do While count <= 10
' The counter never changes, so the condition stays true.
Loop
End Sub
This is an illustration of the bug, not code to run. A similar mistake can happen when a Do Until test is written with the wrong comparison, a variable is reset inside the loop, or a loop waits for a worksheet value that the macro itself never updates. A For loop can also take an impractically long time if its starting value, ending value, or step is wrong.
Loops that depend on worksheet data have their own traps. For example, a search that keeps moving to the next row may miss the condition that marks the last row, or a blank cell may cause the code to stop checking too early. Recursive procedures—procedures that call themselves—can also repeat without a base case, although they are not written with a loop statement.
Find the line and diagnose the cause
Choose Debug when prompted if you want to inspect the interruption point. In the Visual Basic Editor, the highlighted line indicates where execution is paused; it does not necessarily identify the original mistake. Check the loop’s condition and the statements that should change its inputs. Ask whether the condition can eventually become false and whether every route through the loop reaches the update.
Useful checks in the editor include:
- Hover over a variable to see its current value, or open View > Locals Window to review variables in the current procedure.
- Open the Immediate window with Ctrl+G on Windows and inspect a value by typing an expression such as
?count, then pressing Enter. - Use F8 to execute one statement at a time. Watch the counter or condition variable to see whether it changes as intended.
- Set a breakpoint by clicking beside a line or pressing F9 in the editor. A breakpoint is useful for stopping at a repeatable point during a later run.
Before resuming, make a change that addresses the cause. If the highlighted line is inside a loop, stepping through it once may clarify what should happen next. If a variable has an unexpected value, trace where it is assigned rather than merely changing the comparison until the macro stops. That distinction matters: an incorrect exit condition can make a macro terminate while leaving many rows unprocessed.
Fix the loop’s exit condition
A reliable loop has a clear stopping rule and updates the value that controls that rule. For the counter example above, this version processes ten iterations because count increases on each pass:
Sub CountToTen()
Dim count As Long
count = 1
Do While count <= 10
' Place the work for this iteration here.
count = count + 1
Loop
End Sub
For a loop over worksheet rows, use a known final row and a counter that advances. This example assumes there is a worksheet named Data and column A contains the records to process. Save a copy of the workbook before running or changing a macro: VBA actions generally cannot be undone with Ctrl+Z.
Sub ProcessDataRows()
Dim ws As Worksheet
Dim lastRow As Long
Dim rowNum As Long
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For rowNum = 2 To lastRow
' Add the work for this row here.
Next rowNum
End Sub
For loops that could run longer than expected, add a sensible limit or a condition that detects invalid input. For example, a search can stop at the last used row rather than scanning indefinitely. Avoid relying on a time limit alone: a time limit can prevent a hang, but it may also stop a valid operation before it finishes. If the code changes worksheet values, verify the final results before saving over an important file.
What DoEvents can and cannot do
DoEvents temporarily yields control so Excel can process pending events and respond to some user actions. In a long-running loop, it can make the interface more responsive and give Excel opportunities to handle an interrupt. It does not fix a loop whose exit condition never changes; the procedure will continue looping when control returns.
For example, a long row-processing procedure might call DoEvents periodically rather than on every iteration:
Sub ProcessRowsWithYielding()
Dim rowNum As Long
Dim lastRow As Long
lastRow = ThisWorkbook.Worksheets("Data").Cells( _
ThisWorkbook.Worksheets("Data").Rows.Count, "A").End(xlUp).Row
For rowNum = 2 To lastRow
' Process the current row here.
If rowNum Mod 100 = 0 Then DoEvents
Next rowNum
End Sub
Use this technique thoughtfully. While VBA yields, Excel may process events or other actions, so code that assumes the workbook cannot change during the procedure can behave unexpectedly. Calling DoEvents on every pass can also add overhead. A sound exit condition and a bounded amount of work matter more than adding frequent yields.
Recover without losing more work
After stopping the macro, check what it changed before continuing to use the workbook. A procedure may have updated some cells, deleted rows, changed formatting, or altered application settings before the interruption. Excel does not automatically reverse those actions. If the result is uncertain, close without saving only if you are willing to discard all unsaved workbook changes, not just the macro’s changes.
If the editor remains in break mode, choose Run > Reset in the Visual Basic Editor to reset the paused project. Resetting stops execution and clears the current procedure state, including its local variables; it is different from fixing the code. If Excel is completely unresponsive and the interrupt does not work, allow a little time for it to recover. As a last resort on Windows, use Task Manager to end Excel, understanding that unsaved changes may be lost. On a Mac, Force Quit has the same risk.
Before testing a correction, save a separate copy of the workbook. Macros cannot be undone with Ctrl+Z, and a procedure may make changes before reaching a later error. Test on a small or disposable data set first, then check that the macro processes the expected rows and stops at the expected point.
Excel desktop, Mac, and the web
These steps are for desktop Excel. Excel for the web does not run VBA macros; if a workbook contains VBA, open it in the desktop Excel application to run or debug that code. Keyboard shortcuts can also differ by hardware. On Windows, Ctrl+Break is the standard interrupt to try; on Mac, Command+Period is the common interrupt shortcut. If a shortcut does not work, use the Visual Basic Editor’s Run > Break command where available and consult the keyboard documentation for your device.
Frequently Asked Questions
Will Ctrl+Break damage my workbook?
It stops the running procedure but does not roll back changes already made. Inspect the workbook after interrupting, and use a saved backup if you cannot confirm the results.
Why does Ctrl+Break not work?
Your keyboard may require an Fn combination, Excel may be busy before it can handle the interrupt, or the macro may have disabled interruption. Try the Visual Basic Editor’s Run > Break command if accessible, and avoid force-quitting until you have given Excel time to respond.
Should I choose End or Debug?
Choose End if you only need to stop the procedure. Choose Debug if you want to pause at the interruption point and inspect variables or step through the code. Debug does not fix the loop by itself.
Does DoEvents stop an endless loop?
No. It yields control temporarily, which can help Excel respond, but the loop continues unless its exit condition becomes false or you interrupt it.
Can I undo a macro with Ctrl+Z?
Usually not. VBA operations generally clear or bypass Excel’s normal undo history. Save a copy before running an unfamiliar macro, especially one that changes or deletes data.
Related
- Excel Text to Columns Greyed Out? Causes and Fixes
- Excel Text to Columns: How to Split Data
- Excel Text to Columns: How to Enter a Delimiter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Rows: Select, Count, and Manage Worksheet RowsIn Excel VBA, Rows refers to worksheet rows or the rows within a specified range. For example, Worksheets("Data").Rows(5)…
- Excel VBA Find and Replace in a Range: Code and TipsTo find and replace text only within a chosen Excel range, use VBA’s Range.Replace method and call it…
- Excel VBA: How to Include Double Quotes in a StringIn Excel VBA, write two double-quote characters inside a string literal wherever you want one actual double quote.…
- Excel VBA Application-Defined Error: Causes and FixesAn Excel VBA “application-defined or object-defined error” is usually a runtime error 1004: Excel could not carry out…
- Access VBA InputBox: Syntax, Examples, and TipsIn Access VBA, use the built-in InputBox function to show a simple dialog and collect one line of…
- Excel UserForm Coding: Build a VBA Data-Entry FormExcel UserForm coding means using VBA to build a custom window for entering, reviewing, or selecting information in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.