Previous Part: 2.3 Object variables
Understanding the For Each Loop
Having looked at object Variables in the last part, we will now apply that knowledge to understanding another type of loop, the For Each loop.
Back in Loops (part 1) we said: “A loop will perform a certain action over and over again. Each time the action occurs something in the loop changes, so that the action varies slightly each time.”
The For Each loop works specifically for groups or collections of objects. It will perform a specific action for each object within a collection. Examples would be:
- Loop through each worksheet in a workbook
- Loop through each chart on a worksheet
- Loop through each cell within a selected range
- Loop through all the shapes on a worksheet
- Loop through all the Pivot Tables in a worksheet
When we learned the basic For loop, we created a counter to perform an action a specific number of times. When using the For Each loop the action will be performed for each object in the collection. We do not need to know the number or names of the individual objects. Excel will handle all of that.
The Basic layout
The basic syntax for a For Each loop is as follows:
Dim [variableName] as [Variable type] For Each [variableName] In [group or collection] 'Insert code 'Use Exit For to leave the loop early 'Insert code Next [object]
To better understand how the loop works we should consider a few examples
The following example will loop through each worksheet within the workbook and display the name of the worksheet within a message box.
Sub ForEachExample1() Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets MsgBox ws.Name Next ws End Sub
The loop starts with a variable statement. This code creates an object variable called ‘ws’, which can only contain worksheets.
Dim ws As Worksheet
The next line of code creates the For Each loop. The loop will run for every worksheet (“ws”) which exists within the active workbook.
For Each ws In ActiveWorkbook.Sheets
Various lines of code are implemented before we get to the line which closes the Loop.
Example 2 – Nested Loops
It is possible to insert a loop inside another loop, look at the example below. The first loop will go through each worksheet in the active workbook. As each worksheet is selected, another loop fires which will go through each cell in the Selection before moving to the next worksheet.
Sub ForEachExample2() Dim ws As Worksheet 'Start of first loop For Each ws In ActiveWorkbook.Sheets ws.Select 'Start of 2nd loop For Each c In Selection MsgBox c.Address Next c Next ws End Sub
In which order is the collection/group looped?
The order of the collection or group is the order in which the For Each loop runs. This is not alphabetical, but based on an Index reference. Examples are:
- Worksheets – are ordered in the order of the tabs of the workbook. The first workbook will always be looped through first.
- Cells – are ordered left-to-right then top to bottom.
A few notes
I have a few notes to help your understanding.
- The For Each loop can only work where the object and the group or collection contain the same things (i.e., it will not let you loop through all the worksheets within a selected range, as ranges do not have worksheets).
- The loop does not select any of the objects. If you wish to select the object you will need to include that in your code (just like Example 2 above).
- Typically the loop will run through all the items in the group/collection. By including Exit For between the start and end, the loop will stop running. The code picks up again directly after the end of the loop.
Previous Part: 2.3 Object variables
Next Part: 2.5 Using logic