Excel VBA Break While Loop (4 Unique Ways)

In Excel VBA, the “While” loop is structured to repeat a block of code while a certain condition is true. The loop will continue executing until the condition becomes false. If you need to break out of the “While” loop before the condition becomes false. This article will help you to know the use of Excel VBA break while loop.

If you need to stop the repetition of the code after meeting any specific criterion, not necessarily the overall condition specified in the code, you will need to exit the code. If the corresponding statement specified to break the code is encountered, Excel will terminate the loop and the program will start executing the code that comes after the loop.

Let’s have a quick look at the VBA code with the While loop we are using in this tutorial and check the execution of the code.

Why Do You Need to Use a Loop in Excel VBA?

Loops are an essential part of VBA as well as any language because they allow you to repeat a set of instructions a specified number of times or until a certain condition is met.

Loops enable users to automate repetitive tasks, reduce the amount of code they need to write, and make their code more efficient.

If you have a large dataset, then you might do a single task for each of the items in your dataset. But with the application of a loop, you can iterate through each row or column in your dataset in the blink of an eye. So, loops are an inevitable part of any programming language for performing repetitive tasks very efficiently.

Introduction to While Wend Loop in Excel VBA

The “While Wend” loop is structured in a way to run a code repeatedly until a specific criterion is true. This loop is also known as the “While loop” and is used in many programming languages.

Code:

Sub WhileLoopExample()
Dim n As Integer
n = 1
While n <= 5
Cells(n, 1).Value = n
n = n + 1
Wend
End Sub

This is a simple interpretation of the While Wend loop. This loop prints the values from 1 while it reaches 5.

How to Launch VBA Editor in Excel VBA

For executing VBA, you need to activate the Developer tab on Ribbon if you haven’t done it before. After launching the Developer tab on the Home screen, launch the Visual Basic Editor window.

• Go to the Developer tab and select Visual Basic under the Code

Alternative command: Pressing ALT+F11 will also take you to the VBA window.

• Now, the Visual Basic Editor window will show up on the screen. Click the Insert tab of the VBA window and select Module.

• You will see a Module window has popped up right beside the Project – VBAProject You have to insert the code you want to execute in this window.

You can either use multiple Modules for different Macros or can also insert your Macros just one after another in the same Module. If you have different macros for serving different purposes, then it is preferable to use different Modules as it will help find your macros quickly.

How to Use Excel VBA Break While Loop: 4 Unique Ways

For the dataset in the image above, we have some employees whose ages are arranged in chronological order. We will see some cases of finding employees whose ages are greater than 30. Implementing VBA While loop will allow iterating this process. We will now try to break out of the While loop after meeting a certain criterion.

1. Break Out of a While Loop with “Exit Sub” Statement

This is an overview of the output of our macro with a While loop. Here, our criterion is to find the very first person aged over 30. So, we have to set a condition where the While loop exits immediately after reaching the first age value after 30.

• After launching the Visual Basic Editor window, insert the following code in the code Module

The code contains an IF loop nested in the While loop.

Code:

Sub Nested_with_If_Loop()
Dim i As Integer
Set Rng = Range("B4:F11")
i = 2 'start at row 2 since row 1 contains headers
While i <= Rng.Rows.Count
    If Rng.Cells(i, 3).Value > 30 Then  'AgeLimit = 30
        Debug.Print "Found " & Rng.Cells(i, 2).Value & " whose age is over 30 "
        Exit Sub
    End If
    i = i + 1
Wend
End Sub

Sub Nested_with_If_Loop()

Dim i As Integer
Set Rng = Range("B4:F11")

i = 2

While i <= Rng.Rows.Count

If Rng.Cells(i, 3).Value > 30 Then  'AgeLimit = 30
        Debug.Print "Found " & Rng.Cells(i, 2).Value & " whose age is over 30 "
        Exit Sub
End If

 i = i + 1

Wend

Now, click Run on the toolbar options of the VBA window. The first person here aged over 30 is Sarah Wong (31). So, after running the macro, it will immediately print the output in the immediate console (If you don’t find the Immediate console, press CTRL+G to make the console visible). As we have used the Exit Sub command, the loop will break from iterating further.

2. Set a Variable as Boolean For a Range of Cells

A boolean variable is one type of data that represents two values=> True/False.

Boolean variables are often used in conditional statements and loops to control the flow of a program based on the outcome of a particular test or condition. Setting a variable as a Boolean and implementing this variable as the criteria will create the execution of a loop.

2.1. Show Output in Message Box

