Module 6 - Collections and Loops
Lesson 6.3 - For Each Loops
Topic 6.3.1 - Basic For Each Loops

In an earlier lesson you saw how to use a For Next loop to count through the items of a collection using the index number of each object.  A more convenient and elegant way to loop through a collection is to use a For Each loop, as this part of the lesson will show you.

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 collection of eight worksheets:

Example worksheet

Each worksheet contains a table of results from teams in a single group at the 2018 FIFA World Cup.

 

We'd like to create a separate chart for each worksheet and we'll use a For Each loop to do so.

Creating a Basic For Each Loop

A For Each loop requires a variable which can hold a reference to a single object.  The type of object variable you declare will depend on the collection you intend to loop over.  In our example, we're looping over the Worksheets collection and each object in that collection is a Worksheet object.  Open the VBE and find Module1.  Create a new subroutine called CreateGroupCharts and declare a Worksheet variable:

Declare variable

It's conventional to use a variable name which is an abbreviation of the object type.

 

The variable will be used to hold a reference to each object in the collection, one-by-one, as the loop is processed.  To begin the loop, you use the For Each statement, followed by the name of the variable, as shown below:

For Each

Write For Each, followed by the name of the variable.

 

On the same line you must then state which collection you are looping over by writing In followed by the name of the collection:

Collection

In this example, the collection name is Worksheets.

 

You may find it useful to refer to the object which contains the collection you are looping over:

Container name

Adding ThisWorkbook before the collection name ensures that the loop will process the worksheets in the same workbook that the code is stored in, rather than whichever workbook happens to be active when the code is executed.

 

To finish the loop you write Next and, optionally, the name of the variable:

Next

The name of the variable is optional in the Next statement.

 

The loop will now process the collection of worksheets, setting a reference to each worksheet in the ws variable, one-by-one until there are no more worksheets to process.  Try adding some basic sample code to the loop to prove this:

Debug

Add a Debug.Print statement to write the name of the worksheet referenced by the variable to the Immediate window.

 

Notice that you don't write an explicit Set statement to assign a reference to the ws variable.  The For Each loop does this automatically.

Run the code and check the results in the Immediate window.  If you can't see this window, choose View | Immediate Window from the menu:

Sheet names

The worksheet names will appear in the Immediate window.

 

You can now delete the Debug.Print statement from the code.

Adding Code to the Loop

We can now add code inside the loop to perform the task of creating a chart for each worksheet.  Start by declaring a variable to hold a reference to a Chart object:

New variable

The new variable will help when it comes to reference the chart later.

 

Add an instruction within the For Each loop which creates a new chart and returns a reference to the chart that was created to the ch variable:

New chart

Insert the new chart as shown here.

 

Add an instruction which moves the new chart after the worksheet that is referenced by the ws variable:

Move chart

Use the Move method to change the position of the chart.

 

Add an instruction to change the chart's name to indicate which worksheet's data it is displaying:

Chart name

Concatenate the name of the worksheet referenced by the ws variable with the text " Chart" to create the chart's name.

 

Add an instruction to set the source data for the chart by referencing the relevant range of cells on the worksheet referenced by the ws variable:

Source data

Refer to the CurrentRegion property of cell A1 on the relevant worksheet.

 

Run the subroutine and check the results in Excel:

New charts

You should find a chart next to each worksheet which displays the data for that sheet.

If you choose to run the subroutine again, make sure that you have deleted the existing charts to avoid a naming conflict.  You can run the DeleteAllCharts subroutine in Module1 to do this quickly.

To practise writing a basic For Each loop:

  1. Create a new subroutine called FormatAllCharts and declare a variable which can hold a reference to a Chart object:

Sub FormatAllCharts()

 

Dim ch As Chart

 

End Sub

  1. Add instructions to call the DeleteAllCharts subroutine followed by the CreateGroupCharts subroutine:

Sub FormatAllCharts()

 

Dim ch As Chart

 

DeleteAllCharts

CreateGroupCharts

 

End Sub

  1. Add a For Each loop to process every chart in the workbook:

Sub FormatAllCharts()

 

Dim ch As Chart

 

DeleteAllCharts

CreateGroupCharts

 

For Each ch In ThisWorkbook.Charts

 

Next ch

 

End Sub

  1. Add instructions within the loop to change the chart type and chart style:

Sub FormatAllCharts()

 

Dim ch As Chart

 

DeleteAllCharts

CreateGroupCharts

 

For Each ch In ThisWorkbook.Charts

 

ch.ChartType = xlBarStacked

ch.ChartStyle = 23

 

Next ch

 

End Sub

  1. Run the subroutine and check that each chart has been changed:
Charts changed

Each chart should have changed from the original type and style.

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