Using Exit For or Exit Do to Exit a Loop





Using Exit For or Exit Do to Exit a Loop

Most loops run their natural course and then the procedure moves on. There might be times, however, when you want to exit a loop prematurely. For example, you might come across a certain type of cell, an error might occur, or the user might enter an unexpected value. To exit a For...Next loop or a For Each...Next loop, use the Exit For statement. To exit a Do...Loop, use the Exit Do statement.

Listing 6.14 shows a revised version of the BigNumbers procedure, which exits the Do...Loop if it comes across a cell that isn't a number.

Listing 6.14. Version of the BigNumbers Procedure That Terminates with the Exit Do Statement if the Current Cell Isn't a Number
Sub BigNumbers2()
    Dim rowNum As Integer, colNum As Integer, currCell As Range
    '
    ' Initialize the row and column numbers
    '
    rowNum = ActiveCell.Row
    colNum = ActiveCell.Column
    '
    ' Get the first cell
    '
    Set currCell = ActiveSheet.Cells(rowNum, colNum)
    '
    ' Loop while the current cell isn't empty
    '
    Do While currCell.Value <> ""
        '
        ' Is it a number?
        '
        If IsNumeric(currCell.Value) Then
            '
            ' Is it a big number?
            '
            If currCell.Value >= 1000 Then
                '
                ' If so, color it magenta
                '
                currCell.Font.Color = VBAColor("magenta")
            End If
        '
        ' Otherwise, exit the loop
        '
        Else
            Exit Do
        End If
        '
        ' Increment the row number and get the next cell
        '
        rowNum = rowNum + 1
        Set currCell = ActiveSheet.Cells(rowNum, colNum)
    Loop
End Sub

The Absolute Minimum

This chapter showed you a number of methods for gaining maximum control over your VBA code. We began with a look at various VBA structures that allow your procedures to make decisions and act accordingly. In particular, I showed you how to work with If...Then...Else for true/false decisions; for multiple decisions I told you about the And and Or operators, the If...ElseIf...Else structure, and Select Case. I also included material on three decision-making functions: IIf, Choose, and Switch. You then learned about looping, including the structures Do...Loop, For...Next, and For Each...Next.

For beginning programmers, one of the most common causes of confusion when using these control structures is keeping track of which statements belong to which If...Then test or Do...While loop. This is particularly true if you end up with control structures nested within other control structure (see, for example, Listings 6.3, 6.11, and 6.14). I've stressed indenting your code throughout this book, and I want to underline this programming principle once again here:

  • In the main test or loop, indent the statements once (press Tab at the beginning of the first statement).

  • In a secondary (that is, nested) test or loop, double indent the statements (press Tab again at the beginning of the first statement).

  • In a tertiary (that is, nested within a nested structure) test or loop, triple indent the statements (press Tab again at the beginning of the first statement).

Here's a general example of how this indenting looks:

If MainExpression Then
    If SecondaryExpression Then
        If TertiaryExpression Then
            [TertiaryTrueStatements]
        Else
            [TeriaryFalseStatements]
        End If
    Else
        [SecondayFalseStatements]
    EndIf
Else
    [MainFalseStatements]
End If

Here's a list of chapters where you'll find related information:

  • This chapter used a few Word and Excel objects as examples. To get the full scoop on other objects available for these programs, see Chapter 7, "Programming Word" and Chapter 8, "Programming Excel."

  • Controlling code often depends on interaction with the user. For example, you might use If...Then...Else to test the value of a check box or Select Case to process a group of option buttons. See Chapter 12, "Interacting with the User," and Chapter 13, "Creating Custom VBA Dialog Boxes," to find out more about these topics.

  • A big part of procedure control involves anticipating potential user errors. You'll learn more about this topic in Chapter 15, "Debugging VBA Procedures."



     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows