If statements allow you to set conditions that determine whether or not a block of code gets executed. You have just seen the If statement in action in Listing 4.1. VBA offers a variety of If statement structures, which are all described in this section. Also included here is the Select Case statement, which allows the value of one variable or expression to determine which block of statements from a group to execute.
If…Then… Block
The If statement allows you to state conditions that must be met before a block of code statements can be executed. The conditional expression is usually some comparison, such as
where > means greater than, <= means less than or equal to, and <> means not equal to. The “Comparison Operators” sidebar lists all the different combinations available. The block of code inside the If statement is executed only if the conditional expression evaluates to True; otherwise, execution jumps to the End If statement, or to the next line if the whole If statement block is contained in a single statement.
When an If statement block contains only one statement, the whole block can be written on a single line, as shown in the following FindMax function:
Alternatively, an If statement block with one statement can also be written over several lines, which requires an End If statement to indicate the end. For example, the same FindMax function can be written as
Similarly, when there are several coding statements, these can be placed on separate lines, as in
Or they can be placed on the same line separated by colons:
Even the whole If statement can be placed on the same line:
Be aware, though, that code containing multiple statement lines is generally harder to read and maintain as compared with single statement lines.
You can have If statements nested inside other If statements, with each one terminated by an End If. For example:
Placing one If statement inside another If statement without any other coding statements is considered bad programming practice and can be avoided by combining the conditions, as follows:
Comparison Operators
Comparison operators are used to compare the values of two expressions and provide a True or False result. Here are the operators available in VBA:
If…Then…Else…
The If…Then…Else… statement allows you to have two blocks of statements: one to be executed if the condition is True, and the other to be executed if the condition is False. For example:
The following example gives an If..Then…Else… statement that includes some nested If statements:
VBA provides an ElseIf clause that is useful for combining an Else clause that’s immediately followed by an If statement. For example, the If…Then…Else… block shown just above can be rewritten as follows:
This rewrite avoids the need for so many End If statements and so much statement indenting. The statement in the last Else clause will be executed if the Number variable is not equal to 1, 2, or 3. This version is easier to read and maintain than the first version. However, where the same variable is being tested at every condition, a Select Case statement provides an even better alternative.
Select Case Statement
The Select Case statement provides a way of selecting a block of code to be executed according to the value of an expression given in the first line of this statement. The designated value can be just about anything; in the following statement, the expression is the name of the door style that’s currently selected:
The value of the variable CurrentDoorStyle is a string specifying the current door’s style. When the Select Case statement is entered,this string is compared with the strings in each Case clause until either a match is found or the Case Else clause is encountered. The Price variable is then set to the price in the statement from the first Case clause that evaluates to True.
