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”
 
Search JabSto ::




Custom Search