Excel VBA to Exit For Each Loop (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel VBA is a potent tool for automating routine processes. We can use loops to repeatedly carry out a particular job or iterate over a group of data, which is one of VBA’s core capabilities. The “For Each” loop is one form of a loop in VBA that lets you iterate over a group of objects, like cells in a range or items in an array. Although the “For Each” loop is pretty helpful, there are situations when you might need to break the loop early. This could be due to a certain condition being met or an error occurring in your code. However, if you don’t exit for each loop properly, it will cause unexpected results and make the VBA code run longer than necessary in Excel. This article will explain different ways for Excel VBA to exit for each loop.

That’s why we need to stop the code when our condition is met. In this article, we have tried to cover the different ways to exit a “For Each” loop in Excel VBA. Hopefully, after going through the tutorial, you will be better equipped to write efficient and effective VBA code in Excel.

Here, we attached a summarized video of our task for better visualization.


Introduction to VBA Loops in Excel

Loops are used in VBA (Visual Basic for Applications) to repeatedly perform a series of steps or operations until a certain condition is met.

Types of VBA Loops:

VBA has a variety of loop types, such as For Loops, Do Loops, and While Loops.

For Loop: Basically, when the iterative value is known to you, you can use the For Loop. The loops will contain a start value and a stop value. Then it increased by an incremental value. Whenever the condition is met, the For Loop will stop.

Do Loop: Here, the iterative value is unknown, but the condition is known when to stop the loop. There existed 2 types of Do Loops. And they are Do While and Do Until.

While Loop: While loops are similar to the Do loop. Only the start and end statements are different here.

Advantages of Loops in Excel VBA:

  • The Loops automatically do your repetitive work through lots of iterations. That’s why it saves lots of time.
  • The loops also ensure high accuracy in the code.
  • It allows easy modification of the code if any kind of error occurs.
  • Moreover, it handles a large dataset or runs the code with lots of iterations in a blink of an eye.

Basics of VBA For Each Loop:

Whenever you deal with a collection of items in an array, you may use the “For Each” loop. This loop repeats the set of items from the collection one by one. The collection indicates a table, a worksheet, or a range of cells. The “For Each” loop counts every single item in the loop.

For Each element In group
[ statements ]
Exit For 
[ statements ]
Next [ element ]

The VBA For Each loop has three important sections:

  • Item – This is a required field. It is used to iterate over the items in the collection or array.
  • Collection – This is the name of the collection or array.
  • Statements – The argument you need to execute.

Overview of Exit For Statement in Excel VBA

An Exit For statement is used when one needs to terminate the iterations. When it is executed, the code stops the iterations and jumps to the next iteration without the condition. We have attached a flow diagram of the “Exit For” statement.

Flow Chart of Exit For Statement in Excel VBA


How to Launch VBA Editor in Excel

You can launch the VBA editor by following these simple methods. Here, we use the developer tab to insert the module where we write the code. Follow the simple steps to open the VBA editor.

  • Initially, hover over to the Developer tab >> choose Visual Basic.

Launching Visual Basic Editor

  • Then, the Visual Basic Editor launches and goes to the Insert tab >> Module >> Module1.

Inserting Module


Excel VBA to Exit For Each Loop: 4 Examples

We have discussed 4 different methods to use Excel VBA to exit the For Each loop. We use different statements with which we can stop the iterations when certain conditions are met.

Not to mention, we have used the “Microsoft 365” version. You may use any other version at your convenience.


1. Using “Exit For” Statement to For Each Loop

Output of Exit For Statement in For Each Loop

If you want to exit the “For Each” loop, you can use the Exit For statement. This statement breaks the loop when a specific condition is fulfilled. In the above code, we declare a variable called myArray where we put 5 fruit names. Then, we iterate it with the For Each loop. The condition we have set here is that when the Item value is “date“, then the code will stop iterating and break code. That’s why you can see the results in the Immediate window as “apple”, ” banana”, and “cherry”.

VBA code to Exit For in For Each Loop

Here, we have attached the VBA code for your better understanding.

Sub Example()
Dim myArray As Variant
myArray = Array("apple", "banana", "cherry", "date", "elderberry")
Dim Item As Variant
For Each Item In myArray
    If Item = "date" Then
        Exit For ' exit the loop if "date" is found
    End If
    Debug.Print Item
Next Item
End Sub

Code Breakdown:

  • Here, we create an array of strings called myArray using the VBA Array function. The array contains five elements, which are the strings “apple“, “banana“, “cherry“, “date“, and “elderberry“.
  • Then, the code defines a loop using the For Each statement, which allows you to iterate over each element of the array. The loop variable Item is declared as a variant, which means it can hold any type of value.
  • Inside the loop, the code checks whether the item’s current value is equal to the string “date“. If it is, the Exit For statement is executed, which causes the loop to exit immediately. This means that any remaining elements in the array will not be processed.
  • If the current value of the Item is not “date“, the code prints the value of the Item to the Immediate window using the Debug.Print statement.

Output of Exit For Statement in For Each Loop


2. Use of “Go To” Statement to Exit a VBA For Each Loop

Output of Go To Statement to Exit VBA For Each Loop

The same thing you can do using the GoTo statement. It also helps to break the loop when the required situation is met. Here, we use a StopLoop command. When the loop exited in the GoTo statement, the StopLoop showed that the Loop Stopped in the Debug.Print statement. Moreover, the other things are almost the same as Method 1. When the Item is in the For Each loop equals “cherry“. Immediately, the loop stops, and the result is displayed in the Immediate window (see the above image).

VBA Code for Go To statement to Exit For Each Loop

Sub GoTo_Statement()
    Dim myArray(3) As String
    myArray(0) = "apple"
    myArray(1) = "banana"
    myArray(2) = "cherry"
    myArray(3) = "mango"
    For Each Item In myArray
        If Item = "cherry" Then
            GoTo StopLoop
        End If
        Debug.Print Item
    Next Item
StopLoop:
    Debug.Print "Loop stopped"
End Sub

Code Breakdown:

  • First, the code declares an array of four strings called myArray and initializes its elements using individual assignments.
  • Then, the code defines a loop using the For Each statement, which iterates over each array element. The loop variable Item is declared as a variant, meaning it can hold any value.
  • Inside the loop, the code checks whether the item’s current value is equal to the string “cherry“. If it is, the GoTo statement is executed, which jumps to the StopLoop label. This means that any remaining elements in the array will not be processed.
  • However, If the current value of the Item is not “cherry“, the code prints the value of the Item to the Immediate window using the Debug.Print statement.
  • After the loop finishes, the macro continues execution at the StopLoop label, which prints the message “Loop stopped” to the Immediate window using the Debug.Print statement.

Output of Go To Statement to Exit VBA For Each Loop


3. Using Flag Variable to Stop VBA For Each Loop

Displaying output of flag variable to Exit a loop

In this method, we have set another variable, “flag” whose initial value is “False“. When the For Each loop starts its iteration from 1 to 15, it checks for the iterative value, whether it is 6 or not. If the value is 6, then the flag variable is “True“. The loop will skip prematurely and stop the iteration. The MsgBox will show the iterative value where the loop has been executed. Moreover, the flag variable here is a boolean variable.

VBA code of flag variable in For Each Loop

Sub flag_Variable()
Dim i As Integer
Dim flag As Boolean 'taking a new flag variable
    flag = False 'putting the variable FALSE initially
    For i = 1 To 15
        If i = 6 Then
            flag = True 'the variable will be TRUE if condition is met
            Exit For
        End If
    Next i
    If flag = True Then
        MsgBox "Exited the loop occured at i = " & i
        'Showing the result in MsgBox
    Else
        MsgBox "Completed loop"
    End If
End Sub

Code Breakdown:

  • Firstly, the code declares an integer variable i and a Boolean variable flag. The flag variable is used as a flag to indicate whether the loop has encountered a certain condition.
  • The code sets the value of the flag variable to False initially, which means that the condition has not been met yet.
  • Then, the code defines a loop using the For statement, which iterates from 1 to 15.
  • Inside the loop, the code checks whether the value of i is equal to 6. If it is, the code sets the value of the flag variable to True and exits the loop using the Exit For statement. This means that the loop will terminate early if the condition is met.
  • After the loop finishes, the code checks the value of the flag variable. If it is True, the code displays a message box that indicates the loop has been exited at the value of i when the condition was met. If the value of the flag variable is still False, the code displays a message box that indicates that the loop has been completed without meeting the condition.

We have attached a video for better understanding.


4. Applying “Exit Function” or “Exit Sub” Statement to Exit VBA For Each Loop

You can also use the Exit Sub statement to stop the loop. When you use the Exit For statement, it only stops the For Each loop, but the Exit Sub statement stops the whole sub-procedure immediately after reaching the desired value in the iteration. It stops the loop as well as the procedure. Although it is not encouraged to use it, the whole code will be stopped if any error occurs in the statement.

VBA code to Exit Sub in VBA For Each Loop

Follow the VBA code that we have attached here.

Sub Exit_Sub()
    Dim myArray() As Variant
    myArray = Array(1, 2, 3, 4, 5)
    Dim i As Variant
    For Each i In myArray
        If i = 3 Then
            Exit Sub 'Exit the MySub procedure if i is 3
        End If
            Debug.Print i
    Next i
    'Code here that will not be executed if Exit Sub is triggered
End Sub

Code Breakdown:

  • The For Each loop iterates through every element in the array, assigning each one to the variable “i” and running the loop’s code each time.
  • The loop’s If statement determines whether the value of “i” equals 3. The current sub procedure (Exit Sub) is instantly terminated if the Exit Sub instruction is executed and the condition is true. The code after the Next i statement (i.e., the code here that won’t be performed if Exit Sub is triggered) won’t be executed if the Exit Sub statement is executed, since it has already exited the Exit Sub statement.

Output to Exit Sub statement to Exit VBA For Each Loop


How to Exit Diverse Types of Loops (Excluding For Each Loop) in Excel VBA

The above discussion is all about exiting the For Each loop in VBA. But when you need to stop the other loops, such as Do While and For loops, this section would be helpful for you.

To execute the Do While loop, you should use the Exit Do statement. It will exit the loop when the declared condition is fulfilled. Suppose, you have a set of arrays from which you want to stop the loop for a certain condition.


1. Exit VBA Do While Loop

Result of exiting Do while loop

For the Do While loop, we have taken the variable i from 1 to 10. If the variable is equal to 5 then the Exit Do command is executed and breaks the iterations.

VBA code to Exit Do while loop in Excel

Sub Do_While_Execution()
    Dim i As Integer
    i = 1
    Do While i <= 10
        ' print the current value of i to the Immediate window
        Debug.Print i
        ' do something here
        If i = 5 Then
            Exit Do ' stop the loop when i is 5
        End If
        i = i + 1
    Loop
    ' print a message to the Immediate window when the loop is stopped
    Debug.Print "The loop has been stopped."
End Sub

2. Exit VBA For Next Loop

Next, we used the For Loop to show its execution. The syntax is almost the same. The basic difference is that here we used the Exit For statement for the execution of the iterations.

Showing output using For Loop

Here, we have taken the variable p from 5 to 55 and printed the iterative value with the Debug.Print statement. But we want to exit the loop when the value is 10. So, we use the Exit For statement here. The loop will be executed when the value is 10. Also, we use Debug.Print statement to print the execution point and the iterative value.

We have used the following code.

VBA code to exit for loop

Sub Example_1()
 Dim p 'Variable Declared
For p = 5 To 55 'It will iterate 5 to 55
 Debug.Print p 'Print the Iterative value
 If p = 10 Then
    Exit For 'Exit the Code when condition is met
 End If
Next p
Debug.Print "Exit at " & p
End Sub

Read More: How to Use For Next Loop in Excel VBA


How to Continue VBA For Loop in Excel

Showing continuation of For loop using Go To statement

In the above image, the value 5 is not printed, as the iterative value of 5 is executed from the code.

In VBA, you cannot use the Continue For a statement, as we do in another type of coding. So, you may think the continuation may not be possible. But there is a GoTo statement that can continue the iterations except for the given condition. The code will stop just for the given condition. For example, if you use a value where the code will exit, then the code will not print that specific value. But the iterations continue, except for that certain value.

The code we have used is:

VBA code to continue For loop

Sub ExampleCode()
    Dim i As Integer
    For i = 1 To 10
        If i = 5 Then
            'Skip iteration 5 and move on to the next iteration
            GoTo NextIteration
        End If
        'Do something with the current value of i
        Debug.Print i
NextIteration:
    Next i
    'Other code to execute after the loop is finished
End Sub

Here, we take integers 1 to 10 in the variable i. We set a condition. When i=5, then the code will be executed. So, it will not print the value 5 in the Immediate Window. For the execution, we have used the GoTo statement.

Note: 

  • The current iteration of a For loop can be skipped by using the Go To command, which advances to the following iteration. To skip specific iterations based on specific conditions, it is often used in conjunction with an IfThen statement.
  • On the other side, you can skip to a certain loop iteration by using the For loop Skip To Next expression. Instead of skipping all further iterations, it is often used when you only want to skip a particular iteration based on criteria.

Frequently Asked Questions

1. How do you exit an infinite loop in Excel VBA?

The Infinite loop runs continuously without stopping the loop. If you want to break the loop, then press the CTRL + Break key. Also, you can stop the infinite loop with the Esc key.

2. When should I use an Exit For statement in Excel VBA?

Whenever your desired condition is fulfilled, and you want to stop the loop, you need to insert the Exit For statement. This will terminate your code prematurely and exit the further iterations when the condition is met.

3. Can I use the Exit For statement in a nested For loop in Excel VBA?

You can use the Exit For statement in a nested For loop, but the execution will take place in the innermost For loop of the nested loops. If you want to stop all the loops, you need to put Exit For in each loop individually.

4. Can I use the Exit For statement in a While loop in Excel VBA?

You can use the Exit For statement in the While loop. But instead of the Exit For statement, you can use the Exit While in the While loop.

5. How does the Exit For statement differ from the Exit Sub statement in Excel VBA?

The Exit For and Exit Sub statements are two different statements. The Exit For statement is used to exit a loop. When it is used, the loop jumps to the For loop and starts the iterations. On the other hand, the Exit Sub statement stops the sub-procedure or function. When it is used, the iteration stops, and the procedure ends.


Things to Remember

  • We cannot use the For Each loop in the form of a user-defined type, a user-defined type cannot use in a variant type because it operates on collections of known types. So, it cannot be used as a user-defined type.
  • Only a For Loop has the Exit For statement. You will get a build problem if you use it outside a For Loop.
  • By eliminating pointless loop iterations, the Exit For statement can improve the efficiency of your code, but it can also make it more difficult to read and comprehend. Only employ it when absolutely required.
  • In a For Loop, the Exit For expression can be combined with a Step value.
  • After utilizing the Exit For the statement, make sure your code is thoroughly tested to ensure it functions as intended.

Download Practice Workbook

Download the following practice workbook. It will help you understand the topic more clearly.


Conclusion

The above discussion indicates that, when your condition is met, and you need to break out from a loop, you need to insert the Exit For or Go To statements. These statements stop the iteration prematurely and also help if you have conducted any error in the loop. It also ensures more accuracy in the code. The exit statement breaks the code immediately when the condition is met, instead of continuing the unnecessary iterations. It also allows flexibility and better readability in your code. Hopefully, now you know the use of Excel VBA to exit for each loop. Also, understand all the methods and can apply them in your work. If you have any further queries, you can comment to us below. Thanks for reading the article patiently.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Fahim Shahriyar Dipto
Fahim Shahriyar Dipto

Fahim Shahriyar Dipto is a graduate of Mechanical Engineering at BUET. With over 1.5 years of experience at Exceldemy, he authored 70+ articles on ExcelDemy. He has expertise in designing worksheets at You’ve Got This Math. Currently, He is a Team Leader at Brainor. Dipto's passion extends to exploring various aspects of Excel. Beyond tech, he enjoys creating engaging kids' worksheets using Illustrator. A dedicated employee and innovative content developer, He incorporates a commitment to academic excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo