Programming Excel with VBA and .NET : Loops

The other fundamental part of programming logic is the ability to repeat a set of actions until some condition is met. This type of logic is called looping .

Table 1. Visual Basic statements for repeating actions

Statement	Use to
Do...Loop or While...Wend	Repeat a set of actions until a condition is met
For...Next	Repeat a set of actions a number of times using a counter
For Each	Repeat as set of actions for each item in a collection

Do...Loop and While...Wend are similar, but Do...Loop is more flexible so most programmers simply ignore While...Wend. I'm going to follow their lead—if you're interested, you can read about While...Wend in Help.

The Do...Loop statement repeats a set of actions until a condition is met; you can include the condition at the beginning or the end of the loop. For example, the following code speaks any words you type in an input box and exits if you don't type anything:

    Sub RepeatAfterMe( )
        Dim str As String
        Do
            str = InputBox("Enter something to say.", "Repeat after me...")
            Application.Speech.Speak str
        Loop While str <> ""
    End Sub

The preceding code executes the Speak method one time more than it really needs to —after the user cancels the input. This isn't a big problem, but you can avoid it by using an Exit Do statement instead of testing the condition at the end of the loop (change shown in bold):

    Sub RepeatAfterMe( )
        Dim str As String
        Do
            str = InputBox("Enter something to say.", "Repeat after me...")
            If str = "" Then Exit Do
            Application.Speech.Speak str
        Loop
    End Sub

It's not good style to use Exit Do within long, complicated loops. In those situations, it's important to be able to easily locate the exit condition, and burying it within the body of the loop makes it harder to find. If the unneeded Speak method still bothers you, just change the loop as shown here:

    Sub RepeatAfterMe( )
        Dim str As String
        Do
            str = InputBox("Enter something to say.", "Repeat after me...")
            If str <> "" Then _
              Application.Speech.Speak str
        Loop While str <> ""
    End Sub

Now the loop executes no unneeded statements. There's one last step in this example: the Speech object was introduced in Excel 2002. If you want the preceding code to work with previous versions of Excel, you need to add some conditional logic as shown here:

    Sub RepeatAfterMe( )
        Dim str As String
        Do
            str = InputBox("Enter something to say.", "Repeat after me...")
            ' Speech was added in Excel 10
            If Application.VERSION >= 10 And str <> "" Then
                Application.Speech.Speak str
            ' This is boring, but it works with all versions!
            ElseIf str <> "" Then
                MsgBox str, , "Repeat after me."
            End If
        Loop While str <> ""
    End Sub

This example shows how loops and conditional statements are often used together. Together they form the logical path that your program follows.

Placing the condition first in a Do...Loop prevents the loop from running unless the condition is true to start with. You can choose between While and Until when testing a condition; they are inverses of each other so While str <> "" is the same as Until str = "". The basic form of a Do...Loop is as follows:

    Do [While condition | Until condition]
        ' action
        [If condition Then Exit Do]
    Loop [While condition | Until condition]

For...Next statements perform an action a set number of times as determined by a variable used as a counter. In many situations, you know how many times you want to repeat an action, but the most common is probably when working with lists or tables of items from an array. In that case, you know the start point (the lower bound of the array) and the end point (the upper bound of the array), as shown by this code from an earlier sample:

    ' Display the items in the Flavors array
    Dim i As Integer
    For i = 0 To UBound(Flavors) - 1
        Debug.Print Flavors(i)
    Next

By default, For...Next increments the counter (i) by one each time the loop executes. Thus, Flavors(i) gets each element of the array from 0 to one less than the upper bound. That last bit might seem a little odd, but UBound returns the number of elements in the array, not the maximum index of the array; in this case, the array starts at 0, so the maximum index is one less than the upper bound. If the lower bound is 1 (as it is for arrays returned by Excel methods), then the For...Next loop looks like this:

    Sub ForNextLoop( )
        If Not IsArray(Selection.Value) Then Exit Sub
        Dim i As Integer, j As Integer, str As String
        For i = 1 To UBound(Selection.Value, 1)
            str = ""
            For j = 1 To UBound(Selection.Value, 2)
                str = str & vbTab & Selection(i, j)
            Next
            Debug.Print str
        Next
    End Sub

In the preceding code, Selection.Value returns an array if more than one cell is selected. Since Excel arrays start at 1, the count of elements in the array is the same as the array's upper bound.

You can change the increment used by For...Next by using the Step keyword as shown by the following general version of the For...Next statement:

    For counter = start To stop [Step increment]
        ' action
        [If condition Then Exit For]
    Next [counter]

Use a negative number for increment to count backward. For example, the following code performs a 10-second countdown:

    Sub CountDown( )
        Dim i As Integer
        For i = 10 To 1 Step -1
            Debug.Print i
            Application.Wait Now + #12:00:01 AM#
        Next
        Debug.Print "Blast off!"
    End Sub

In some cases, it is more convenient to perform an action on each item in a group, rather than relying on the number of items in the group. For those situations, Visual Basic provides the For Each statement. The following code displays a list of the workbook's worksheets in the Immediate window:

    Dim ws As Worksheet
    For Each ws In Worksheets
        Debug.Print ws.Name
    Next

For Each works only with collections , which are a special type of object that contains a group of other objects. All collections have a special, hidden property called an enumerator that allows them to work with For Each. Collections also usually have an Add method and a Count property and are usually named using the plural form of the name of the object they contain. Thus the Worksheets collection contains Worksheet objects.

Excel doesn't always follow these rules for collections. That's why I say usually here.

For Each sets the object variable to the next object in the collection each time the loop executes and automatically ends after it reaches the last object. There's no Step keyword to skip objects or count backward; the general form of the statement looks like this:

    For Each object In collection
        ' action
        [If condition Then Exit For]
    Next [object]

The type of object must be the same as the type of objects in collection. Some collections, such as the collection returned by the Excel Sheets method, can contain objects of more than one type. Therefore, the following code would cause a type-mismatch error if a workbook contains a Chart sheet:

     Sub ForEachLoop( )
        Dim ws As Worksheet
        For Each ws In Sheets ' Possible error!
            Debug.Print ws.Name
        Next
    End Sub

If you want to work with a mixed collection like Sheets, use a generic object as shown here:

    Sub ForEachLoop( )
        Dim obj As Object
        For Each obj In Sheets
            Debug.Print obj.Name, TypeName(obj)
        Next
    End Sub

Now the code displays a list of all the sheets in the workbook, along with their type.