Looping over collections (FOR EACH ... NEXT)
As already mentioned, this type of loop structure is (for Excel at any rate) nearly always the way to go, although it is harder to use to begin with.
An Example using Cream Cakes
Consider the following (somewhat greedy) example:
Suppose that given this collection of creamcakes, we want to eat every single one.
We want to write a program in VBA to eat every cream cake. We know there is a collection of CreamCakes, and we know that we want to apply the Eat method to each of the objects in the collection. Here is how to do this:
Sub EatAllCakes()
Dim cc As CreamCake
For Each cc In CreamCakes
cc.Eat
Next cc
End Sub
The syntax of the FOR EACH ... NEXT loop is thus:
Dim Item As Object
For Each Item In Collection
Next Item
Useful Collections to Loop Over
In Excel, the three most useful collections to loop over are:
Collection | What it contains |
|---|
Workbooks | All of the files that you currently have open in Excel |
Worksheets | All of the worksheets in the current workbook |
Range of cells | Any range of cells |
However, there are many other collections in Excel, including PivotTables, Charts, Shapes and many more besides.
The great thing about VBA is this: once you've learnt the principles of looping over collections, you can write code to count paragraphs in Word, colour slides in PowerPoint or close forms in Access - the structure is always the same!
An Example of Looping over Workbooks
Suppose that you want to close down all of the workbooks that a user has open apart from the one containing the running code, but you want to give your user in each case the change to save any changes they've made. You could do this as follows:
Loop over all of the objects in the Workbooks collection.
For each such Workbook object, check it isn't the same as ThisWorkbook (the workbook containing the code you're running).
If it isn't the same, close it down; otherwise, ignore it.
Here's the code to do this - admire it's simplicity, elegance and conciseness!
Sub CloseAllButCodeWorkbooks()
Dim wb As Workbook
For Each wb In Workbooks
If wb.Name <> ThisWorkbook.Name Then
wb.Close
End If
Next wb
End Sub
Instead of using ThisWorkbook (which refers to the workbook containing the current running code), you could also have used ActiveWorkbook (the current workbook). These are not necessarily the same.
An Example of Looping Over Worksheets
Imagine that (somewhat egotistically) you decide to name all of your worksheets in a workbook after your company name (for us it's Wise Owl).
The results of running our macro - worksheets with different names!
Here's how to rename all of the worksheets in a workbook like this:
Sub RenameWorksheets()
Dim ws As Worksheet
Dim SheetNumber As Integer
SheetNumber = 0
For Each ws In Worksheets
SheetNumber = SheetNumber + 1
ws.Name = "Wise Owl " & SheetNumber
Next ws
End Sub
Note that the above code works because it never tries to create 2 worksheets with the same name.
Another Worksheet Example - Deleting All but One Worksheet
A common requirement is to delete all of the other worksheets in a workbook apart from the current one (whatever this may be called). When you do this, you will normally get the following message appearing:
The default message which appears when you try to delete a worksheet
To avoid this appearing, you can turn the DisplayAlerts property of Excel off temporarily:
Sub DeleteAllButCurrentSheet()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> ActiveSheet.Name Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Next ws
End Sub
The above macro is dangerous: you'll lose all of the other worksheets in your wokbook!
An Example - Looping Over Cells
By far the most common requirement in looping over collections in Excel is to do something to every single cell. For example, suppose that you wanted to colour our high-rating muppets again, but this time using FOR EACH ... NEXT:
We want to write a macro to colour all of the Muppets scoring more than 5
To do this we want to loop over the collection of cells from Kermit (shown shaded in green above) to the bottom of the column. We could refer to this block of cells as B5:B11, but it would be better to make our macro work even if we added more muppets. To do this, our macro will select the range shown below:
Our macro will select the cells shown, and leave the rows coloured blue as here.
Here's how to do this:
Sub ColourHighRatingMuppets()
Dim c As Range
Cells.Find("Kermit").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
For Each c In Selection
If c.Offset(0, 2).Value > 5 Then
Range(c, c.End(xlToRight)).Interior.ColorIndex = 20
End If
Next c
End Sub
The big surprise here is that when you loop over a collection of cells, you refer to each cell in the collection as a Range object.
There is no such thing in Excel as a Cell object! Any range of cells is a collection of individual single-cell Range objects.
The perfect macro using object variables
The perfect macro (!) for the above problem would use object variables (that is, variables which refer to objects built into Excel, rather than numbers, text or dates):
Sub ColourHighRatingMuppets()
Dim TopCell As Range
Dim MuppetRange As Range
Dim MuppetCell As Range
Set TopCell = Cells.Find("Muppet Name")
If TopCell Is Nothing Then
MsgBox "Can not find top of column"
Exit Sub
End If
Set MuppetRange = Range( _
TopCell.Offset(1, 0), _
TopCell.End(xlDown))
For Each MuppetCell In MuppetRange
If MuppetCell.Offset(0, 2).Value > 5 Then
Range(MuppetCell, MuppetCell.End(xlToRight)).Interior.ColorIndex = 20
End If
Next MuppetCell
End Sub
Question: where is the active cell after you run the above macro?
Answer: where it started (it doesn't move).
Now that you've learnt looping over collections, you can consider yourself a serious VBA programmer!
Possible further training
Ideas for where to go now:
Course | Notes |
|---|
Introduction to VBA | A two-day course showing how to program in VBA in Excel. |
Advanced VBA | The same course, but again delivered in a classroom in the UK. |
You can see all of these courses at our Visual Basic for Applications training home page.
