How to Use the For Each Next Loop in Excel VBA

We have already covered an introduction to looping and the simplest type of loop, the For…Next Loop in a previous tutorial. Let’s just remind ourselves of what a loop is: its code that handles repetitive tasks efficiently without the use of a repetitive coding block.

We are now going to look at how to use a For Each…Next Loop. The For Each…Next Loop is also used to handle repetitive actions in a finite manner, however unlike the For…Next Loop, the programmer is not required to set the number of iterations using a counting variable. The For Each…Next Loop works on objects in a collection. The simple For Each…Next Loop has the following general syntax:

For Each Object In Collection

Statements that need to be repeated when looping

Next Object

Now let’s just remind us what the definition of an object is.

VBA is an object-orientated language, and Excel contains over 100 objects such as cells, tables, workbooks and shapes, which can be manipulated using code. Each object has properties and methods associated with it. A collection is also an object that contains a group of the same type of objects. For example, the workbook collection contains all the workbook objects, the worksheets collection contains all the worksheets in one’s workbook and cells in one’s workbook can be referred to using a range object.

So let’s look at a few simple examples of how to use the For Each…Next Loop Structure in order to work on objects in a collection.

Using the For Each…Next Loop Structure with the Range Object

We have a workbook with one worksheet, with the cells A1: A7 containing numbers. We want to loop through all the cells in the designated range using the range object and a For Each…Next Loop Structure and give each cell in the range a blue fill and the numbers a bold format. Additionally, we want to round all the numbers to the nearest integer using the VBA Round Function. The source data is shown below.

1) We would like to give the cells in the range a blue fill, give the numbers a bold format, as well as round the numbers to the nearest integer. We are going to use a For Each…Next Loop structure in order to accomplish this using the range object.

2) So, first things first go to Developer>Controls>Insert and under the ActiveX Controls section, choose Command Button.

3) Draw a Command Button on the worksheet.

4) Using the Properties Window, name the Command button, cmdforeachLoop and change the caption to Using a For Each…Next Loop.

5) Right-click the button and select View Code in order to be taken to the Visual Basic Editor.

6) Enter the following code for the button click event, in order for all the cells in the range object specified to be filled with a light blue color, the font made bold and the numbers rounded to the nearest integer.

Private Sub cmdforeachLoop_Click()

 

Dim myRng As Range

Dim cell As Range

Set myRng = Range(“A1:A7”)

 

For Each cell In myRng

cell.Interior.Color = RGB(156, 207, 212)

cell.Font.Bold = True

cell.Value = Round(cell.Value, 0)

Next cell

 

End Sub

7) Return to the worksheet and make sure Design Mode is not activated, click on the button and the cells specified in the code will now be filled with a light blue color, the font made bold and the numbers rounded to the nearest integer.

We have used the range object in conjunction with the For Each…Next Loop structure in order to ensure all the cells in the range collection are filled with blue, formatted with bold and the numbers rounded to the nearest integer. We can see that we did not have to specify a counter variable and instead accessed all the items or cells in this case in the specified range object. We also used the VBA Round Function within the loop in order to ensure that all the cells in the range would be rounded to the nearest integer. The VBA Round Function uses bankers rounding logic in order to round numbers. The syntax of the VBA Round Function is:

= Round(expression, [decimal_places])

Where expression is the number you would like to evaluate, and decimal_places is an optional value, which determines the number of places you would like to round the number to. If decimal_places is omitted or specified as 0, then the expression will be rounded to an integer. We specified 0 in our VBA code for the decimal_places value, thus rounding to an integer value took place.

Using the For Each…Next Loop Structure in conjunction with all the worksheets in the Worksheets collection

We now want to change the tab color of all the worksheets in a workbook to light orange. The worksheets collection contains all the sheets in one’s workbook, the one exception is chart sheets. When one wants to access all the sheets in one’s workbook whether they are chart sheets or worksheets one uses the sheets collection. We have a workbook containing four worksheets as shown below.

1) We would like all the worksheets in the workbook to have a light orange tab. We are going to use a For Each…Next Loop structure in order to accomplish this using the Worksheets collection object.

2) So, first things first go to Developer>Controls>Insert and under the ActiveX Controls section, choose Command Button.

3) Draw a command button on the worksheet (it can be any worksheet of your choice, in this case, Sheet4 was selected).

4) Using the Properties Window, name the Command button, cmdforeachworksheetLoop and change the caption to Using a For Each…Next Loop with the Worksheets Collection.

5) Right-click the button and select View Code in order to be taken to the Visual Basic Editor.

6) Enter the following code for the button click event, in order for all the worksheets in the workbook to get a light orange tab color:

Private Sub cmdforeachworksheetLoop_Click()

 

Dim wksh As Worksheet

 

For Each wksh In Worksheets

wksh.Tab.ThemeColor = xlThemeColorAccent2

wksh.Tab.TintAndShade = 0.599993896298105

Next wksh

 

End Sub

7) Return to the worksheet and make sure Design Mode is not activated, click on the button and all the worksheets will be given a light orange tab color as shown below.

We have accessed all the worksheets in the worksheets collection and using the For Each…Next Loop structure changed the tab color to light orange, for each worksheet in the collection. The advantage of using this particular loop structure is that if we added another three or four sheets and pressed the button again, the tab color of these sheets would also be changed to light orange.

Using a Nested For Each…Next Loop Structure with the ListObjects Collection for Tables

Excel Tables refer to structured ranges, with designated column headers and data organized in rows according to the column headers specified. Excel Tables are dynamic, which means that they grow as data is added in order to include the data added. Excel Tables can be referred to in VBA code using the standard method of referring to a range name, in this case one would use the table name. For example let’s say one had a table named ProductT, on sheet one in one’s workbook, one would refer to the data in the table in VBA, using the following code:

Worksheets(1).Range(“ProductT”)

If one wanted to refer to the header row, the totals row and all the data in the table in VBA, one would use the following code:

Worksheets(1).Range(“ProductT[#All]”)

Tables can also be referred to using the ListObjects collection. We are going to access all the tables in our workbook, on the different sheets using the ListObjects collection.

Using a Nested For Each…Next Loop structure, we will change all the tables from the default style to Table Style Medium 8. Sometimes one will need to nest loops inside other loops. One can do this with For Next…Loops, For Each…Next Loops and other types of loops quite effectively.

We have a workbook containing three different sheets, and each sheet has a table. The source data is shown below.

1)  So in order to change the styles of all the tables simultaneously, we will use a Nested For Each…Next Loop Structure.

2) So, first things first go to Developer>Controls>Insert and under the ActiveX Controls section, choose Command Button.

3) Draw a Command Button on the worksheet.

4) Using the Properties Window, name the Command button, cmdforeachtableLoop and change the caption to Using a For Each…Next Loop with the ListObjects Collection.

5) Right-click the button and select View Code in order to be taken to the Visual Basic Editor.

6) Enter the following code for the button click event, in order for all the tables in the workbook to be changed to Table Style Medium 8.

Private Sub cmdforeachtableLoop_Click()

 

Dim wksh As Worksheet

Dim tbl As ListObject

 

For Each wksh In Worksheets

For Each tbl In wksh.ListObjects

 

tbl.TableStyle = “TableStyleMedium8”

 

Next tbl

Next wksh

 

End Sub

7) Return to the worksheet and make sure Design Mode is not activated, click on the button and all the tables on each of the worksheets containing tables in the workbook are now changed to Table Medium Style 8.

Using a Nested For Each…Next Loop Structure with the ChartObjects Collection for Charts

We have a workbook, with three worksheets and there is a chart on each worksheet. The charts show the sales amounts for the different sales reps by region. Each sheet contains a chart for a different month. This type of chart is referred to as an embedded chart, this is different from a chart sheet. The difference between the two is that the parent object of a chart sheet is a workbook object whereas the embedded chart’s parent object is a ChartObject. The parent of a ChartObject is a worksheet.

