Decisions Based on More Than One Condition

The SimplelfThen procedure that you worked with in the previous section evaluated only a single condition in the If.. .Then statement. This statement, however, can take more than one condition. To specify multiple conditions in an If.. .Then statement, use the logical operators AND and OR (see Table 5-2 at the beginning of this chapter). Here's the syntax with the AND operator:

If conditionl AND condition2 Then statement

In the above syntax, both conditionl and condition2 must be true for Visual Basic to execute the statement to the right of the Then keyword. For example:

If sales = 10000 AND salary <45000 Then SlsCom = Sales * 0.07

In this example:

Condition1 sales=10000 Condition2 salary <45000

When AND is used in the conditional expression, both conditions must be true before Visual Basic can calculate the sales commission (SlsCom). If any of these conditions are false, or both are false, Visual Basic ignores the statement after Then.

When it's good enough to meet only one of the conditions, you should use the OR operator. Here's the syntax:

If conditionl OR condition2 Then statement

The OR operator is more flexible. Only one of the conditions has to be true before Visual Basic can execute the statement following the Then keyword. Let's look at this example:

If dept = "S" OR dept = "M" Then bonus = 500

In the above example, if at least one condition is true, Visual Basic assigns 500 to the bonus variable. If both conditions are false, Visual Basic ignores the rest of the line.

Now let's look at a complete procedure example. Suppose you can get a 10% discount if you purchase 50 units of a product, each priced at \$7.00. The IfThenAnd procedure demonstrates the use of the AND operator.

1. Enter the following procedure in the IfThen module of the Decisions

(Chap05.xls) project:

Sub IfThenAnd()

Dim price As Single Dim units As Integer Dim rebate As Single

Const strmsg1 = "To get a rebate you must buy an additional " Const strmsg2 = "Price must equal \$7.00"

units = Range("B1").Value price = Range("B2").Value

If price = 7 AND units >= 50 Then rebate = (price * units) * 0.1 Range("A4").Value = "The rebate is: \$" & rebate End If

Range("A4").Value = strmsg1 & 50 - units & " unit(s)." End If

Range("A4").Value = strmsg2 End If

Range("A4").Value = "You didn't meet the criteria." End If End Sub

The IfThenAnd procedure shown above has four If.. .Then statements that are used to evaluate the contents of two variables: price and units. The AND operator between the keywords If.. .Then allows more than one condition to be tested. With the AND operator, all conditions must be true for Visual Basic to run the statements between the Then.End If keywords.

Because the IfThenAnd procedure is based on the data entered in worksheet cells, it's more convenient to run it from the Excel window.

2. Switch to the Microsoft Excel application window, and choose Tools | Macro | Macros.

3. In the Macro dialog box, select the IfThenAnd macro and click the Options button.

4. Assign the shortcut key to your macro: Ctrl+Shift+I, and then exit the Macro Options dialog box.

5. Enter the following data in a worksheet:

 A B 1 Units 234 2 Price 7

7. Change the values of cells B1 and B2 so that each time you run the procedure, a different If.. .Then statement is true.

Tip 5-2: If Block Instructions and Indenting

 If condition Then If condition Then actionl action End If End If

Looking at the If...Then block statement on the right, you can easily see where the block begins and where it ends.

The If...Then...Else Statement

Now you know how to display a message or take an action when one or more conditions are true or false. What should you do, however, if your procedure needs to take one action when the condition is true and another action when the condition is false? By adding the Else clause to the simple If.. .Then statement, you can direct your procedure to the appropriate statement depending on the result of the test.

The If...Then...Else statement has two formats—single line and multi-line. The single line format is as follows:

If condition Then statementl Else statement2

The statement following the Then keyword is executed if the condition is true, and the statement following the Else clause is executed if the condition is false. For example:

If Sales>5000 Then Bonus = Sales * 0.05 Else MsgBox "No Bonus"

If the value stored in the variable Sales is greater than 5000, Visual Basic will calculate the bonus using the following formula: Sales * 0.05. However, if the variable Sales is not greater than 5000, Visual Basic will display the message "No Bonus."

The If...Then...Else statement should be used to decide which of the two actions to perform.

When you need to execute more statements when the condition is true or false, it's better to use the multi-line format of the If...Then...Else statement:

If condition Then statements to be executed if condition is True

Else statements to be executed if condition is False

End If

Notice that the multi-line (block) If...Then...Else statement ends with the End If keywords. Use the indentation shown above to make this block structure easier to read.

