Looping in Visual Basic for Applications macros
Part four of a four-part series of blogs

There are 4 types of loop in Visual Basic for Applications - this blog shows how and when to use each type of VBA loop structure.

  1. Looping in VBA Macros - an introduction to the 4 types of loop
  2. Looping a set number of times or iterations (FOR … NEXT)
  3. Looping with conditions (DO UNTIL / WHILE ... LOOP)
  4. Looping over collections (FOR EACH ... NEXT) (this blog)

This series of blogs is part of our Excel VBA online tutorial.  We also run training courses in Excel and VBA.

Posted by Andy Brown on 15 November 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

Collection of cream cakes

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()

'a reference to each object in the collection

'that we are about to loop over

Dim cc As CreamCake

'for each of the objects in the collection of cream cakes ...

For Each cc In CreamCakes

'... eat it!

cc.Eat

'now go on automatically to the next cream cakes

Next cc

End Sub

The syntax of the FOR EACH ... NEXT loop is thus:

Dim Item As Object

For Each Item In Collection

'do something to the item

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:

  1. Loop over all of the objects in the Workbooks collection.
  2. For each such Workbook object, check it isn't the same as ThisWorkbook (the workbook containing the code you're running).
  3. 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()

'a reference to each workbook in turn

Dim wb As Workbook

'for each of the open workbooks ...

For Each wb In Workbooks

'if it isn't this one containing the code (note that you can't directly

'compare two workbooks: instead, you must see if they have the same name)

If wb.Name <> ThisWorkbook.Name Then

'it's not the same - close it down, giving user a chance to save changes

'(this is the default()

wb.Close

End If

'now go on automatically to the next workbook

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). 

Worksheets renamed Wise Owl 1 to 3

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()

'a reference to each worksheet in the active workbook

Dim ws As Worksheet

'the index number to use

Dim SheetNumber As Integer

SheetNumber = 0

For Each ws In Worksheets

'for each worksheet, rename it

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:

Message when try to delete a worksheet

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()

'a reference to each worksheet in the active workbook

Dim ws As Worksheet

For Each ws In Worksheets

'for each worksheet, suppress error messages and delete it

'(providing that it's not the active worksheet)

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:

A list of Muppets again

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:

Muppets coloured if high ranking

Our macro will select the cells shown, and leave the rows coloured blue as here.

 

Here's how to do this:

Sub ColourHighRatingMuppets()

'a reference to each cell in the muppet names column

Dim c As Range

'go to Kermit, and select from here down to the bottom of the column

Cells.Find("Kermit").Select

Range(ActiveCell, ActiveCell.End(xlDown)).Select

'now look at each muppet in turn in this block

For Each c In Selection

'if this has rating more than 5 in column D ...

If c.Offset(0, 2).Value > 5 Then

'colour entire row blue

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()

'a reference to the first Muppet

Dim TopCell As Range

'references to:

'- the range of Muppets; and

'- each individual Muppet cell

'a reference to each cell in the muppet names column

Dim MuppetRange As Range

Dim MuppetCell As Range

'first check there is a top cell to start from

Set TopCell = Cells.Find("Muppet Name")

If TopCell Is Nothing Then

MsgBox "Can not find top of column"

Exit Sub

End If

'get a reference to the column of muppets

Set MuppetRange = Range( _

TopCell.Offset(1, 0), _

TopCell.End(xlDown))

'now look at each muppet in turn in this block

For Each MuppetCell In MuppetRange

'if this has rating more than 5 in column D ...

If MuppetCell.Offset(0, 2).Value > 5 Then

'colour entire row blue

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!

 

This blog has 0 threads Add post