Word Document Properties

Part II: Learning the Ropes 
10_046500 ch05.qxp 12/5/06 5:35 PM Page 118

---

Using the IIf function
You might need to make a decision in a single line of code instead of the
three lines (minimum) that other decision-making techniques require. The
IIf
function is a good choice when you need to make simple and concise
decisions in your program. It has the advantage of providing decision-making
capability in a single line of code. Listing 5-4 shows an example of the 
IIf
function in action. (You can find the source code for this example on the
Dummies.com site at 
http://www.dummies.com/go/vbafd5e
.)
Listing 5-4
Using IIf to Make Inline Decisions
Public Sub IIfDemo()
‘ Create variables to hold the two numbers.
Dim Input1 As Double
Dim Input2 As Double
‘ Create an output string.
Dim Output As String
‘ Fill the variables with input from the worksheet.
Input1 = Sheet1.Cells(3, 2)
Input2 = Sheet1.Cells(4, 2)
‘ Use nested IIf functions to check all three
‘ conditions.
Output = IIf(Input1 = Input2, _
“The values are equal.”, _
IIf(Input1 > Input2, _
“First Number is greater.”, _
“Second Number is greater.”))
‘ Place the output on the worksheet.
Sheet1.Cells(6, 2) = Output
End Sub
This example begins by getting the input from the worksheet. It also creates
an 
Output
string.
The 
IIf
function requires three arguments, none of which is optional. The
first argument is an expression. The second is a single-line statement of 
what to do if the expression is true, and the third is a single-line statement 
of what to do if the expression is false.
119
Chapter 5: Creating Structured Programs
10_046500 ch05.qxp 12/5/06 5:35 PM Page 119

---

The first 
IIf
function determines whether 
Input1
is equal to 
Input2
. If the
expression is true, the first 
IIf
function returns 
The values are equal.
to 
Output
. Otherwise, the first 
IIf
function calls the second 
IIf
function.
The second 
IIf
function determines whether 
Input1
is greater than 
Input2
.
If it is, the second 
IIf
function returns 
First Number is greater.
to the
first 
IIf 
function, which returns it to 
Output
. Likewise, if the expression is
false, the second 
IIf
function returns 
Second Number is greater.
You can see that this technique could easily become impossible to read and
is nearly impossible to comment. However, it uses only one line of code. The
nearest contender appears in the preceding “Using the If...Then...ElseIf state-
ment” section and requires seven lines of code.
Making a Choice by Using 
the Select Case Statement
You can use the 
If...Then...Else
or 
If...Then...ElseIf
statement to
meet all decision-making needs. However, using these statements can quickly
make your code hard to read when you need to make a lot of decisions in
rapid succession. Using these statements is required when you want to per-
form complex expression checking. VBA provides the 
Select Case
state-
ment as an easier-to-read choice when making a single selection from a list of
choices. If you know that a variable contains one of several choices and all
you need to check is the choice, using the 
Select Case
statement makes
sense.
Using the Select Case statement
The 
Select Case
structure begins with the 
Select Case
statement and
ends with an 
End Case
statement. You provide a variable that the 
Select
Case
statement can use for selection. Within the 
Select Case
structure are
Case
clauses,
or values that the 
Select Case
structure uses for compari-
son. When the value of a clause matches the value of the input variable, the
Select Case
structure performs all tasks required by that clause.
In this section, I use examples of the 
Select Case
statement to make a
choice of which room to use to store a product. You could easily make this
example into a database program, but this section uses a spreadsheet for
ease of explanation. Figure 5-2 shows the two input columns used for this
example along with typical output in the third column.
120