VBA For Each Loop

Excel VBA For Each Loop

“Each” keyword is used in VBA along with “For” function. It signifies that for each entity in an array or the range repeat the process in for loop. Basically, we won’t input the step range i.e. from 1 to 5 or 1 to 10, instead we just put it as shown in the syntax below.

Syntax:

For Each object-name In Range

If you do not see the “Developer” tab in excel, refer the below steps.

Step 1: Click on File Option.

Step 2: Drop-down appears, Click on Options tab.

Step 3: Once you click on “Options”, a dialog box appears as shown below and click on the Customize Ribbon option.

Step 4: When we drag down in the customize ribbon options we will find an option for Developer we need to check that box which will enable us to use VBA in excel.

How to Use For Each Loop in VBA?

Below are the different examples to use For Each Loop in Excel using VBA code.

Excel VBA For Each Loop – Example #1

We shall take a simple example of For Each Loop in VBA. Assume that below is the set of data in VBA.

Follow the below steps to use For Each Loop in Excel VBA.

Step 1: Click on “Developer” tab and then click on the “Visual Basic” option on the left-hand side (first option) as shown below.

Once you click on it, a new window appears as shown below.

You will notice that it contains two tabs on the left-hand side, “Sheet1(Sheet1)” and “ThisWorkbook”. The only difference is “Sheet1” when you want to execute code for this particular sheet only and in “ThisWorkbook”, we can write a code and execute which can be applied for the whole excel workbook.

Step 2: Double click on “Sheet1(Sheet1) and you will see a blank screen where we need to write the code. Give a name to the subset created. Here we have given name as “Sub For_Each_Ex1()” as shown in the below screenshot.

Code:

Sub For_Each_Ex1()

End Sub

This is the way we create a subset and automatically “End Sub” appears. This is by default feature of excel. If “End Sub” does not appear the code will not execute and throw an error.

Step 3: We shall declare the variables as a range.

Code:

Sub For_Each_Ex1()

Dim Earn, Range1 As Range

End Sub

Here we have declared two variables, “Earn” which we will use with For Each and “Range1” as the range of the data set.

Step 4: Now, we shall fix a range i.e. range of data which we need to work upon by using the keyword “Set” as shown below.

Code:

Sub For_Each_Ex1()

Dim Earn, Range1 As Range
Set Range1 = Range("A1:A10")

End Sub

Here we fixed “Range1” as from range cells A1 to A10, which is our data set.

Step 5: We shall now execute For loop using Each keyword.

Code:

Sub For_Each_Ex1()

Dim Earn, Range1 As Range
Set Range1 = Range("A1:A10")
For Each Earn In Range1

End Sub

Here, for each value in Range, Earn will get a value assigned as For loop gets executed step by step.

Step 6: Now, we shall use “Msgbox” keyword to display each line entry in the range assigned.

Code:

Sub For_Each_Ex1()

Dim Earn, Range1 As Range
Set Range1 = Range("A1:A10")
For Each Earn In Range1
MsgBox Earn.Value

End Sub

Here “Earn.value” will display the value in the cell i.e. starting from the first cell in the range.

Step 7: Now, we want to execute the next value in the cell range given, hence, we have to use “Next” keyword in the code. This will help to proceed further in the range and display each entity in cell range one by one.

Code:

Sub For_Each_Ex1()

Dim Earn, Range1 As Range
Set Range1 = Range("A1:A10")
For Each Earn In Range1
MsgBox Earn.Value
Next Earn

End Sub

Step 8: Run the code by hitting F5 or Run button and see the output.

The moment the “Next Earn” code line is executed, “Earn.value” will be assigned with next cell value in the range. One by one click on “OK” and will keep displaying each value in the range until the range of values is finished.

Excel VBA For Each Loop – Example #2

We shall take another example in which we shall display the name of each sheet in an excel file. Let’s assume we have sheets named as below in excel file:

As we notice that there are around 5 sheets in the excel file and we need to display the name of each one by one.

We will write and execute the code in “ThisWorkbook” section as highlighted in the red box below:

Double click on it and while window pane appears and start writing the code in it. Follow the below steps to use For Each Loop in Excel VBA:

Step 1: We shall writing the code by assigning a subset name as shown below.

Code:

Sub pagename()

End Sub

Step 2: Also, we can directly start writing the “For” loop statement without declaring the variable as shown below.

Code:

Sub pagename()

