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

Excel VBA is used to automate repetitive tasks by allowing users to write code in modules and custom functions. However, it can also be prone to errors, such as infinite loops. Infinite loops occur when a part of code is executed at indefinite times. To run your code successfully, you need to know how to use Excel VBA to break infinite loop. One way is to debug your code to identify the section of the code where the problem is and then it. You can use conditional statements, error handling, and the Exit that part option to break an infinite loop.

What Is Infinite Loop in Excel VBA?

An Infinite loop in Excel VBA 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, you can see an example of an infinite loop below.

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 we set the value of x as 0. As the value of x is never going to be 2 this will create an infinite loop.

How to Launch VBA Editor in Excel

To write a VBA code in Excel, you have to launch the VBA editor. To launch this VBA editor, you must need the Developer tab, now if you don’t see the Developer tab then you have to enable the Developer tab.

You can insert your code into a module following the steps given below.

• Firstly, go to the Developer tab >> click on Visual Basic.

• Now, the Microsoft Visual Basic Editor will open.
• Lastly, 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 show you different ways to use Excel VBA to break infinite loops and solve the issues.

1. Use Keyboard Shortcuts to Break Infinite Loop in Excel VBA

In the first example, we will use the following code which will create an infinite loop to show the salary of the employees.

• Insert the following code into your module to see how this indefinite loop is going to work.

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. Next, we 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 will create an infinite loop as the value of i will never change.

• Now, if you run the code you will notice that the code is running in an indefinite loop and showing the same answer repetitively.

To find out where the error is occurring or end the loop you can use different keyboard shortcuts. Use any of the buttons below to break your infinite loop.

1.1 Apply Esc Button to End Infinite Loop

Applying the Esc button 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.

• After you run the code, it will run continuously.
• Now, apply the Esc button and then click on End to stop the code execution.

1.2 Use 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 on which laptop you are using. For example, use Ctrl + Fn + B buttons to break your infinite loop if you are using ASUS laptops.

• After running the above code, click on Ctrl + Break button and select Debug.
• Thus, you will find the line where the loop is creating infinite results.

1.3 Use Ctrl + Alt + Del Buttons When Excel VBA Freeze

You can use the Ctrl +Alt + Del buttons when Excel VBA freezes. These buttons 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. To use this method follow the steps given below.

• After running your code if Excel freezes, press Ctrl + Alt +Del and then select Task Manager.
• In the Task Manager box, all the windows currently running will show. Select Microsoft Excel and click on End Task.

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

1.4 Use Alt + Esc or Ctrl + Scroll Lock If Above Commands Don’t Work.

If the Esc button or Ctrl + Break buttons are not working hold on to Alt + Esc buttons until it breaks the infinite loop. This button is used when you need to access other applications to troubleshoot the problem. All the versions starting from Windows 7, these buttons will go through all open windows. On 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 end the break of the infinite loop.

Thus, you can use these buttons to end or debug code to solve the current issue.

Read More: Excel VBA Break a Loop with Keyboard Shortcut

2. Use On-Screen Keyboard to Stop Infinite Loop in Excel

To stop an infinite loop in Excel VBA you can also use the on-screen keyboard. To open the on-screen keyboard follow the steps given below.

• Firstly, click on your Windows button >> select Settings.

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

• Thus, you will get a keyboard on your screen like below.

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

• Now, run your code by clicking on the Run button. Then, to force stop the code press Ctrl + ScrLk. You can also click on Ctrl + Pause or press Esc twice.

3. Use of Yes No Cancel Button in MsgBox to Break a Loop

In this example, we will show you how to use Excel VBA to break an infinite loop. For that, we will use the Cancel button in MsgBox to terminate an infinite loop in Excel. This method is user-friendly as it allows the user to exit the loop anytime the cancel button is clicked. However, it is not suitable for long codes where the user may need to click on the buttons multiple times.

• Insert the following code into your module to use this code.

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 Infinite Loop Excel VBA for Mac

To break an infinite loop in Excel VBA for Mac you can use the Command (⌘) + Full stop (.) buttons or Ctrl + Esc buttons.

How to Break For Loop in Excel VBA

In the above examples, we showed you how you can debug or stop an endless loop. In case of an endless For loop, you 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.

• Now, if you run the code, it will show the value of the first row just one time and 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, you can 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

Here, we added the Exit Do statement before the Loop statement to exit the loop.

• Now, run the code to show the Salaries over 60000 just one.

Read More: Excel VBA Break While Loop

How to Break a Do Until Loop in Excel VBA

In this example, we have created an endless Do Until loop which will show the values of salary that are above the Salary limit in MsgBox. 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.

You can use the Exit Do statement in this case too to exit the loop.

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

Here, we added the Exit Do statement before the Loop statement to exit the loop.

• Finally, run the code to show the Salaries over 60000 just one.

How to Terminate Macro While Running in Excel

There are several ways to terminate a macro while running. They are given below.

• The macro can be stopped by right-clicking on the icon in the notification box if you have chosen to show the Running icon when a macro is running.
• Alternatively, you can end the macro by pressing the Windows + End key.
• You can 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. Please try to use the button assigned for your type of laptop.

Download Practice Workbook

You can download the workbook to practice yourself.

Conclusion

So, in this article, you will find 6 examples of Excel VBA to break infinite loop. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here.