Controlling Program Flow

Useful procedures must be able to make decisions based on the values of variables and then take specified actions based on those decisions. Blocks of code, for example, might need to be repeated until a specified condition occurs. Statements used to make decisions and repeat blocks of code are the fundamental elements that control program flow in VBA and all other programming languages.

All programming languages require methods of executing different algorithms based on the results of one or more comparison operations. You can control the flow of any program in any programming language with just three types of statements: conditional execution (If...Then...End If), repetition (Do While...Loop and related structures), and termination (End... and Exit). Additional flow control statements in VBA and other programming languages make writing code more straightforward.

Branching and Labels

When BASIC was first developed, the only method of controlling program flow was through its GOTO LineNumber and GOSUB LineNumber statements. Every line in the program required a number that could be used as a substitute for a label. GOTO LineNumber caused the interpreter to skip to the designated line and continue executing the program from that point. GOSUB LineNumber caused the program to follow that same branch, but when the BASIC interpreter that executed the code encountered a RETURN statement, program execution jumped back to the line following the GOSUB statement and continued executing at that point.

VBA's GoTo Label statement causes your code to branch to the location named Label: and continue from that point. Note the colon following Label:, which identifies the single word you assigned as a label. However, the colon isn't required after the label name following the GoTo. In fact, if you add the colon, you get a "Label not found" error message.

A label name must begin in the leftmost column (1) of your code. This positioning often interferes with the orderly indenting of your code (explained in the next section), which is just one more reason, in addition to those following, for not using GoTo.

The GoTo statement is required for only one purpose in VBA: to handle errors with the On Error GoTo Label statement. Although VBA supports BASIC's ON...GOTO and ON...GOSUB statements, using those statements is not considered good programming practice. You can eliminate most GoTo statements in form and report modules by using Access's Error event and the DAO and ADO Errors collection. The Error event is described in the "Handling Runtime Errors" section later in this chapter, and the Errors collection is explained in the next chapter.

Note

Visual Basic .NET finally dispenses with On Error GoTo Label statements by incorporating structured error handling similar to that provided by C/C++ and Java. Visual Basic .NET provides the Try...Catch...Finally...End Try structure to eliminate the need for On Error GoTo Label and the traditional Label: statement that identifies the beginning of error-handling code.

Conditional Statements

A conditional statement executes the statements between its occurrence and the terminating statement if the result of the relational operator is true. Statements that consist of or require more than one statement for completion are called structured statements, control structures, or just structures.

The If...Then...End If Structure

The syntax of the primary conditional statement of VBA is as follows:

 If blnCondition1 [= True] Then     Statements to be executed if Condition1 is true   [Else[If blnCondition2[= True] Then]]      Optional statements to be executed if blnCondition1      is false [and blnCondition2 is true] End If 

The = True elements of the preceding conditional statement are optional and typically not included when you write actual code. If blnCondition1 Then and If blnCondition1 = True Then produce the same result when blnCondition1 is True.

You can add a second condition with the ElseIf keyword. The ElseIf condition must be true to execute the statements that are executed if blnCondition1 is not True (False). Note that no space is used between Else and If. An If...End If structure that incorporates an ElseIf statement is the simplified equivalent of the following:

 If blnCondition1 Then     Statements to be executed if Expression1 is true Else    If blnCondition2 Then        Statements to be executed if Condition1% is        false and blnCondition2 is true]    End If End If 

A statement is executed based on the evaluation of the immediately preceding expression. Expressions that include If ... End If or other flow-control structures within other If...End If structures are said to be nested, as in the preceding example. The number, or depth, of If...End If structures that can be nested within one another is unlimited.

Note that the code between the individual keywords that make up the flow-control structure is indented. Indentation makes code within structures easier to read. You usually use the Tab key to create indentation.

