Excel Visual Basic for Applications online training

This page has 0 threads | 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.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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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

Run the subroutine and check the results in Excel:

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:

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

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

Sub FormatAllCharts()

Dim ch As Chart

DeleteAllCharts

CreateGroupCharts

End Sub

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

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

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

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

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The Example Workbook

Creating a Basic For Each Loop

Adding Code to the Loop

Head office

London

Manchester