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,

Workbook, or Add-in.

Example 7-1. The Switch Function

Sub ShowFileType(FileExt As String)

Dim FileType As Variant