For Each sht In Application.Sheets

End Sub

Here “sht” is our variable1 which by default gets declared and “Application.sheets” signifies that in the file consider all the sheets. Application.fucntion specifies that we can add any feature or function or excel tool to be considered while executing the code.

Step 3: Now we shall enter the message box syntax and along with it we shall add syntax to display sheet name as shown below:

Code:

Sub pagename()

For Each sht In Application.Sheets
MsgBox "The sheet name is:" & sht.Name

End Sub

Here the statement which we would like to display is “The sheet name is:” and then we type an ampersand(&) which concatenates the code line and then we type “sht.Name” i.e. it will select the sheet starting from the first sheet and display the name.

Step 4: Now, we need to repeat the process for displaying each sheet name. Hence, we would use “Next sht” as shown below:

Code:

Sub pagename()

For Each sht In Application.Sheets
MsgBox "The sheet name is:" & sht.Name
Next sht

End Sub

“Next sht” will let the system know that, consider next sheet and display its name for further execution.

Step 6: Run the code by hitting F5 or Run button and see the output.

Once, you click on “OK” and the code executes further, it will display the next sheet name. The code will execute until there are no further sheets on display.

Excel VBA For Each Loop – Example #3

We shall now take an example where we have to sum all the values in a given set of data or an array. Assume below is the set of data:.

Follow the below steps to use For Each Loop in Excel VBA:

Step 1: First declare the subset name as “eachadd()” as shown below.

Code:

Sub eachadd()

End Sub

Step 2: Now, we shall declare the variables needed, one as integer named “total” in which we keep summing up the cell values one by one and fix a range of cells which contains values we need to add.

Code:

Sub eachadd()

Dim total As Integer
Dim Range1 As Range

End Sub

Step 3: After, declaring variable and range, we need to fix the range to be used for loop. We shall fix this range from A1 to A10 cells using the keyword “Set” as shown below:

Code:

Sub eachadd()

Dim total As Integer
Dim Range1 As Range
Set Range1 = Range("A1:A10")

End Sub

Step 4: We can now directly start using the For loop function and directly declare the variable. Here, we are using “add1” as variable and it will be by default declared by the system.

Code:

Sub eachadd()

Dim total As Integer
Dim Range1 As Range
Set Range1 = Range("A1:A10")
For Each add1 In Range1

End Sub

Step 5: Adding each entity in the range one by one and into the variable declared “total”. By default, the value of “total” initially is zero. And we need to add one by one value of cells and again that sum has to add in “total” again.

Code:

Sub eachadd()

Dim total As Integer
Dim Range1 As Range
Set Range1 = Range("A1:A10")
For Each add1 In Range1
total = total + add1.Value

End Sub

The above statement is executed from right to left i.e. “add1.value” is the first cell value and add it to “total” which is zero. Hence, the sum would be 0 + 1 which is 1. And that sum as 1 would go in “total” before “=” sign. This is an advantage as formula executes from left to right.

Step 6: Now add “Next add1” which will now help to consider the next cell value of the data range and add that value in the “total” variable as shown below.

Code:

Sub eachadd()

Dim total As Integer
Dim Range1 As Range
Set Range1 = Range("A1:A10")
For Each add1 In Range1
total = total + add1.Value
Next add1

End Sub

Step 6: Press “F8” to run the code by each line. Also, we can see the value of “total” in the screenshot below.

Similarly, until the range of cells gets over, for loop will keep on executing this sum of an array.

Step 7: Now, we will use “Msgbox” keyword.

Code:

Sub eachadd()

Dim total As Integer
Dim Range1 As Range
Set Range1 = Range("A1:A10")
For Each add1 In Range1
total = total + add1.Value
Next add1
MsgBox "Final Summation:" & total

End Sub

Step 8: Run the code by hitting F5 or Run button and see the output.

Things to Remember

• “Each” keyword is only used most of the times in case of use of VBA For Loop function only. Else, it has limited usage in excel VBA.
• The advantage for “Each” keyword is only that we don’t have declare steps like from 1 to x in the “For” statement.

Recommended Articles

This is a guide to VBA For Each Loop. Here we discuss how to use For Each Loop in Excel using VBA code along with practical examples and downloadable excel template. Below are some useful excel articles related to VBA –

1. VBA Do While Loop
2. Switching Columns in Excel
3. VBA Break for Loop
4. Count Names in Excel
5. How to Run the VBA Code?