This post may contain affiliate links. Please read my disclosure for more info:

2.4 Loops (part 2)

Beginning VBA

Beginning VBA

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


Example 1

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.

Next ws


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


    '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.
    [insert image showing the order]


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: Coming Soon.

Leave a Reply

Your email address will not be published. Required fields are marked *