4 Examples to Understand VBA For Each Loop [Cells, Arrays]
The For..Each is a loop type in VBA. This loop is used to execute one or more statements for arrays or collections
What is For Each loop in VBA
- The For..Each is a loop type in VBA
- This loop is used to execute one or more statements for arrays or collections
- For each element of the collection/array, the statements inside the loop execute
- The collection example can be a range of cells in Excel
An example of For Each with cell range
Let us start our example with the range of cells in the For Each loop. First, have a look at our sample sheet that we will use for our example:
We will create a range of six cells (B2 to B7) and use them in the For Each.
Inside the For Each, we will get the value of each cell and concatenate the values in a String variable.
Finally, we will display a MsgBox with the values of all cells.
VBA code with For Each loop
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Sub each_ex() Dim SheetRange As Range Dim cell Dim concat_str As String 'Our collection to be used in For Each Set SheetRange = Range("B2:B7") For Each cell In SheetRange concat_str = concat_str & cell & ", " Next cell 'Display concatenated cells MsgBox concat_str End Sub |
Output:
Syntax of For Each loop
So, based on our above example, you can notice the syntax of using For..Each .. Next loop:
|
1 2 3 4 5 6 7 8 9 |
For Each element In group [ statements ] [ Exit For ] [ statements ] Next [ element ] |
An example of using For Each with an array
For this example, we have created an array of programming languages. In the For..Each loop, that iterates through each element of the array, we will display a MsgBox with the current array element value.
This should make your For Each concept clearer as the MsgBox statement is written once and you can see it executes for each item separately i.e. message box appears four times as you execute this code:
VBA code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Sub each_ex() Dim Langs Dim item 'Creating an array of four elements Langs = Array("C#", "Java", "C++", "Python") 'Loop will display Message Box four time as we have four array elements For Each item In Langs MsgBox item Next item End Sub |
Iterate through an array partially
Rather than iterating through each element of the array as using For Each loop, you may go through the array partially.
For that, you may use the If statement and check the element value and exit the for loop if the condition is met.
The example below shows how the If condition is used in our array:
The code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Sub each_ex() Dim Langs(1 To 4) As String Dim concat_str As String Dim curr_item As Variant 'Array Elements Langs(1) = "C#" Langs(2) = "Java" Langs(3) = "C++" Langs(4) = "Python" 'Using the For Next loop and exit as current language is “C++” For Each curr_item In Langs If curr_item = "C++" Then Exit For End If concat_str = concat_str & curr_item & ", " Next curr_item MsgBox concat_str End Sub |
Output:
You can see, only two languages are displayed.
How to omit only the current iteration solution
Though the continue statement is not available in VBA, as in other programming languages. We can omit the current element (without executing any code) by using another way (which is the purpose of continue).
So, again consider our array that has four languages.
In the example below, we will omit “C++” and display the other three languages (elements).
VBA code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
Sub each_ex() Dim Langs(1 To 4) As String Dim concat_str As String Dim curr_item As Variant 'Array Elements Langs(1) = "C#" Langs(2) = "Java" Langs(3) = "C++" Langs(4) = "Python" 'Omit loop only when condition is True For Each curr_item In Langs If curr_item = "C++" Then 'Do Nothing Else concat_str = concat_str & curr_item & ", " End If Next curr_item MsgBox concat_str End Sub |
Result:
You can see, though C++ is our third element, we did not display it in the message box.