For our corresponding previous set of data, if you want to break out of the loop just immediately after obtaining the first person aged over 30, you have to Exit the repetitive iteration. For this, you can use the following code.

Code:

Sub ExitingWhileLoop()
Dim i As Integer
Dim found As Boolean
Set rng = Range("B4:F11")
found = False
i = 2
While i <= rng.Rows.Count
If rng.Cells(i, 4).Value = "Female" And rng.Cells(i, 3).Value > 30 Then
MsgBox "The first female employee over 30 is " & rng.Cells(i, 2).Value
found = True
Exit Sub
End If
i = i + 1
Wend
If Not found Then
MsgBox "No female employee over 30 found."
End If
End Sub

In this code, we have used nested IF loops inside the While loop.

But, if we didn’t break the loop, we would get the name of each person aged over 30, you have to repeat the iteration through the loop to the last row. The code below will give you the names of all people aged over 30.

Code:

Sub SettingBooleanVariable()
Dim i As Integer
Dim found As Boolean
Set rng = Range("B4:F11")
found = False
i = 2
While i <= rng.Rows.Count
If rng.Cells(i, 3).Value > 30 Then
MsgBox "The person named " & rng.Cells(i, 2).Value & " is aged over 30 "
found = True
End If
i = i + 1
Wend
End Sub

Dim found As Boolean

If rng.Cells(i, 3).Value > 30 Then
MsgBox "The person named " & rng.Cells(i, 2).Value & " is aged over 30 "
found = True
End If

After running the code, it will go through all the rows in the specified range and show a message box for each person over 30 years old. If there are no people over 30 in the range, nothing happens.

2.2. Show Output in Worksheet with Condition Specified in Code

Let’s consider a different situation. We have numbers in column B and we want to find the squares of the first five numbers from column B and put the output in column C of the active Excel worksheet. So, we have to break out of the “While” loop after finding the squares of the first 5 numbers.

Code:

Sub SquareNumbers()
Dim i As Integer
Dim number As Double
Dim squared As Double
Dim counter As Integer
Dim continueLoop As Boolean
counter = 0
i = 5
continueLoop = True
While continueLoop And i <= 14
number = Range("B" & i).Value
squared = number ^ 2
Range("C" & i).Value = squared
counter = counter + 1
i = i + 1
If counter = 5 Then
continueLoop = False
End If
Wend
End Sub

number = Range("B" & i).Value
squared = number ^ 2
Range("C" & i).Value = squared

If counter = 5 Then
    continueLoop = False
End If

3. Break Out of a While Loop Based on Multiple Conditions

For the previous dataset, if you want to break out of the “While” loop after getting the first female with an age over 30, you have to stop the iteration immediately after getting the first output. Here, we use the “And” clause in the code.

Code:

Sub BreakingLoopWithMultipleConditions()
Dim i As Integer
Dim found As Boolean
Set rng = Range("B4:F11")
found = False
i = 2
While i <= rng.Rows.Count And Not found
If rng.Cells(i, 3).Value > 30 And rng.Cells(i, 4).Value = "Female" Then
MsgBox "First female employee over 30 is " & rng.Cells(i, 2).Value
found = True
Exit Sub
End If
i = i + 1
Wend
If Not found Then
MsgBox "No female employee over 30 found."
End If
End Sub

This code will exit the loop just after getting the first output which means the name of the first female aged over 30.

If we don’t want to break the loop and wish to get each person over the age of 30  and after that exit the code, then the following code can be helpful.

Code:

Sub AndStatementWhileLoop()
Dim i As Integer
Dim found As Boolean
Set Rng = Range("B4:F11")
found = False
i = 2
While i <= Rng.Rows.Count And Not found
If Rng.Cells(i, 3).Value > 30 Then
MsgBox "Person named " & Rng.Cells(i, 2).Value & " is aged over 30 "
End If
i = i + 1
Wend
End Sub

So, the code searches a range of cells for people aged over 30 and displays a message box if it finds any. If the loop reaches the end of the range without finding any people aged over 30, it will exit normally.

4. Exit Infinite While Loop

An infinite loop is created when a loop in a program runs continuously without ever terminating. This can happen when the loop condition is always true, or if there is no condition at all. Some of the common reasons behind this disturbance are:

1. i) The loop condition is never met/logical error
2. ii) The loop counter is not incremented

iii) The loop condition is always true

1. iv)  Improper nesting of the loop. If a loop is nested within another loop and the inner loop does not properly reference the outer loop, an infinite loop can occur.

Consider the following code.

Code:

