Excel Visual Basic for Applications online training

This page has 1 thread | Add post

Finding our tutorials useful? Please consider helping us to share more!

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:

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:

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:

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:

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 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:

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

To practise nesting For Each loops:

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

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

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

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

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

Run the subroutine and check that everything appears as expected:

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

Save and close the workbook.

This page has 1 thread

Add a new post

From:	CoraCobbles
When:	04 May 22 at 00:04

Thank you for such thorough & comprehensive VBA training. I feel like I'm actually understanding it now!

I tried to do the practice for this particular lesson and the charts don't format. I'm not getting any errors so I don't know why it's not working. I'm running Excel 2019. Could that be part of this issue?

From:	Andrew G
When:	04 May 22 at 06:59

Hi Cora!

I'm not sure if the version of Excel should affect the number of chart styles available. Microsoft's documentation suggests using a number between 1 and 48 - can you try a different value and see if you notice any changes?

Happy to hear that you're finding the materials useful!

From:	CoraCobbles
When:	04 May 22 at 16:59

Hi Andrew,

I came across the same information yesterday and tried several different values but nothing seemed to work. I thought it was how I named my variable (ChartOb) but that wasn't the issue. I just tried the same exercise on a different computer running a different Excel version while using ChartOb as the variable name and it worked perfectly.

From:	Andrew G
When:	05 May 22 at 06:38

Hi Cora!

I'm genuinely surprised to hear that it works in one version of Excel but not in 2019. Thanks for taking the time to shareyour findings and if you ever get to the bottom of why it doesn't work on your own machine I'd love to hear about it!

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The Example Workbook

Looping Over Worksheets

Nesting a For Each Loop

Adding Code Within the Loop

Head office

London

Manchester