Exiting Loops Early

Sometimes you may not want to wait until the loop ends on its own. It's possible that a user enters the wrong data, a procedure encounters an error, or perhaps the task has been completed and there's no need to do additional looping. You can leave the loop early without reaching the condition that normally terminates it. Visual Basic has two types of Exit statements:

■ The Exit For statement is used to end either a For.. .Next or a For Each.. .Next loop early.

■ The Exit Do statement immediately exits any of the VBA Do loops.

The following procedure demonstrates how to use the Exit For statement to leave the For Each.Next loop early:

l. Enter the following procedure in the current module:

Sub EarlyExit() Dim myCell As Range

For Each myCell in Range("A1:H10") If myCell.Value = "" Then myCell.Value = "empty"


Exit For End If Next myCell End Sub

The EarlyExit procedure examines the contents of each cell in the specified range—A1:H10. If the active cell is empty, Visual Basic enters the text "empty" in the active cell. When Visual Basic encounters the first non-empty cell, it exits the loop.

2. Open a new workbook and enter a value in any cell within the specified range—A1:H10.

3. Run the EarlyExit procedure.

Tip 6-5: Exiting Procedures

If you want to exit a subroutine earlier than normal, use the Exit Sub statement. If the procedure is a function, use the Exit Function statement instead.

0 0

Post a comment