Microsoft Office Tutorials and References

In Depth Information

**Controlling Code Execution**

VBA’s IIf function

VBA offers an alternative to the
If-Then
construct: the
IIf
function. This function takes three

arguments and works much like Excel’s IF worksheet function. The syntax is

IIf(expr, truepart, falsepart)

●
expr
: (Required) Expression you want to evaluate.

●
truepart
: (Required) Value or expression returned if
expr
is
True
.

●
falsepart
: (Required) Value or expression returned if
expr
is
False
.

The following instruction demonstrates the use of the
IIf
function. The message box displays

Zero
if cell A1 contains a zero or is empty and displays
Nonzero
if cell A1 contains anything

else.

MsgBox IIf(Range(“A1”) = 0, “Zero”, “Nonzero”)

It’s important to understand that the third argument (
falsepart
) is always evaluated, even if

the first argument (
expr
) is
True
. Therefore, the following statement generates a Division By

Zero error if the value of
n
is
0
(zero):

MsgBox IIf(n = 0, 0, 1 / n)

Select Case constructs

The
Select Case
construct is useful for choosing among three or more options. this construct

also works with two options and is a good alternative to
If-Then-Else
. The syntax for

Select Case
is as follows:

Select Case testexpression

[Case expressionlist-n

[instructions-n]]

[Case Else

[default_instructions]]

End Select

The following example of a
Select Case
construct shows another way to code the
GreetMe

examples that I presented in the preceding section:

Sub GreetMe()

Dim Msg As String

Select Case Time

Case Is < 0.5

Msg = “Good Morning”