Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
547 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
You can click here to download the file for this part of the lesson.
You can click here to download a file containing the sample code.
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.
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.
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:
Sub FormatAllCharts()
Dim ch As Chart
End Sub
Sub FormatAllCharts()
Dim ch As Chart
DeleteAllCharts
CreateGroupCharts
End Sub
Sub FormatAllCharts()
Dim ch As Chart
DeleteAllCharts
CreateGroupCharts
For Each ch In ThisWorkbook.Charts
Next ch
End Sub
Sub FormatAllCharts()
Dim ch As Chart
DeleteAllCharts
CreateGroupCharts
For Each ch In ThisWorkbook.Charts
ch.ChartType = xlBarStacked
ch.ChartStyle = 23
Next ch
End Sub
Each chart should have changed from the original type and style.
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.