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.
Download Practice Workbook
Download the following practice workbook. It will help you understand the topic more clearly.
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 ]
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.
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.
- Then, the Visual Basic Editor launches and goes to the Insert tab >> Module >> Module1.
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
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”.
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
- 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.
2. Use of “Go To” Statement to Exit a 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).
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
- 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.
3. Using Flag Variable to Stop VBA For Each 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.
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
- 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.
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
- 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.
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
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.
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.
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.
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
How to Continue VBA For Loop in Excel
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:
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.
- 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 If…Then 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.
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.