How to Use Excel VBA to Break Infinite Loop (3 Easy Ways)

We can use conditional statements, error handling, and the “Exit that part” option to break an infinite loop in Excel VBA.

What is an Infinite Loop in Excel VBA?

An Infinite loop occurs when a section of code is repeated continuously without any break. This makes the code unresponsive and can cause the program to crash.

Here is an example of an infinite loop:

Sub Infinite_Loop1()
x = 0
Do Until x = 2
x = x + 1
Loop
End Sub

In the above code, we wanted to run the code until the value of x is equal to 2, however at the beginning of the loop, we keep resetting the value of x to 0. As the value of x is never going to be 2, this creates an infinite loop.

How to Launch VBA Editor in Excel

To write a VBA code in Excel, we have to launch the VBA editor, which requires the Developer tab. If you don’t see the Developer tab, then you will have to enable it.

Insert your code into a module as follows.

Steps:

• Go to the Developer tab >> click on Visual Basic.

The Microsoft Visual Basic Editor will open.

• To add a module, click on Insert >> Select Module.

Excel VBA to Break Infinite Loop: 3 Easy Ways

Here, we have a dataset containing the Name, Department, and Salary of some employees. Using this dataset we will create some infinite loops and demonstrate different ways to use Excel VBA to break them and solve the issues.

Method 1 – Using Keyboard Shortcuts

In the first example, we will use some code to create an infinite loop showing the Salary of the employees, the stop the loop with keyboard shortcuts.

• Insert the following code into your module:

Sub Keyboard_Shortcuts()
'Insert range as per dataset
Set Rng = Range("D5:D13")
For i = 1 To Rng.Rows.Count
MsgBox Rng.Cells(i, 1)
i = i - 1
Next i
End Sub

In the above code, we created a Sub Procedure named Keyboard_Shorcuts. Then, we set the cell range D5:D13 as Rng. We then created a For loop where the value is incremented by 1 each time and used a MsgBox to show the value of cell (i,1). Finally, we subtracted 1 from the loop counter (i) on each iteration. This line creates an infinite loop as the value of i will never change.

• If you run the code, it gets stuck in an indefinite loop showing the same answer repetitively.

To find out where the error is occurring or end the loop, we can use several different keyboard shortcuts.

1.1  – Using the Esc Button to End Infinite Loop

This is the simplest way to break an infinite loop. It immediately stops the execution if the code is running on the same thread as the interface. However, this button will not work if the code is executing on a separate thread.

• Press the Esc button and then click on End to stop the code execution.

1.2 – Using Ctrl + Break Buttons as a Conventional Technique

Ctrl + Break buttons will pause code execution and will allow you to debug your code and identify where the problem is. The break key can differ depending on the device. For example, use Ctrl + Fn + B buttons on ASUS laptops.

• After running the above code, click on your Ctrl + Break keys and select Debug.

Thus, you will identify the line where the loop is creating infinite results.

1.3 – Using Ctrl + Alt + Del When Excel VBA Freezes

Pressing Ctrl +Alt + Del when Excel VBA freezes will bring up the Windows Task Manager, which can be used to force Excel to close. This method should only be used when Excel is not responding.

Steps:

• After running your code, if Excel freezes, press Ctrl + Alt +Del and then select Task Manager.

In the Task Manager window, all the windows currently running will be shown.

• Select Microsoft Excel and click on End Task.

Thus, you can force stop the code and close Excel when it freezes.

1.4 – Using Alt + Esc or Ctrl + Scroll Lock If Above Commands Don’t Work

If the Esc button or Ctrl + Break buttons are not working, press down the Alt + Esc keys until it breaks the infinite loop. Use this method when you need to access other applications to troubleshoot the problem. In all Windows versions starting from Windows 7, these buttons will cycle through all open windows. In Office 13, you can use the Ctrl + Scroll Lock to do this without changing any settings.

• After running the code, click on Alt + Esc or Ctrl +Scroll Lock to break the infinite loop.

Read More: Excel VBA Break a Loop with Keyboard Shortcut

Method 2 – Using the On-Screen Keyboard

To stop an infinite loop in Excel VBA you can also use the on-screen keyboard.

Steps:

• Click on the Windows key >> select Settings.

• Go to the Accessibility tab >> click on the Keyboard option.
• Turn on the On-screen keyboard.

You will get a keyboard on your screen like below.

Alternatively, use the keyboard shortcut Windows + Ctrl + O to open the On-screen keyboard.

• Run your code by clicking on the Run button.
• To force stop the code, press Ctrl + ScrLk on the On-screen keyboard. Alternatively, click on Ctrl + Pause or press Esc twice.

Method 3 – Using a “Yes No Cancel” Button in a MsgBox

In this example, we will write some VBA code to enable breaking an infinite loop by pressing a Cancel button in a MsgBox. This will allow the user to exit the loop anytime by clicking the button, but it is not suitable for long code where the button may be needed multiple times.

• Insert the following code into a module:

Sub MsgBox_Cancel_Button()
'Insert range as per dataset
Set Rng = Range("D5:D13")
i = 1
Do While True
response = MsgBox(Rng.Cells(i, 1), vbYesNoCancel)
'If user clicks "Yes" button
If response = vbYes Then
i = i + 1
If i > Rng.Rows.Count Then
i = 1
End If
'If user clicks "No" button
ElseIf response = vbNo Then
'If user clicks "Cancel" button
ElseIf response = vbCancel Then
Exit Do
End If 'Exit the infinite loop if "Cancel" is clicked
Loop
End Sub

How to Break an Infinite Loop in Excel VBA for Mac

Use the Command (⌘) + Full stop (.) keys, or the Ctrl + Esc keys.

How to Break a For Loop in Excel VBA

The above examples showed how to debug or stop an endless loop. In the case of an endless For loop, we can use the Exit For statement to break the loop.

Sub Exit_For()
'Insert range as per dataset
Set Rng = Range("D5:D13")
For i = 1 To Rng.Rows.Count
MsgBox Rng.Cells(i, 1)
i = i - 1
Exit For
Next i
End Sub

In the above code, as the line “i=i-1” creates an endless loop, we used Exit For after that. As a result, the MsgBox shows the value of the first row, and then before going to the next iteration, it exits the For loop.

If you run the code, it will show the value of the first row once, then end the code.

How to Break a Do While Loop in Excel VBA

In this example, we have created an endless Do While loop which will show the values of Salary which are above the Salary limit in MsgBox.

Sub Do_While()
Dim SalaryLimit As Double
Dim Data_range As Range
Dim Name As String, Salary As Double
'Set the salary limit here
SalaryLimit = 60000
'Insert range as per dataset
Set Data_range = Range("B5:D13")
Do While True
For i = 1 To Data_range.Rows.Count
Name = Data_range.Cells(i, 1).Value
Salary = Data_range.Cells(i, 3).Value
If Salary > SalaryLimit Then
MsgBox Name & _
"'s salary is above the limit. Salary = $" & Salary
End If
Next i
Loop
End Sub

• To solve this problem, add an Exit Do statement to exit the loop after it has shown all the values looping through the Data_range once.

Sub Exit_Do()
Dim SalaryLimit As Double
Dim Data_range As Range
Dim Name As String, Salary As Double
'Set the salary limit here
SalaryLimit = 60000
'Insert range as per dataset
Set Data_range = Range("B5:D13")
Do While True
For i = 1 To Data_range.Rows.Count
Name = Data_range.Cells(i, 1).Value
Salary = Data_range.Cells(i, 3).Value
If Salary > SalaryLimit Then
MsgBox Name & "'s salary is above the limit. Salary = $" & Salary
End If
Next i
Exit Do 'Exit the infinite loop
Loop
End Sub

• Run the code to show the Salaries over 60000 just once.

Read More: Excel VBA Break While Loop

How to Break a Do Until Loop in Excel VBA

Do While and Do Until are similar in that they are used to repeat a block of code while a certain condition is true. The condition that determines when the loop ends its iteration is the main difference between them.

Do While loop continues as long as the condition in the block is True and Do Until loop continues to iterate until the condition specified after this statement is False.

Sub Do_Until()
Dim SalaryLimit As Double
Dim Data_range As Range
Dim Name As String, Salary As Double
'Set the salary limit here
SalaryLimit = 60000
'Insert range as per dataset
Set Data_range = Range("B5:D13")
Do Until False
For i = 1 To Data_range.Rows.Count
Name = Data_range.Cells(i, 1).Value
Salary = Data_range.Cells(i, 3).Value
If Salary > SalaryLimit Then
MsgBox Name & _
"'s salary is above the limit. Salary = $" & Salary
End If
Next i
Loop
End Sub

This code is similar to Example 4. Here, instead of using a Do While loop we used a Do Until loop and set it as False to create an endless loop.

• Use the Exit Do statement to exit the loop in this case too.

Sub Do_Until()
Dim SalaryLimit As Double
Dim Data_range As Range
Dim Name As String, Salary As Double
'Set the salary limit here
SalaryLimit = 60000
'Insert range as per dataset
Set Data_range = Range("B5:D13")
Do Until False
For i = 1 To Data_range.Rows.Count
Name = Data_range.Cells(i, 1).Value
Salary = Data_range.Cells(i, 3).Value
If Salary > SalaryLimit Then
MsgBox Name & "'s salary is above the limit. Salary = $" & Salary
End If
Next i
Exit Do 'Exit the infinite loop
Loop
End Sub

• Run the code to show the Salaries over 60000 just once.

How to Terminate a Macro While Running

There are several ways to terminate a macro while it is running:

• Right-clicking on the icon in the notification box, if you have chosen to show the Running icon when a macro is running.
• Pressing the Windows + End keys.

Change these default keys by going to Options >> Preferences >> Playback >> Miscellaneous tab.

Things to Remember

There are different Break buttons depending on which laptop you are using. Use the button assigned for your device.

Download Practice Workbook