Method 1 – Exit a Loop Early
Steps:
- Insert a module as stated earlier.
- Write the following code inside the module.
Code Syntax:
Sub EarlyExitExample()
Dim row As Integer
For row = 5 To 14 ' assuming the data starts at row 5 and ends at row 14
If row = 7 Then
Range("D" & row).End(xlToLeft).Select
Selection.Interior.ColorIndex = 35
' exit the loop when we reach row 7
Exit For ' early exit without meeting a condition statement
End If
' put any code you want to execute inside the loop
Next row
MsgBox "Processing row " & row
End SubCode Breakdown:
- Sub EarlyExitExample() – This line starts the definition of a new subroutine called “EarlyExitExample”.
- Dim row As Integer – This line declares a variable called “row” as an integer.
- For row = 5 To 14 – This line starts a “For” loop that will execute the code inside the loop for each value of “row” between 5 and 14.
- If row = 7 Then – This line starts an “If” statement that checks whether the current value of “row” is equal to 7.
- Range(“D” & row).End(xlToLeft).Select – This line selects the cell in column D on the current row and moves the selection to the last non-empty cell to the left of the selected cell.
- Selection.Interior.ColorIndex = 35 – This line sets the interior color of the selected cell to color index 35 (coral).
- Exit For – This line immediately exits the “For” loop without reaching the loop’s end.
- End If – This line ends the “If” statement.
- Next row – This line moves the loop to the next value of “row”.
- MsgBox “Processing row ” & row – This line displays a message box that indicates the value of the “row” being processed when the loop was exited.
- End Sub – This line ends the definition of the subroutine.
Run the code. And you will see how to exit a for loop with Excel VBA.
You will get the output. Inside the MsgBox, click OK to end the process.
Method 2 – Exit a Loop When Condition Met
Steps:
- Insert a module as stated earlier.
- Write the following code inside the module.
Code Syntax:
Sub ExitForExample()
Dim row As Integer
For row = 5 To 14 'assuming the data starts at row 5 and ends at row 14
If Range("D" & row).Value = 100 Then 'assuming "Marks" column is in column D
Range("D" & row).End(xlToLeft).Select
Selection.Interior.ColorIndex = 35
MsgBox "Student " & Range("B" & row).Value & " got a perfect score!"
Exit For 'exit the loop when a perfect score is found
End If
Next row
End SubCode Breakdown:
- Sub ExitForExample(): This line defines the start of the VBA subroutine, named ExitForExample.
- Dim row As Integer: This line declares a variable row as an integer data type to store the loop counter.
- For row = 5 To 14: This line starts a For loop that will iterate through rows 5 to 14.
- If Range(“D” & row).Value = 100 Then: This line checks if the value in column D and the current row is equal to 100.
- Range(“D” & row).End(xlToLeft).Select: This line selects the last cell in the row that has a value to the left of column D.
- Selection.Interior.ColorIndex = 35: This line changes the background color of the selected cell to a specific color (color index 35).
- MsgBox “Student ” & Range(“B” & row).Value & ” got a perfect score!”: This line displays a message box showing the name of the student in column B and the message that the student got a perfect score.
- Exit For: This line exits the For loop early if a perfect score is found.
- Next row: This line indicates the end of the loop and moves the loop counter to the next row.
- End Sub: This line indicates the end of the subroutine.
Run the code.
You will get the output. Inside the MsgBox, click OK to end the process.
Method 3 – Exit a For Each Loop
Steps:
- Insert a module as stated earlier.
- Write the following code inside the module.
Code Syntax:
Sub ExitForEachExample()
Set Rng = Range("B5:D14")
For Each cell In Rng ' assuming the data range is B5:D14
If cell.Value = "Lily" Then
cell.Select
Selection.Interior.ColorIndex = 35
End If
If cell.Value = "Lily" Then ' exit the loop if we find the name "Lily"
Exit For
End If
' put any code you want to execute inside the loop
Next cell
MsgBox "Processing " & cell.Value & " in " & cell.Offset(0, 1).Value
End SubCode Breakdown:
- Sub ExitForEachExample(): starts the definition of a subroutine named “ExitForEachExample”.
- Set Rng = Range(“B5:D14”): defines a range object named “Rng” that encompasses the range B5:D14.
- For Each cell In Rng: starts a loop that iterates over each cell in the range “Rng”.
- If cell.Value = “Lily” Then: checks if the value of the current cell is equal to “Lily”.
- cell.Select: selects the current cell.
- Selection.Interior.ColorIndex = 35: sets the background color of the selected cell to a specific color.
- End If: ends the if-block.
- If cell.Value = “Lily” Then: checks if the value of the current cell is equal to “Lily”.
- Exit For: exits the loop if the current cell’s value is equal to “Lily”.
- End If: ends the if-block.
- Next cell: proceeds to the next cell in the loop.
- MsgBox “Processing ” & cell.Value & ” in ” & cell.Offset(0, 1).Value: displays a message box with a string that concatenates the values of the current cell and the cell one column to the right of it.
- End Sub: indicates the end of the subroutine.
Run the code.
You will get the output. Inside the MsgBox, click OK to end the process.
Method 4 – Exiting a For Loop Using GoTo Statement
Steps:
- Insert a module as stated earlier.
- Write the following code inside the module.
Code Syntax:
Sub ExitForLoopWithGoto()
Dim i As Integer
For i = 5 To 14 ' assuming the data range is in rows 5 to 14
If Range("B" & i).Value = "Alex" Then ' exit the loop if we find the name "Alex"
GoTo exitLoop
End If
' put any code you want to execute inside the loop
Next i
exitLoop:
Set Rng = Range("B5:D14")
For Each cell In Rng ' assuming the data range is B5:D14
If cell.Value = "Alex" Then
cell.Select
Selection.Interior.ColorIndex = 35
End If
Next cell
MsgBox "Processing " & Range("B" & i).Value & " in " & Range("C" & i).Value
End SubCode Breakdown:
- “Sub ExitForLoopWithGoto()” – defines the start of the subroutine.
- “Dim i As Integer” – declares a variable “i” as an integer data type.
- “For i = 5 To 14” – starts a loop that iterates from 5 to 14.
- “If Range(“B” & i).Value = “Alex” Then” – checks if the value in column B and the current row is equal to “Alex”.
- “GoTo exitLoop” – jumps to the label “exitLoop” if the condition in line 4 is met.
- “Next i” – moves to the next iteration of the loop.
- “exitLoop:” – defines a label “exitLoop”.
- “Set Rng = Range(“B5:D14″)” – assigns a range of cells B5:D14 to the variable “Rng”.
- “For Each cell In Rng” – starts a loop that iterates over each cell in the range “Rng”.
- “If cell.Value = “Alex” Then” – checks if the value in the current cell is equal to “Alex”.
- “cell.Select” – selects the current cell.
- “Selection.Interior.ColorIndex = 35” – sets the interior color of the selected cell to 35 (coral).
- “Next cell” – moves to the next iteration of the loop.
- “MsgBox “Processing ” & Range(“B” & i).Value & ” in ” & Range(“C” & i).Value” – displays a message box with the value of cell B and C in the row where the name “Alex” was found.
- “End Sub” – defines the end of the subroutine.
Run the code.
You will get the output. Inside the MsgBox, click OK to end the process.
Method 5 – Early Exit of a For Loop by Changing Loop Counter
Steps:
- Insert a module as stated earlier.
- Write the following code inside the module.
Code Syntax:
Sub EarlyExitForLoopCounter()
Dim i As Integer
For i = 5 To 14 ' assuming the data range is in rows 5 to 14
If Range("B" & i).Value = "Ryan" Then ' exit the loop if we find the name "Ryan"
i = 15 ' set the loop counter to a value outside the loop range to exit early
Else
' put any code you want to execute inside the loop
End If
Next i
Set Rng = Range("B5:D14")
For Each cell In Rng ' assuming the data range is B5:D14
If cell.Value = "Ryan" Then
cell.Select
Selection.Interior.ColorIndex = 35
End If
Next cell
MsgBox "Processing " & Range("B" & 9).Value & " in " & Range("C" & 9).Value
End SubCode Breakdown:
- Sub EarlyExitForLoopCounter(): Defines a subroutine with the name “EarlyExitForLoopCounter”.
- Dim i As Integer: Declares a variable “i” as an integer.
- For i = 5 To 14: Starts a loop to iterate from 5 to 14, assigning the values to “i”.
- If Range(“B” & i).Value = “Ryan” Then: Checks if the value in column “B” and current row “i” equals “Ryan”.
- i = 15: Sets the value of “i” to 15 to exit the loop.
- Else: Executes when the condition in line 4 is not met.
- Next i: Moves to the next iteration of the loop.
- Set Rng = Range(“B5:D14”): Defines a range variable “Rng” for cells B5 to D14.
- For Each cell In Rng: Starts a loop to iterate over each cell in the range “Rng”.
- If cell.Value = “Ryan” Then: Checks if the value of the current cell equals “Ryan”.
- cell.Select: Selects the current cell.
- Selection.Interior.ColorIndex = 35: Sets the color of the selected cell’s interior to index 35.
- Next cell: Moves to the next iteration of the loop.
- MsgBox “Processing ” & Range(“B” & 9).Value & ” in ” & Range(“C” & 9).Value: Displays a message box with a string concatenation of “Processing ” with the value in cell B9 and C9.
- End Sub: defines the end of the subroutine.
Run the code.
You will get the output. Inside the MsgBox, click OK to end the process.
We saw a different examples of prematurely breaking a For Loop. It is time to learn how to exit/break other types of VBA Loops, such as the Do-Before Loop, Do-While Loop, and Infinite Loop.
How to Exit/ Break Do-Until Loop in Excel VBA
Steps:
- Insert a module as stated earlier.
- Write the following code inside the module.
Code Syntax:
Sub DoUntilLoop()
Dim i As Integer
i = 1
Do Until i > 10
If i = 7 Then 'Break the loop when i is equal to 7
Exit Do
Else
i = i + 1
End If
Loop
Set Rng = Range("B5:D14")
For Each cell In Rng ' assuming the data range is B5:D14
If cell.Value = "Alex" Then
cell.Select
Selection.Interior.ColorIndex = 35
End If
Next cell
'Print the row number and student name
MsgBox "Row " & i & ": " & Cells(i, 2).Value
End SubCode Breakdown:
- Sub DoUntilLoop() – defines the start of the subroutine
- Dim i As Integer – declares a variable “i” as an integer data type
- i = 1 – assigns the value 1 to the variable “i”
- Do Until i > 10 – start a Do Until loop until the value of “i” becomes greater than 10
- If i = 7 Then – checks if the value of “i” is equal to 7
- Exit Do – exits the Do Until loop if the value of “i” is equal to 7
- Else – if the value of “i” is not equal to 7, then continue to the next line of code
- i = i + 1 – increments the value of “i” by 1
- Loop – ends the Do Until loop
- Set Rng = Range(“B5:D14”) – defines a range of cells from B5 to D14 and assigns it to the variable “Rng”
- For Each cell In Rng – starts a For Each loop that iterates through each cell in the range “Rng”
- If cell.Value = “Alex” Then – checks if the value of the current cell is equal to “Alex”
- cell.Select – selects the current cell
- Selection.Interior.ColorIndex = 35 – sets the background color of the selected cell to color index 35
- Next cell – goes to the next cell in the For Each loop
- MsgBox “Row ” & i & “: ” & Cells(i, 2).Value – displays a message box showing the row number and student name, where the student name is taken from the value in column B at the same row as the current value of “i”.
- End Sub – defines the end of the subroutine.
Run the code.
You will get the output. Inside the MsgBox, click OK to end the process.
How to Break a Do-While Loop in Excel VBA
Steps:
- Insert a module as stated earlier.
- Write the following code inside the module.
Code Syntax:
Sub DoWhileLoopExample()
Dim ws As Worksheet
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Sheet1") 'Change "Sheet1" to the name of your worksheet
i = 5
Do While i <= 15
If ws.Cells(i, 4).Value > 95 Then 'Change 4 to the column number of "Marks"
Exit Do
Else
i = i + 1
End If
'Perform any actions inside the loop here
Loop
Set Rng = Range("D5:D14")
For Each cell In Rng ' assuming the data range is B5:D14
If cell.Value >= 95 Then
cell.Select
Selection.Interior.ColorIndex = 35
End If
Next cell
MsgBox "Marks value exceeds 95. Exiting loop now."
End SubCode Breakdown:
- Sub DoWhileLoopExample(): starts the definition of the VBA subroutine named “DoWhileLoopExample”.
- Dim ws As Worksheet: declares a variable named “ws” as a Worksheet object.
- Dim i As Long: declares a variable named “i” as a Long integer.
- Set ws = ThisWorkbook.Worksheets(“Sheet1”): assigns the Worksheet object that represents the worksheet named “Sheet1” to the variable “ws”.
- i = 5: initializes the loop counter “i” to 5.
- Do While i <= 15: starts a do-while loop that continues as long as the loop counter “i” is less than or equal to 15.
- If ws.Cells(i, 4).Value > 95 Then: checks if the value in column 4 (assumed to be the “Marks” column) in the current row is greater than 95.
- Exit Do: exits the do-while loop if the condition in line 7 is true.
- Else: executes if the condition in line 7 is false.
- i = i + 1: increments the loop counter “i” by 1.
- Loop: ends the do-while loop.
- Set Rng = Range(“D5:D14”): assigns the range D5:D14 to the variable “Rng”.
- For Each cell In Rng: starts a loop that iterates through each cell in the range “Rng”.
- If cell.Value >= 95 Then: checks if the value in the current cell is greater than or equal to 95.
- cell.Select: selects the current cell.
- Selection.Interior.ColorIndex = 35: sets the background color of the selected cell to light blue (ColorIndex = 35).
- Next cell: goes to the next cell in the range.
- MsgBox “Marks value exceeds 95. Exiting loop now.”: displays a message box with the specified text.
- End Sub: defines the end of the subroutine.
Run the code.
Inside the MsgBox, click OK to end the process.
What Is an Infinite Loop in Excel VBA?
An infinite loop in Excel VBA is a loop that runs indefinitely because the loop condition is never met or because the code inside the loop does not allow the loop to exit. This can cause the program to freeze or crash and can lead to loss of data. It’s important to ensure that any loops in Excel VBA have a clear exit condition and to test the code thoroughly to avoid infinite loops. Below is an example of an infinite Do-While Loop:
This code creates an infinite loop in VBA that displays a message box repeatedly. The loop will run indefinitely unless the user manually stops it by pressing Ctrl + Break on the keyboard.
How to Break an Infinite Loop or Any VBA Loop with Keyboard in Excel
Breaking a loop using the keyboard is important because it allows the user to stop an infinite or long-running loop that may otherwise freeze or crash the program or the computer. By pressing the Ctrl + Break keys, the user can interrupt the loop and stop the execution of the code, preventing any potential harm or damage to the system. It is, therefore, important for programmers to include ways to break loops in their code and for users to know how to do so in case of an infinite or long-running loop. Below is an example:
Best Practice While Using a Loop in Excel VBA
In VBA, it is important to anticipate and handle errors that might occur during the execution of a loop. This is because a single error can cause the entire loop to terminate prematurely or produce incorrect results. One common way to handle errors is to use the “On Error” statement, which allows the user to define a specific error-handling routine. In this routine, the user can use various VBA statements to address the error, such as displaying a message box or logging the error to a file. By implementing proper error-handling techniques, it can be ensured that a loop runs smoothly and handles any errors that may occur. Below is an example:
You can also see the output image:
Advantages of Loops in Excel VBA
- Saves time and resources: Exiting a loop prematurely saves time and computational resources that would have been used to complete the remaining iterations of the loop.
- Increases efficiency: By exiting a loop prematurely when a specific condition is met, you can increase the efficiency of your code and improve its performance.
- Improves readability: Exiting a loop prematurely can also improve the readability of your code by eliminating unnecessary code that would have been executed if the loop continued to its natural conclusion.
Disadvantages of Loops in Excel VBA
- Incomplete execution: Exiting a loop prematurely means that some iterations of the loop will not be executed. This can lead to incomplete data analysis or calculations, potentially compromising the accuracy of your results.
- Unexpected behavior: If you’re not careful, prematurely exiting a loop can lead to unexpected behavior in your code, especially if you have nested loops or other code that depends on the loop to complete its execution.
- Difficult to debug: Exiting a loop prematurely can make it difficult to debug your code, as it can be challenging to determine why the loop terminated before it was supposed to.
Overall, it’s important to carefully consider the advantages and disadvantages of exiting a loop prematurely in VBA before implementing this strategy in your code. While it can be a useful technique in some cases, it’s essential to ensure that your code remains accurate, efficient, and easy to debug.
In this article, we’ll explore the loop-exiting methods of VBA in-depth. We’ll also provide examples of how to exit a loop in VBA and discuss best practices for using these statements, including error handling and avoiding infinite loops. By the end of this article, you’ll have a thorough understanding of how to exit a loop in VBA and which method is best for your specific use case.
Download Practice Workbook
You can download this workbook.
Related Articles
- Excel VBA: Running a Backwards For Loop
- Excel VBA with For Loop Decrement
- Excel VBA For Loop with Array
- How to Use Excel VBA Nested For Loop
