Page 105 - Excel Progamming Weekend Crash Course

h540629 ch06.qxd 9/2/03 9:33 AM Page 80

80 Saturday Morning

The IIf Function

VBA’s IIf function is related to the If...Then statement and is used to return
one of two values, depending on whether a condition is True. The syntax is:
IIf(condition, exp1, exp2)
If condition is True, the function returns exp1; if condition is False, it
returns exp2. Here’s an example that sets the variable Z equal to the larger of
X or Y:
Z = IIf(X > Y, X, Y)

The Select Case Statement

The Select Case statement lets your program make multiple choices based on the value of
a single expression. The syntax is as follows:
Select Case expression
Case template-1
block-1
Case template-2
block-2
...
Case template-n
block-n
Case Else
block-else
End Select
Here’s how it works. The program starts by evaluating expression. It then goes down
the list of Case statements, comparing the value of the expression against each template. If
a match is found, the associated block of statements is executed. If there is no match, the
block of statements associated with the Case Else is executed. You can have as many Case
statements as desired. The Case Else is optional. Note than even if there are multiple tem-
plates that match that value of expression, only one (the first one found) is executed.
The template in a Case statement can contain a single comparison value, or multiple com-
parison values separated by commas. Each comparison value can be one of the following:

Any expression (must be matched exactly).
A range of values indicated by the To keyword. For example, 10 To 20.
The Is keyword followed by a comparison operator and an expression. For example,
Is < 100.
Here’s an example that would match numerical values that are equal to 12, greater than
or equal to 20, or between 1 and 4.

Case 12, Is >= 20, 1 To 4