Sub Infinite_While__Loop()
Dim i As Integer
Set Rng = Range("B4:F11")
i = 2
While i <= Rng.Rows.Count
If Rng.Cells(i, 3).Value > 30 Then
Debug.Print "Found " & Rng.Cells(i, 2).Value & " whose age is over 30 "
Exit Sub
End If
Wend
End Sub

Don’t Run this code. This will create an infinite loop.

Note: Here, the variable is not updated, so the code will run infinitely and will always be not equal to 5.

If you have run the before correcting it and it is running infinitely, then there are some possible solutions for it:

1. Press the “Esc” key: If your code is currently running and the MsgBox is running infinitely, try pressing the “Esc” key on your keyboard. This should stop the code execution and close the MsgBox.
2. Use the Task Manager: If the first two methods don’t work, you can use the Task Manager to force quit Excel. This will close the MsgBox and any running code. To open Task Manager, press “Ctrl+Shift+Esc” on your keyboard, find the Excel process, right-click it, and choose “End task“.
3. Add an exit condition: If you know what condition needs to be met for the MsgBox to close, you can add this as an exit condition in your code. For example, if the MsgBox is running in a loop, you can add a counter and exit the loop after a certain number of iterations.

Solved Code:

Sub Exit_Infinite_While_Loop()
Dim i As Integer
Set Rng = Range("B4:F11")
i = 2 'start at row 2 since row 1 contains headers
While i <= Rng.Rows.Count
If Rng.Cells(i, 3).Value > 30 Then  'AgeLimit = 30
Debug.Print "Found " & Rng.Cells(i, 2).Value & " whose age is over 30 "
Exit Sub
End If
i = i + 1
Wend
End Sub

Now we have updated the counter variable and it won’t run infinitely further. This is the code of Method 1 that we used in that article.

Read More: How to Use Excel VBA to Break Infinite Loop

How to Exit “Do While” Loop in Excel VBA

Here, we see the use of the “Do While” loop. Like the previous examples in our method, if you want to exit the loop just after getting the first output, the “Exit Do” command is available here.

Use the following code with the “Exit Do” command.

Code:

Sub BreakDoWhile()
Dim i As Integer
Dim found As Boolean
Set rng = Range("B4:F11")
found = False
i = 2
Do While i <= rng.Rows.Count
If rng.Cells(i, 3).Value > 30 Then
MsgBox "The person named " & rng.Cells(i, 2).Value & " is aged over 30 "
found = True
Exit Do
End If
i = i + 1
Loop
End Sub

But if we want to extract the name of each person aged over 30 with the Do While loop, we don’t have to exit the loop. The following code will give you the names of all persons aged over 30.

Code:

Sub BreakingDoWhileLoop()
Dim i As Integer
Dim found As Boolean
Set rng = Range("B4:F11")
found = False
i = 2
Do While i <= rng.Rows.Count
If rng.Cells(i, 3).Value > 30 Then
MsgBox "The person named " & rng.Cells(i, 2).Value & " is aged over 30 "
found = True
End If
i = i + 1
Loop
End Sub

The Do While statement ends with Loop statement.

How to Exit “For” Loop in Excel VBA

Let’s reconstruct our dataset a little bit. We have two females in this dataset whom we want to show in the lower region (cell D14). This can be done with the application of the For loop.

If you want to Exit the loop just after getting the first name of the female, use the “Exit For” statement within the code.

Code:

Sub BreakForLoop()
Dim i As Integer
Dim rng As Range
Dim female As String
Set rng = Range("B4:F11")
For i = 1 To rng.Rows.Count
If rng.Cells(i, 4).Value = "Female" Then
female = rng.Cells(i, 2).Value
Exit For 'Exit the loop once the first female is found
End If
Next i
Range("D13").Value = female
End Sub

As we have used the “Exit Do” command, the code will immediately stop after getting the first value.

The code below will extract the female names separated with commas and put them in cell D14.

Code:

Sub BreakingForLoop()
Dim i As Integer
Dim rng As Range
Dim female As String
Set rng = Range("B4:F11")
For i = 1 To rng.Rows.Count
If rng.Cells(i, 4).Value = "Female" Then
female = female & rng.Cells(i, 2).Value & ", "
End If
Next i
'Remove the last comma and space from the concatenated string
female = Left(female, Len(female) - 2)
'Write the concatenated string to cell D13
Range("D13").Value = female
End Sub

If rng.Cells(i, 4).Value = "Female" Then
      female = female & rng.Cells(i, 2).Value & ", "
End If

female = Left(female, Len(female) - 2)

Range("D13").Value = female

How to Break “Do Until” Loop in Excel VBA