We would like to fill the plot area of all the charts in our workbook with a light blue color and change the line surrounding the plot area to a blue color with a width of 2, when we click a check box. Additionally, when we click the checkbox again to clear its value, we’d like the plot area to have no fill area and no line (as it is in the default chart option). The source data is shown below.

1) So in order to change the formatting of the plot areas of all the charts in the workbook simultaneously, we will use a Nested For Each…Next Loop Structure.

2) So, first things first go to Developer>Controls>Insert and under the ActiveX Controls section, choose CheckBox.

3) Insert a CheckBox on the worksheet.

4) Using the Properties Window, name the CheckBox, chkboxOne and change the caption to Check to change the formatting of all the plot areas.

5) Right-click the Check Box and select View Code in order to be taken to the Visual Basic Editor.

6) Enter the following code for the checkbox click event, in order for all the plot areas of all the charts in the workbook to receive a light blue fill, with a blue outline of width 2 when the check box is checked and for the plot areas to have no fill and no outline when the check box is cleared.

Private Sub chkboxOne_Click()

 

Dim wksh As Worksheet

Dim chrt As ChartObject

 

If chkboxOne.Value = True Then

 

For Each wksh In Worksheets

For Each chrt In wksh.ChartObjects

chrt.Chart.PlotArea.Format.Fill.Visible = msoTrue

chrt.Chart.PlotArea.Format.Fill.ForeColor.RGB = RGB(235, 250, 255)

chrt.Chart.PlotArea.Format.Line.Visible = msoTrue

chrt.Chart.PlotArea.Format.Line.ForeColor.RGB = RGB(14, 208, 234)

chrt.Chart.PlotArea.Format.Line.Weight = 2

Next chrt

Next wksh

 

ElseIf chkboxOne.Value = False Then

 

For Each wksh In Worksheets

For Each chrt In wksh.ChartObjects

chrt.Chart.PlotArea.Format.Fill.Visible = msoFalse

chrt.Chart.PlotArea.Format.Line.Visible = msoFalse

Next chrt

Next wksh

 

End If

 

End Sub

7) Return back to the worksheet and check the checkbox in order for all the plot areas to be filled with the formatting set in the code.

8) Clearing the checkbox clears the formatting.

9) If you haven’t already done so, save the workbook as a macro-enabled workbook.

We have used a Nested For Each…Loop Structure in conjunction with an If-ElseIf statement in order to ensure that if the checkbox is checked, all the plot areas of all the charts in the workbook will receive the formatting set in the code using the Nested For Each…Loop structure to loop through all the embedded charts in the workbook. If the checkbox is cleared then using a Nested For Each…Loop Structure again, all the plot areas will be changed back to the default of no fill and no line.

And there you have it.

Download Workbook

LoopsForNext

Conclusion

The For Each…Next Loop structure allows one to manipulate objects in a collection. It is finite in the sense that there will be a set number of these objects depending on the workbook of course, but one does not need to specify the number of iterations as one would for a For…Next Loop structure.

Please feel free to tell us if you use the For Each…Next Loop structure in your VBA code.

Review Section: Test your Understanding

1) What is the general syntax of a For Each…Next Loop?

2) When does one use a For Each…Next Loop?

3) What is an object?

4) What is a collection?

5) What is a nested loop?

6) Use a For Each…Next Loop to format all the cells in a specified range with a red fill color, a white font color and make sure the font is bold and italicised.

7) Use a For Each…Next Loop to fill all the chart areas of all the charts in a workbook with a light green fill.

Useful Links

Looping in Excel Explained completely

Using the For Each Next Loop in VBA Excel Tutorial

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS) and biofuels. She enjoys showcasing the functionality of Excel in various disciplines.

In her spare time when she’s not exploring Excel or Access, she is into graphic design, amateur photography and caring for her two pets, Pretzel and Snoopy.
We will be happy to hear your thoughts

Leave a reply

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.