In the following example, if the condition ActiveSheet.Name = "Sheetl" is true, Visual Basic will execute the statements between Then and Else and ignore the statement between Else and End If. If the condition is false, Visual Basic will omit the statements between Then and Else and execute the statement between Else and End If.

If ActiveSheet.Name = "Sheetl" Then ActiveSheet.Name = "My Sheet" MsgBox "This sheet has been renamed."

Else

MsgBox "This sheet name is not default."

End If

Let's look at the procedure example:

1. Insert a new module into the Decisions (Chap05.xls) project.

2. Change the module name to IfThenElse.

3. Enter the following WhatTypeOfDay procedure:

Sub WhatTypeOfDay()

Dim response As String

Dim question As String

Dim strmsgl As String, strmsg2 As String

Dim myDate As Date question = "Enter any date in the format mm/dd/yyyy:" _ & Chr(13)& " (e.g., 11/22/1999)" strmsgl = "weekday" strmsg2 = "weekend"

response = InputBox(question) myDate = Weekday(CDate(response)) If myDate >= 2 AND myDate <= 6 Then MsgBox strmsgl

Else

MsgBox strmsg2 End If

End Sub

The above procedure asks the user to enter any date. The user-supplied string is then converted to the Date data type with the built-in CDate function. Finally, the Weekday function converts the date into an integer that indicates the day of the week (see Table 5-3). The integer is stored in the variable myDate. The conditional test is performed to check whether the value of the variable myDate is greater than or equal to two (>=2) and less than or equal to six (<=6). If the result of the test is true, the user is told that the supplied date is a weekday; otherwise, the program announces that it's a weekend.

Table 5-3: Values returned by the built-in Weekday function

Constant Value vbSunday 1

vbMonday 2

vbTuesday 3

vbWednesday 4

vbThursday 5

vbFriday 6

vbSaturday 7

4. Run the procedure from the Visual Basic window. Run it a few times, each time supplying a different date. Check the Visual Basic answers against your desktop or wall calendar.

Tip 5-3: What is Structured Programming?

Structured programming requires that all programs have a modular design and use only three types of logical structures: sequences, decisions, and loops. Sequences are statements that are executed one after another. Decisions allow you to execute specific statements based on a test of some condition. Loops make it possible to execute one or more statements repeatedly, as long as a specified condition is true. Loops are the subject of the next chapter. In structured programming, other logical statements, such as GoTos, are not allowed. The code of a structured program is easy to follow—it flows smoothly from top to bottom without jumping around to specified labels. Following is an example of a structured and unstructured program:

Unstructured program:

Sub GoToDemo() Dim num, mystr num = 1 If num = 1 Then

GoTo linel Else

GoTo Line2 Linel:

mystr = "Number equals 1" GoTo LastLine Line2:

mystr = "Number equals 2" LastLine:

Debug.Print mystr End sub

Structured program:

Sub Structure() Dim num, mystr num = 1

If num = 1 Then mystr = "Number equals 1" Debug.Print mystr Else mystr = "Number equals 2" End if End Sub

When you write your VBA procedure from scratch and it needs to jump from one line of a program to another, you may be tempted to use the GoTo statement. Don't jump around. Relying on GoTo statements for changing the course of your procedure leads to confusing code referred to as spaghetti code. You can easily arrive at the required destination in your procedure by using structured programming.

Here's another practice procedure to demonstrate the use of the If.. .Then.. .Else statement:

Sub EnterData()

Dim cell As Object Dim strmsg As String

On Error GoTo VeryEnd strmsg = "Select any cell:"

Set cell = Application.InputBox(prompt:=strmsg, Type:=8) cell.Select

If IsEmpty(ActiveCell) Then

ActiveCell.Formula = InputBox("Enter text or number:")

Else

ActiveCell.Offset(1, 0).Select

End If VeryEnd: End Sub

The EnterData subroutine shown above prompts the user to select any cell. The cell address is then assigned to the cell object variable. The If...Then...Else structure checks if the selected cell is empty. IsEmpty is the built-in function that is used to determine whether a variable has been initialized. IsEmpty returns true if the variable is uninitialized. Recall that a variable is said to be initialized when it is assigned an initial value. In this procedure, if the active cell is empty, Visual Basic treats it as a zero-length string (" "). Instead of:

If IsEmpty(ActiveCell) Then you can use the following instruction:

If ActiveCell.Value = "" Then

If the active cell is empty, the statement following Then is executed. This statement prompts the user to enter text or number data, and once the input is provided, the data is entered in the active cell. If the active cell is not empty, Visual Basic will jump to the instruction following the Else clause. This instruction will cause Visual Basic to select the next cell in the same column.

