How to stop an endless loop (Office 2010, 2013...)

Stopping endless loops in VBA

One of the most disappointing things for a VBA beginner is the handling of the endless loops.

When using loops ("for loop", "while loop", "do loop", etc) sometimes we use conditions that are always true and the macro runs forever,  in programming we call it an endless loop.

To give you an example, think about, but please do not run, the following macro:

Sub endless()

While Worksheets(1).Range("A1").value=""

Worksheets(1).Range("A1").value=Worksheets(1).Range("A1").value

Wend

End Sub

If on the first worksheet of the excel file, the cell(A1) is blank this macro will run forever, because the condition to be true is having the cell(A1) blank and this condition will always be true. The macro will run forever, we created an endless loop.

To stop the macro we would have to close the excel program (clicking on task manager-> choose the task -> end task) and to lose all the unsaved data in our file.

However, fortunately there is an easier way to stop an endless loop:

1. Before running the macro write the following script on the immediate window: Application.EnableCancelKey=xlInterrupt
2. Run the macro.
3. If your macro has an endless loop, or is taking much longer than you thought. Please press ESC and it will stop immediately. J

Please check the following video, if you did not get it: