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." |
|