When you run this procedure and the input box prompts you to select a cell, click any cell in the worksheet. The selected cell address will appear in the edit box. Click OK to exit the input box. Visual Basic will check the contents of the selected cell and jump to the true or false section of your procedure (the true section follows Then, and the false section follows Else).

The If...Then...ElseIf Statement

Quite often you will need to check the results of several different conditions. To join a set of If conditions together, you can use the Elself clause. Using the If...Then...ElseIf statement, you can supply more conditions to evaluate than is possible with the If...Then...Else statement, which was the subject of the preceding section. Here's the syntax of the If.. .Then.. .ElseIf statement:

If conditionl Then statements to be executed if conditionl is True ElseIf condition2 Then statements to be executed if condition2 is True ElseIf condition3 Then statements to be executed if condition3 is True ElseIf conditionN Then statements to be executed if conditionN is True

Else statements to be executed if all conditions are False End If

The Else clause is optional; you can omit it if there are no actions to be executed when all conditions are false.

Tip 5-4: ElseIf Clause

Your procedure can include any number of ElseIf statements and conditions. The ElseIf clause always comes before the Else clause. The statements in the ElseIf clause are executed only if the condition in this clause is true.

Let's look at the following example:

If ActiveCell.Value = 0 Then

ActiveCell.Offset(0, 1).Value = "zero" ElseIf ActiveCell.Value >0 Then

ActiveCell.Offset(0, 1).Value = "positive" ElseIf ActiveCell.Value <0 Then

ActiveCell.Offset(0, 1).Value = "negative"

End If

This example checks the value of the active cell and enters the appropriate label (zero, positive, negative) in the adjoining column. Notice that the Else clause is not used. If the result of the first condition (Active.Value = 0) is false, Visual Basic jumps to the next ElseIf statement and evaluates its condition (ActiveCell.Value>0). If the value is not greater than zero, Visual Basic skips to the next ElseIf and the condition ActiveCell.Value<0 is evaluated.

Let's see how the If...Then...Else statement works in a complete procedure:

1. Insert a new module into the current project.

2. Rename the module IfThenElself.

3. Enter the following WhatValue procedure:

Sub WhatValue()

Range("A1").Select If ActiveCell.Value = 0 Then

Sub WhatValue()

Range("A1").Select If ActiveCell.Value = 0 Then

ActiveCell.0ffset(0,

1)

.Value =

"zero"

Elself ActiveCell.Value

> 0

Then

ActiveCell.0ffset(0,

1)

.Value =

"positive

Elself ActiveCell.Value

< 0

Then

ActiveCell.0ffset(0,

1)

.Value =

End If

End If End Sub

Because you need to run the WhatValue procedure several times to test each condition, let's have Visual Basic assign a temporary keyboard shortcut to this procedure.

4. Open the Immediate window and type the following statement: Application.OnKey "WhatValue"

When you press Enter, Visual Basic runs the OnKey method that assigns the WhatValue procedure to the key sequence Ctrl+Shift+Y. This keyboard shortcut is only temporary—it will not work when you restart Microsoft Excel. To assign the shortcut key to a procedure, use the Options button in the Macro dialog box accessed from the Tools menu in the Microsoft Excel window.

5. Now switch to the Microsoft Excel window and activate Sheetl.

6. Enter zero (0) in cell A1, and press Ctrl+Shift+Y. Visual Basic calls the WhatValue procedure and enters "zero" in cell B1.

7. Enter any number greater than zero in cell A1, and press Ctrl+Shift+Y.

Visual Basic again calls the WhatValue procedure. Visual Basic evaluates the first condition, and because the result of this test is false, it jumps to the ElseIf statement. The second condition is true, so Visual Basic executes the statement following Then and skips over the next statements to the End If. Because there are no more statements following the End If, the procedure ends. Cell B1 now displays the word "positive."

8. Enter any number less than zero in cell A1, and press Ctrl+Shift+Y. This time, the first two conditions return false, so Visual Basic goes to examine the third condition. Because this test returns true, Visual Basic enters the "negative" label in cell B1.

9. Enter any text in cell A1, and press Ctrl+Shift+Y. Visual Basic's response is "positive." However, this is not a satisfactory answer. You may want to differentiate between positive numbers and text by displaying a "text" label. To make the WhatValue procedure smarter, you need to learn how to make more complex decisions by using nested If.. .Then statements.

0 0