To evaluate whether a character is a letter and to determine its case, you can use the following code:

 If Asc(strChar) > 63 And Asc(strChar) < 91 Then   strCharType = "Uppercase Letter" ElseIf Asc(strChar) > 96 And Asc(strChar) < 123 Then   strCharType = "Lowercase Letter" Else   strCharType = "Not a Letter" End If 

You use the If...End If structure more often than any other flow control statement.

Tip

For a list of the ASCII numeric codes for alphabetic, numeric, and special characters, type ascii in the Ask a Question text box of the VBA editor, and click the Character Set (0 - 127) item. Character Set (128 - 255) contains special characters.

The Select Case...End Select Construct

When you must choose among many alternatives, If...End If structures can become very complex and deeply nested. The Select Case...End Select construct was added to procedural BASIC to overcome this complexity. In addition to testing whether an expression evaluates to True or False, Select Case can evaluate variables to determine whether those variables fall within specified ranges. The generalized syntax is in the following example:

 Select Case VarName    Case Expression1[, Expressions,...]      (Statements executed if the value of VarName      = Expression1 or Expressions)   [Case Expression2 To Expression3      (Statements executed if the value of VarName      is in the range of Expression2 to Expression3)]   [Case Is RelationalExpression      (Statements executed if the value of      VarName = RelationalExpression)]   [Case Else      (Statements executed if none of the      above cases is met)] End Select 

Select Case evaluates VarName, which can be a string, a numeric variable, or an expression. It then tests each Case expression in sequence. Case expressions can take one of the following four forms:

• A single value or list of values to which to compare the value of VarName. Successive members of the list are separated from their predecessors by commas.

• A range of values separated by the keyword To. The value of the first member of the range limits must be less than the value of the second. Each string is compared by the ASCII value of its first character.

• The keyword Is followed by a relational operator, such as <>, <, <=, =, >=, or >, and a variable or literal value.

• The keyword Else. Expressions following Case Else are executed if no prior Case condition is satisfied.

The code associated with the first matching Case condition is executed. If no match is found and the Case Else statement is present, the code following the statement is executed. Program execution then continues at the line of code following the End Select terminating statement.

If VarName is a numeric type, all Case expressions that use VarName are forced to the same data type.

The following example is of Select Case using a numeric variable, curSales:

 Select Case curSales    Case 10000 To 49999.99      intClass = 1    Case 50000 To 100000      intClass = 2    Case Is < 10000      intClass = 0    Case Else      intClass = 3 End Select 

Note that because curSales is of the Currency type, all the comparison literals also are treated as Currency values for the purpose of comparison.

A more complex example that evaluates a single character follows:

 Select Case strChar    Case "A" To "Z"      strCharType = "Upper Case"    Case "a" To "z"      strCharType = "Lower Case"    Case "0" To "9"      strCharType = "Number"    Case "!", "?", ".", ",", ";"      strCharType = "Punctuation"    Case ""      strCharType = "Empty String"    Case < 32      strCharType = "Special Character"    Case Else      strCharType = "Unknown Character" End Select 

This example demonstrates that Select Case, when used with strings, evaluates the ASCII value of the first character of the string either as the variable being tested or the expressions following Case statements. Thus, Case < 32 is a valid test, although strChar is a string variable.

Repetitive Operations: Looping

In many instances, you must repeat an operation until a given condition is satisfied, whereupon the repetitions terminate. You might want to examine each character in a word, sentence, or document, or you might want to assign values to an array with many elements. Loops are used for these and many other purposes.

Using the For...Next Statement

VBA's For...Next statement lets you repeat a block of code for a specified number of times, as shown in the following example:

 For intCounter = intStartValue To intEndValue [Step intIncrement]    Statements to be executed    [Conditional statement    Exit For    End of conditional statement] Next [intCounter] 

