MS-Excel / General Formatting

---

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, or 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 shows a revised version of the BigNumbers procedure, which exits the Do...Loop if it comes across a cell that isn't a number.

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