Exiting Loops and Procedures

Usually, your macro will run through loops and procedures from beginning to end. There may be situations, however, when leaving a loop or procedure earlier than normal can save you time by avoiding unnecessary repetition.

For example, if you're searching for a value in an array using a For...Next loop and you find the value the first time through the loop, there's no reason to search the rest of the array — you can stop repeating the loop and continue with the rest of the procedure immediately. If an error occurs in a procedure that makes the remainder of the procedure unnecessary, you can leave the procedure immediately. You can cut a control structure off early by using one of the Exit statements.

While the Exit statements can be convenient, you should use them only where absolutely necessary and only as a response to an extraordinary condition (not in the normal flow of a loop or procedure). Overusing Exit statements can make your code difficult to read and debug.

There may be better ways to avoid portions of your macro. For example, instead of using an Exit statement inside a For...Next loop searching for a value in an array, you could use a Do loop to search the array only while an incremented index value is smaller than the array's upper bound and a Boolean variable value is False. When you find the array value, setting the Boolean value to True causes the loop to stop.

i = LBound(searchArray)
ub = UBound(searchArray)
foundIt = False
Do
    If searchArray(i) = findThis Then foundIt = True
    i = i + 1
Loop While i <= ub And Not foundIt

You use the Exit For statement to exit directly from a For loop, and you use the Exit Do statement to exit directly from a Do loop.

For Each c in rangeToSearch
    If c.Value = searchValue Then
        found = True
        Exit For
    End If
Next

You use the Exit Sub and Exit Function statements to exit a procedure.

For Each c in rangeToSearch
    If c.Value = searchValue Then
        counter = counter + 1
    ElseIf c.Value = "Bad Data" Then
        countValues = Null
        Exit Function    'Stop testing and exit immediately.
    End If
Next c