Microsoft Office Tutorials and References
In Depth Information
7.4.2 The Immediate If Function
This function indicates whether an expression can be evaluated as a number or whether a cell
contains a value that can be evaluated as a number. For instance, if cell A1 contains the data 123
(even if this cell is formatted as text), then the condition in:
If IsNumeric(Range("A1")) Then
will evaluate to True . On the other hand, if the cell contains the data 123 Main Street, then the
condition will evaluate to False .
7.4.2 The Immediate If Function
The Immediate If function has the syntax:
IIf( Expression , TruePart , FalsePart )
If Expression is True , then the function returns TruePart . If Expression is False , the
function returns FalsePart . For instance, the following code displays a dialog indicating
whether or not the first row in the active worksheet is empty:
Dim rng As Range
Set rng = ActiveSheet.Rows(1)
MsgBox IIf(IsEmpty(ActiveSheet.Cells(1, 1)), _
"Cell is empty", "Cell is not empty")
It is very important to note that the Immediate If function always evaluates both TruePart and
FalsePart , even though it returns only one of them. Hence, we must be careful about
undesirable side effects. For example, the following code will produce a division by zero error
because even though the IIf function returns x only when x is not equal to 0, the expression
x is evaluated in all cases, including when x is equal to 0:
x = 0
y = IIf(x = 0, x ^ 2, 1 / x)
7.4.3 The Switch Function
The syntax of the Switch function is:
Switch( expr1 , value1 , expr2 , value2 , ... , exprn , valuen )
where exprn and valuen are expressions. Note that there need only be one expression-value
pair, but the function is more meaningful if there are at least two such pairs.
The Switch function evaluates each expression exprn . When it encounters the first True
expression, it returns the corresponding value. As with the IIf function, Switch always
evaluates all of the expressions. If none of the expressions is True, the function returns Null . This
can be tested with the IsNull function.
The procedure in Example 7-1 displays the type of file based on its extension: Template,