Module 6 - Collections and Loops
Lesson 6.3 - For Each Loops
Topic 6.3.3 - Nesting For Each Loops

In this module we've dealt with the idea of objects belonging to collections and seen that we can look at each object in a collection using a For Each loop.  Each object in a collection can also be a container for other collections, for example, each Workbook object in the Workbooks collection contains a Worksheets collection; each Worksheet object in that collection can contain collections of ChartObjects, ListObjects and Range objects, etc.  You can use nested For Each loops to process these nested collections, as this part of the lesson demonstrates.

Files Needed

You can click here to download the file for this part of the lesson.

Completed Code

You can click here to download a file containing the sample code.

The Example Workbook

Extract and open the workbook linked to in the Files Needed section above.  You'll find a workbook containing a eight worksheets, each of which contains multiple tables, charts and cells:

Example

Each sheet contains data on the groups at the 2018 FIFA World Cup.

We'd like to loop over the rows in the first table on each sheet and calculate the points scored by each team.  We can do this using a function called TotalPoints which you'll find in Module1:

Function

The function awards 3 points for a win and 1 point for a draw.

Looping Over Worksheets

The main loop in this example needs to process each worksheet in the workbook.  Create a new subroutine and configure a basic For Each loop to process the Worksheets collection:

Basic loop

Create the outline of the For Each loop as shown here.

 

Nesting a For Each Loop

In each worksheet, we can loop over the range of cells from A2 to A5 to process each team in the group.  Add a Range variable to the subroutine and then insert a second For Each loop within the first:

Nested loop

Add a second For Each loop inside the first.

 

Notice that we precede the reference to Range("A2:A5") with a reference to the Worksheet variable.  Without this, the code will refer to cells A2:A5 on the active worksheet, rather than the one that is reference by the ws variable.

Adding Code Within the Loop

To complete this example we can call the TotalPoints function for each cell on each worksheet that we are looping over:

Call function

Call the function to set the value of the cell four columns to the right of the cell referenced by the r variable.

 

Run the subroutine and check that the results are as expected in Excel:

Results

Check that each worksheet has points calculated in column E of the first table.

To practise nesting For Each loops:

  1. Create a new subroutine called FormatGroups and configure a For Each loop to process each worksheet in the workbook:

Sub FormatGroups()

 

Dim ws As Worksheet

 

For Each ws In ThisWorkbook.Worksheets

 

Next ws

 

End Sub

  1. Declare a ListObject variable and add a second For Each loop within the first to process the collection of ListObjects on each worksheet that you are looping over:

Dim ws As Worksheet

Dim lo As ListObject

 

For Each ws In ThisWorkbook.Worksheets

 

For Each lo In ws.ListObjects

 

Next lo

 

Next ws

  1. Add an instruction within the second loop to alter the formatting of the table referenced by the lo variable:

For Each ws In ThisWorkbook.Worksheets

 

For Each lo In ws.ListObjects

 

lo.TableStyle = "TableStyleDark7"

 

Next lo

 

Next ws

  1. Declare a ChartObject variable and add another For Each loop within the worksheets loop to process the collection of ChartObjects on each sheet:

Dim ws As Worksheet

Dim lo As ListObject

Dim co As ChartObject

 

For Each ws In ThisWorkbook.Worksheets

 

For Each lo In ws.ListObjects

lo.TableStyle = "TableStyleDark7"

Next lo

 

For Each co In ws.ChartObjects

 

Next co

 

Next ws

  1. Add an instruction within the new loop to alter the formatting of each chart:

For Each ws In ThisWorkbook.Worksheets

 

For Each lo In ws.ListObjects

lo.TableStyle = "TableStyleDark7"

Next lo

 

For Each co In ws.ChartObjects

co.Chart.ChartStyle = 32

Next co

 

Next ws

  1. Run the subroutine and check that everything appears as expected:
Result

Each table and chart on each worksheet should have received a new format.

  1. Save and close the workbook.
This page has 0 threads Add post