Let’s say, we have some numbers in a column of the Excel sheet. We want to get the square of the numbers just beside the right column. We can use the “Do Until” loop here.
The Do Until loop is a type of looping structure in VBA that allows you to repeatedly execute a block of code until a certain condition is met. The loop will continue to execute until the condition specified in the until statement becomes True.

In that case, our code will be as follows.

Code:

Sub BreakingDoUntilLoop()
Dim inputValue As Double
Dim outputValue As Double
Dim count As Integer
Range("B5").Select
count = 0
Do Until ActiveCell.Value = "" Or count >= 5 ' Stop after 5 squares have been calculated
inputValue = ActiveCell.Value
If inputValue > 0 Then ' Check if input is a positive number
outputValue = inputValue ^ 2
ActiveCell.Offset(0, 1).Value = outputValue
count = count + 1 ' Increment count of squares calculated
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Range("B5").Select

Do Until ActiveCell.Value = "" Or count >= 5

outputValue = inputValue ^ 2
ActiveCell.Offset(0, 1).Value = outputValue
count = count + 1

ActiveCell.Offset(1, 0).Select

Difference Between “While” and Other Different Loops in Excel VBA

1. Difference Between “While” and “Do While” Loop

In Excel VBA, the main difference between the While loop and the Do While loop is that the While loop checks the condition before executing the loop, while the Do While loop checks the condition after executing the loop.

Code:

Sub WhileLoop()
While i < 10
i = i + 1 Wend
End Sub

This loop checks the condition i < 10 before executing the loop, and if the condition is true, the code inside the loop will be executed. The loop will continue to execute as long as the condition is true.

Here’s an example of a Do While loop:

Code:

Sub DoWhileLoop()
Do While i < 10
i = i + 1 Loop
End Sub

This loop executes the code inside the loop first and then checks the condition i < 10 after the code has been executed. If the condition is true, the loop will continue to execute, and if the condition is false, the loop will exit.

2. Difference Between “While” and “For” Loop

If you know the number of iterations you can use the “While” loop, otherwise use the “For” loop for iterating through a range of cells or when you don’t know the number of iterations.

Let’s check an example of a While loop.

Code:

Dim i As Integer
i = 1
While i < 5
Range("A" & i).Value = i
i = i + 1
Wend

In the above example, the loop continues until the value of the variable i is less than 5. The loop will execute 4 times and it will write the value of i to cells A1 to A4.

In the “For” loop, the loop counter is incremented or decremented automatically by a specified amount after each iteration of the loop. Now, let’s check an example of a For loop:

Code:

For i = 1 To 5
Range("A" & i).Value = i
Next i

In the above example, the loop continues iteration 5 times and it will write the value of i to cells A1 to A5.

3. Comparison Between “While” and “Do Until” Loop

The “While” loop and the “Do Until” loop are very similar and you can use them interchangeably in most situations. However, the choice of which loop to use depends on personal preference and the specific requirements of the program.

Frequently Asked Questions

1. Can I Use an “Exit While” Statement to Break While Loop in Excel VBA?

Answer: No, the “Exit While” syntax is invalid for a “While” loop. But you can use the “Exit Do” statement in the case of using the “Do While” loop.

2. Can I Use “Exit For” to Break While Loop in Excel VBA?

Answer: No, you cannot use “Exit For” to break out of a while loop in Excel VBA. You can use “Exit For” to break out of a for loop, which has a different syntax than a while loop.

Key Takeaways from the Article

• A While loop in VBA will continue to execute as long as the condition specified in the While statement is True. If it doesn’t meet the condition, the loop will continue indefinitely, causing the program to freeze or crash.
• To break out of a While loop prematurely, you need to Exit the While loop.
• It’s better to structure your loop so that the condition specified in the While statement will eventually become False, and the loop will exit naturally.
• Breaking out of a loop prematurely can make your code more difficult. To debug and maintain since it can introduce unexpected behavior and make it harder to follow the logic of the program. In general, it’s better to design your code so it doesn’t rely on premature loop exits, if possible.
• There is no statement as “Exit While” to break out of the While loop.

📁 Download Practice Workbook

You can download the workbook from the link below.

Conclusion

Finally, in most scenarios, break out of a while loop in Excel VBA represents meeting a specific criterion out of the specified conditions. Doing Exit to a loop depends on the logical condition as well as on the statements. From the above methods mentioned in the article, now you know the use of Excel VBA break while loop. You can choose the one considering your criteria and situation. Don’t hesitate to comment below if you have any confusion.

Related Article

• Excel VBA Break a Loop with Keyboard Shortcut