The block of statements between the For and Next keywords is executed (intEndValue - intStartValue + 1) / intIncrement) times. As an example, if intStartValue = 5, intEndValue = 10, and intIncrement = 1, the execution of the statement block is repeated six times. You need not add the keyword Step in this case the default increment is 1. Although Integer data types are shown, you can use Long (integer) values. The use of real numbers (Single or Double data types) as values for counters and increments is possible but uncommon because decimal rounding errors can cause unexpected results.

The dividend of the previous expression must always be a positive number if the execution of the internal statement block is to occur. If intEndValue is less than intStartValue, intIncrement must be negative; otherwise, the For...Next statement is ignored by the VBA interpreter.

The optional Exit For statement is provided so that you can prematurely terminate the loop using a surrounding If...Then...End If conditional statement. Changing the value of the counter variable within the loop itself to terminate its operation is discouraged as a dangerous programming practice. You might make a change that would cause an infinite statement loop.

Using For...Next Loops to Assign Values to Array Elements

One of the most common applications of the For ... Next loop is to assign successive values to the elements of an array. If you've declared a 26-element array named astrAlphabet, the following example assigns the capital letters A through Z to its elements:

 For intLetter = 1 To 26    strAlphabet(intLetter) = Chr$(intLetter + 64) Next intLetter 

The preceding example assigns 26 of the array's 27 elements if you used Dim strAlphabet(26) As String rather than Dim strAlphabet(1 To 26) As String. 64 is added to intLetter because the ASCII value of the letter A is 65, and the initial value of intLetter is 1. The VBA Chr$() function converts the ordinal position of intLetter in the ASCII character set to a String value. Using Chr() returns a Variant value that the interpreter must coerce to a String value.

Tip

VBA offers two versions of each function that returns a String value. Always use the version with the $ data type identifier when returning values to a variable declared As String. Complex string expressions execute much faster if the compiler doesn't need to coerce Variants to Strings.

A special case of the For...Next loop, For Each objName In colName...Next objName, loop iterates each object (objName) in a collection (colName).

For statement an example of using a For Each...Next loop, see "Customizing Applications with CommandBar Objects," p. 1213.

Understanding Do While...Loop and Do Until...Loop

A more general form of the loop structure is Do While...Loop, which uses the following syntax:

 Do While blnCondition [= True]   Statements to be executed   [Conditional statement   Exit Do   End of conditional statement] Loop 

This loop structure executes the intervening statements only if blnCondition equals True (Not False, a value other than 0) and continues to do so until blnCondition becomes False (0) or the optional Exit Do statement executes.

From the preceding syntax, you can duplicate the previous For...Next array assignment example with the following structure:

 intLetter = 1 Do While intLetter < = 27    astrAlphabet(intLetter) = Chr$(intLetter + 64)    intLetter = intLetter + 1 Loop 

Another example of a Do loop is the Do Until...Loop structure, which loops as long as the condition isn't satisfied, as in the following example:

 Do Until {blnCondition <> True|Not blnCondition}    Statements to be executed    [Conditional statement    Exit Do    End of conditional statement] Loop 

The Not blnCondition expression is more commonly used than blnCondition <> True, but either is acceptable.

VBA also supports the While...Wend loop, which is identical to the Do While...Loop structure, but you can't use the Exit Do statement within While...Wend. The While...Wend structure is provided for compatibility with earlier versions of BASIC and should be abandoned in favor of Do {While|Until}...Loop in VBA.

Making Sure Statements in a Loop Occur at Least Once

You might have observed that the statements within a Do While...Loop structure are never executed if intCondition is false when the structure is encountered in your application. You can also use a structure in which the conditional statement that causes loop termination is associated with the Loop statement. The syntax of this format is in the following example:

 Do   Statements to be executed   [Conditional statement then   Exit Do   End of conditional statement] Loop While intCondition[= True] 

A similar structure is available for the Do Until... Loop:

 Do   Statements to be executed   [Conditional statement   Exit Do   End of conditional statement] Loop Until intCondition[= False] 

These structures ensure that the loop executes at least once before the condition is tested.