Please note files for the “Hands-On” project may be found on the companion CD-ROM.
| Chapter 5 | ADDING DECISIONS TO EXCEL VBA PROGRAMS |
| A QUICK INTRODUCTION TO CONDITIONAL STATEMENTS |
Visual Basic for Applications, like other programming languages, offers special statements that allow you to include decision points in your own procedures. But what is decision making? Suppose someone approaches you with the question, “Do you like the color red?” After giving this question some thought, you’ll answer “yes” or “no.” If you’re undecided or simply don’t care, you might answer “maybe” or “perhaps.” In programming, you must be decisive. Only “yes” or “no” answers are allowed. In programming, all decisions are based on supplied answers. If the answer is positive, the procedure executes a specified block of instructions. If the answer is negative, the procedure executes another block of instructions or simply doesn’t do anything. In this chapter, you will learn how to use VBA conditional statements to alter the flow of your program. Conditional statements are often referred to as “control structures,” as they give you the ability to control the flow of your VBA procedure by skipping over certain statements and “branching” to another part of the procedure.
You make decisions in your VBA procedures by using conditional expressions inside the special control structures. A conditional expression is an expression that uses one of the relational operators listed in Table 5.1, one of the logical operators listed in Table 5.2, or a combination of both. When Visual Basic encounters a conditional expression in your program, it evaluates the expression to determine whether it is true or false.
TABLE 5.1. Relational operators in VBA. |
|
Operator |
Description |
= |
Equal to |
<> |
Not equal to |
> |
Greater than |
< |
Less than |
>= |
Greater than or equal to |
<= |
Less than or equal to |
TABLE 5.2. Logical operators in VBA. |
|
Operator |
Description |
AND |
All conditions must be true before an action can be taken. |
OR |
At least one of the conditions must be true before an action can be taken. |
NOT |
Used for negating a condition. If a condition is true, NOT makes it false. If a condition is false, NOT makes it true. |
The simplest way to get some decision making into your VBA procedure is to use the If…Then statement. Suppose you want to choose an action depending on a condition. You can use the following structure:
If condition Then statement
For example, to delete a blank row from a worksheet, first check if the active cell is blank. If the result of the test is true, go ahead and delete the entire row that contains that cell:
If ActiveCell = "" Then Selection.EntireRow.Delete
If the active cell is not blank, Visual Basic will ignore the statement following the Then keyword.
Sometimes you may want to perform several actions when the condition is true. Although you could add other statements on the same line by separating them with colons, your code will look clearer if you use the multiline version of the If…Then statement, as shown here:
If condition Then
statement1
statement2
statementN
End If
For example, to perform some actions when the value of the active cell is greater than 50, you can write the following block of instructions:
If ActiveCell.Value > 50 Then
MsgBox "The exact value is " & ActiveCell.Value
Debug.Print ActiveCell.Address & ": " & ActiveCell.Value
End If
In this example, the statements between the Then and the End If keywords are not executed if the value of the active cell is less than or equal to 50. Notice that the block If…Then statement must end with the keywords End If.
How does Visual Basic make a decision? It evaluates the condition it finds between the If…Then keywords. Let’s try to evaluate the following condition:ActiveCell.Value > 50.
|
Please note files for the “Hands-On” project may be found on the companion CD-ROM. |
|
1. Open a new Microsoft Excel workbook.
2. Select any cell in a blank worksheet and enter 50.
3. Switch to the Visual Basic Editor window.
4. Activate the Immediate window.
5. Enter the following statement, and press Enter when you’re done:
? ActiveCell.Value > 50
When you press Enter, Visual Basic writes the result of this test—false. When the result of the test is false, Visual Basic will not bother to read the statement following the Then keyword in your code. It will simply go on to read the next line of your procedure, if there is one. If there are no more lines to read, the procedure will end.
6. Now change the operator to less than or equal to, and have Visual Basic evaluate the following condition:
? ActiveCell.Value <= 50
This time, the test returns true, and Visual Basic will jump to whatever statement or statements it finds after the Then keyword.
7. Close the Immediate window.
Now that you know how Visual Basic evaluates conditions, let’s try the If…Then statement in a VBA procedure.
|
Hands-On 5.2. Writing a VBA Procedure with a Simple |
1. Open a new workbook and save it as C:\VBAPrimerExcel_ByExample\Chap05_ExcelPrimer.xlsm.
2. Switch to the Visual Basic Editor screen and rename the VBA project Decisions.
3. Insert a new module in the Decisions (Chap05_ExcelPrimer.xlsm) project and rename this module IfThen.
4. In the IfThen module, enter the following procedure:
Sub SimpleIfThen()
Dim weeks As String
weeks = InputBox("How many weeks are in a year?", "Quiz")
If weeks <> 52 Then MsgBox "Try Again"
End Sub
The SimpleIfThen procedure stores the user’s answer in the variable named weeks. The variable’s value is then compared to the number 52. If the result of the comparison is true (that is, if the value stored in the variable weeks is not equal to 52), Visual Basic will display the message “Try Again.”
5. Run the SimpleIfThen procedure and enter a number other than 52.
6. Rerun the SimpleIfThen procedure and enter the number 52.
When you enter the correct number of weeks, Visual Basic does nothing. The procedure simply ends. It would be nice to display a message when the user guesses right.
7. Enter the following instruction on a separate line before the End Sub keywords:
If weeks = 52 Then MsgBox "Congratulations!"
8. Run the SimpleIfThen procedure again and enter 52.
When you enter the correct answer, Visual Basic does not execute the statement MsgBox “Try Again.” When the procedure is executed, the statement to the right of the Then keyword is ignored if the result from evaluating the supplied condition is false. As you recall, a VBA procedure can call another procedure. Let’s see whether it can also call itself.
9. Modify the first If statement in the SimpleIfThen procedure as follows:
If weeks <> 52 Then MsgBox "Try Again": SimpleIfThen
We added a colon and the name of the SimpleIfThen procedure to the end of the existing If…Then statement. If the user enters the incorrect answer, he will see a message, and as soon as he clicks the OK button in the message box, the input box will appear again and he will get another chance to supply the correct answer. The user will be able to keep on guessing for a long time. In fact, he won’t be able to exit the procedure gracefully until he supplies the correct answer. If he clicks Cancel, he will have to deal with the unfriendly error message “Type mismatch.” You saw in the previous chapter how to use the On Error GoTo labelname statement to go around the error, at least temporarily until you learn more about error handling in Chapter 9. For now, you may want to revise your SimpleIfThen procedure as follows:
Sub SimpleIfThen()
Dim weeks As String
On Error GoTo VeryEnd
weeks = InputBox("How many weeks are in a year:", "Quiz")
If weeks <> 52 Then MsgBox "Try Again": SimpleIfThen
If weeks = 52 Then MsgBox "Congratulations!"
VeryEnd:
End Sub
10. Run the SimpleIfThen procedure a few times by supplying incorrect answers. The error trap that you added to your procedure allows the user to quit guessing without having to deal with the ugly error message.
|
Two Formats for the |
The If…Then statement has two formats—single line and multiline. The single-line format is good for short or simple statements like:
If secretCode <> 01W01 Then MsgBox "Access denied"
Or
If secretCode = 01W01 Then alpha = True : beta = False
Here, secretCode, alpha, and beta are the names of variables. In the first example, Visual Basic displays the message “Access denied” if the value of secretCode is not equal to 01W01. In the second example, Visual Basic sets the value of alpha to true and beta to false when the secretCode variable is equal to 01W01. Notice that the second statement to be executed is separated from the first by a colon. The multiline If…Then statement is clearer when there are more statements to be executed when the condition is true or when the statement to be executed is extremely long, as in the following example:
If ActiveSheet.Name = "Sheet1" Then
ActiveSheet.Move after:= Sheets(Worksheets.Count)
End If
Here, Visual Basic examines the active sheet name. If it is Sheet1, the condition ActiveSheet.Name = "Sheet1" is true, and Visual Basic proceeds to execute the line following the Then keyword. As a result, the active sheet is moved to the last position in the workbook.
If Block Instructions and Indenting |
|||
To make the |
|||
NOTE |
|
||
|
In the |
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 multiline. The single-line format is as follows:
If condition Then statement1 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 multiline 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 multiline (block) If…Then…Else statement ends with the End If keywords. Use the indentation shown in the previous section to make this block structure easier to read. Here’s a code example that uses the foregoing syntax:
If ActiveSheet.Name = "Sheet1" Then
ActiveSheet.Name = "My Sheet"
MsgBox "This sheet has been renamed."
Else
MsgBox "This sheet name is not default."
End If
If the condition (ActiveSheet.Name = "Sheet1") 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. Let’s look at the complete procedure example.
|
Hands-On 5.3. Writing a VBA Procedure with an |
1. Insert a new module into the Decisions (Chap05_ExcelPrimer.xlsm) project.
2. Change the module name to IfThenElse.
3. Enter the following WhatTypeOfDay procedure and then run it:
Sub WhatTypeOfDay()
Dim response As String
Dim question As String
Dim strmsg1 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/2015)"
strmsg1 = "weekday"
strmsg2 = "weekend"
response = InputBox(question)
myDate = Weekday(CDate(response))
If myDate >= 2 And myDate <= 6 Then
MsgBox strmsg1
Else
MsgBox strmsg2
End If
End Sub
The foregoing 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. The day of the week constants are listed in 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 2 (>=2) and less than or equal to 6 (<=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 |
||
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.
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 ElseIf clause. Using the If…Then…ElseIf statement, you can supply more conditions to evaluate than is possible with the If…Then…Else statement discussed earlier.
Here’s the syntax of the If…Then…Else statement:
If condition1 Then
statements to be executed if condition1 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. 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 code 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 (ActiveCell.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…ElseIf statement works in a complete procedure.
|
Hands-On 5.4. Writing a VBA Procedure with an |
1. Insert a new module into the current VBA project.
2. Rename the module IfThenElseIf.
3. Enter the following WhatValue procedure:
Range("A1").Select
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
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 "^+y", "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 Developer | Macros in the Microsoft Excel window.
5. Now switch to the Microsoft Excel window and activate Sheet2.
6. Type 0 (zero) in cell A1 and press Enter. Then press Ctrl+Shift+Y.
7. Visual Basic calls the WhatValue procedure and enters “zero” in cell B1.
8. 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.”
9. 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 word “negative” in cell B1.
10. 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 the word “text.” To make the WhatValue procedure smarter, you need to learn how to make more complex decisions by using nested If…Then statements.
You can make more complex decisions in your VBA procedures by placing an If…Then or If…Then…Else statement inside another If…Then or If…Then…Else statement.
Structures in which an If statement is contained inside another If block are referred to as nested If statements. The following TestConditions procedure is a revised version of the WhatValue procedure created in the previous section. The WhatValue procedure has been modified to illustrate how nested If…Then statements work.
Sub TestConditions()
Range("A1").Select
If IsEmpty(ActiveCell) Then
MsgBox "The cell is empty."
Else
If IsNumeric(ActiveCell.Value) Then
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
Else
ActiveCell.Offset(0, 1).Value = "text"
End If
End If
End Sub
To make the TestConditions procedure easier to understand, each If…Then statement is shown with different formatting. You can now clearly see that the procedure uses three If…Then blocks. The first If block (in bold) checks whether the active cell is empty. If this is true, the message is displayed, and Visual Basic skips over the Else part until it finds the matching End If. This statement is located just before the End Sub keywords. If the active cell is not empty, the IsEmpty(ActiveCell) condition returns false, and Visual Basic runs the single underlined If block following the Else formatted in bold. This (underlined) If…Then…Else statement is said to be nested inside the first If block (in bold). This statement checks if the value of the active cell is a number. Notice that this is done with the help of another built-in function—IsNumeric. If the value of the active cell is not a number, the condition is false, so Visual Basic jumps to the statement following the underlined Else and enters “text” in cell B1. However, if the active cell contains a number, Visual Basic runs the double-underlined If block, evaluating each condition and making the appropriate decision. The first If block (in bold) is called the outer If statement. This outer statement contains two inner If statements (single and double underlined).
To avoid complex nested If statements that are difficult to follow, you can use the Select Case statement instead. The syntax of this statement is as follows:
Select Case testexpression
Case expressionlist1
statements if expressionlist1 matches testexpression
Case expressionlist2
statements if expressionlist2 matches testexpression
Case expressionlistN
statements if expressionlistN matches testexpression
Case Else
statements to be executed if no values match testexpression
End Select
You can place any number of Case clauses to test between the keywords Select Case and End Select. The Case Else clause is optional. Use it when you expect that there may be conditional expressions that return false. In the Select Case statement, Visual Basic compares each expressionlist with the value of testexpression.
Here’s the logic behind the Select Case statement. When Visual Basic encounters the Select Case clause, it makes note of the value of testexpression. Then it proceeds to test the expression following the first Case clause. If the value of this expression (expressionlist1) matches the value stored in testexpression, Visual Basic executes the statements until another Case clause is encountered and then jumps to the End Select statement. If, however, the expression tested in the first Case clause does not match testexpression, Visual Basic checks the value of each Case clause until it finds a match. If none of the Case clauses contain the expression that matches the value stored in testexpression, Visual Basic jumps to the Case Else clause and executes the statements until it encounters the End Select keywords. Notice that the Case Else clause is optional. If your procedure does not use Case Else and none of the Case clauses contain a value matching the value of testexpression, Visual Basic jumps to the statements following End Select and continues executing your procedure.
Let’s look at an example of a procedure that uses the Select Case statement. In Chapter 4, you learned quite a few details about the MsgBox function, which allows you to display a message with one or more buttons. You also learned that the result of the MsgBox function can be assigned to a variable. Using the Select Case statement, you can now decide which action to take based on the button the user pressed in the message box.
|
Hands-On 5.5. Writing a VBA Procedure with a |
1. Insert a new module into the current VBA project.
2. Rename the new module SelectCase.
3. Enter the following TestButtons procedure:
Sub TestButtons()
Dim question As String
Dim bts As Integer
Dim myTitle As String
Dim myButton As Integer
question = "Do you want to open a new workbook?"
bts = vbYesNoCancel + vbQuestion + vbDefaultButton1
myTitle = "New Workbook"
myButton = MsgBox(prompt:=question, buttons:=bts, title:=myTitle)
Select Case myButton
Case 6
Workbooks.Add
Case 7
MsgBox "You can open a new book manually later."
Case Else
MsgBox "You pressed Cancel."
End Select
End Sub
The first part of the TestButtons procedure displays a message with three buttons: Yes, No, and Cancel. The value of the button selected by the user is assigned to the variable myButton. If the user clicks Yes, the variable myButton is assigned the vbYes constant or its corresponding value—6. If the user selects No, the variable myButton is assigned the constant vbNo or its corresponding value—7. Lastly, if Cancel is pressed, the contents of the variable myButton will equal vbCancel, or 2. The Select Case statement checks the values supplied after the Case clause against the value stored in the variable myButton. When there is a match, the appropriate Case statement is executed.
The TestButtons procedure will work the same if you use the constants instead of button values:
Select Case myButton
Case vbYes
Workbooks.Add
Case vbNo
MsgBox "You can open a new book manually later."
Case Else
MsgBox "You pressed Cancel."
End Select
You can omit the Else clause. Simply revise the Select Case statement as follows:
Select Case myButton
Case vbYes
Workbooks.Add
Case vbNo
MsgBox "You can open a new book manually later."
Case vbCancel
MsgBox "You pressed Cancel."
End Select
4. Run the TestButtons procedure three times, each time selecting a different button.
Sometimes a decision is made based on a relational operator, listed in Table 5.1, such as whether the test expression is greater than, less than, or equal to. The Is keyword lets you use a conditional expression in a Case clause. The syntax for the Select Case clause using the Is keyword is shown here:
Select Case testexpression
Case Is condition1
statements if condition1 is True
Case Is condition2
statements if condition2 is True
Case Is conditionN
statements if conditionN is True
End Select
Although using Case Else in the Select Case statement isn’t required, it’s always a good idea to include one, just in case the variable you are testing has an unexpected value. The Case Else statement is a good place to put an error message. For example, let’s compare some numbers:
Select Case myNumber
Case Is <=10
MsgBox "The number is less than or equal to 10."
Case 11
MsgBox "You entered eleven."
Case Is >=100
MsgBox "The number is greater than or equal to 100."
Case Else
MsgBox "The number is between 12 and 99."
End Select
Assuming that the variable myNumber holds 120, the third Case clause is true, and the only statement executed is the one between the Case Is >=100 and the Case Else clause.
In the preceding example you saw a simple Select Case statement that uses one expression in each Case clause. Many times, however, you may want to specify a range of values in a Case clause. Do this by using the To keyword between the values of expressions, as in the following example:
Select Case unitsSold
Case 1 To 100
Discount = 0.05
Case Is <= 500
Discount = 0.1
Case 501 To 1000
Discount = 0.15
Case Is > 1000
Discount = 0.2
End Select
Let’s analyze the foregoing Select Case block with the assumption that the variable unitsSold currently holds the value 99. Visual Basic compares the value of the variable unitsSold with the conditional expression in the Case clauses. The first and third Case clauses illustrate how to use a range of values in a conditional expression by using the To keyword. Because unitsSold equals 99, the condition in the first Case clause is true; thus, Visual Basic assigns the value 0.05 to the variable Discount. How about the second Case clause, which is also true? Although it’s obvious that 99 is less than or equal to 500, Visual Basic does not execute the associated statement Discount = 0.1. The reason for this is that once Visual Basic locates a Case clause with a true condition, it doesn’t bother to look at the remaining Case clauses. It jumps over them and continues to execute the procedure with the instructions that may be following the End Select statement.
You may specify multiple conditions within a single Case clause by separating each condition with a comma, as shown in the following code example:
Select Case myMonth
Case "January", "February", "March"
Debug.Print myMonth & ": 1st Qtr."
Case "April", "May", "June"
Debug.Print myMonth & ": 2nd Qtr."
Case "July", "August", "September"
Debug.Print myMonth & ": 3rd Qtr."
Case "October", "November", "December"
Debug.Print myMonth & ": 4th Qtr."
End Select
|
Multiple Conditions with the |
The commas used to separate conditions within a Case clause have the same meaning as the OR operator used in the If statement. The Case clause is true if at least one of the conditions is true.
Nesting means placing one type of control structure inside another control structure. You will see more nesting examples with the looping structures discussed in Chapter 7.
The SimpleIfThen procedure that you worked with earlier 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 (listed in Table 5.2 at the beginning of this chapter). Here’s the syntax with the AND operator:
If condition1 AND condition2 Then statement
In the foregoing syntax, both condition1 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 either of these conditions is 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 condition1 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 this 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.
|
Hands-On 5.6. Writing a VBA Procedure with Multiple Conditions |
1. Enter the following procedure in the IfThen module of the Decisions (Chap05_ExcelPrimer.xlsm) 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"
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
If price = 7 AND units < 50 Then
Range("A4").Value = strmsg1 & 50 - units & " unit(s)."
End If
If price <> 7 AND units >= 50 Then
Range("A4").Value = strmsg2
End If
If price <> 7 AND units < 50 Then
Range("A4").Value = "You didn't meet the criteria."
End If
End Sub
The IfThenAnd procedure just shown 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 Microsoft Excel window.
2. Switch to the Microsoft Excel application window, and choose Developer | Macros.
3. In the Macro dialog box, select the IfThenAnd macro and click the Options button.
4. While the cursor is blinking in the Shortcut key box, press Shift+I to assign the shortcut key Ctrl+Shift+I to your macro, and then click OK to exit the Macro Options dialog box.
5. Click Cancel to close the Macro dialog box.
6. Enter the sample data in a worksheet as shown in Figure 5.1.
FIGURE 5.1. Sample test data in a worksheet
7. Press Ctrl+Shift+I to run the IfThenAnd procedure.
8. Change the values of cells B1 and B2 so that every time you run the procedure, a different If…Then statement is true.
To get more practice with the Select Case statement, let’s use it in a function procedure. As you recall from Chapter 4, function procedures allow you to return a result to a subroutine. Suppose a subroutine has to display a discount based on the number of units sold. You can get the number of units from the user and then run a function to figure out which discount applies.
|
Hands-On 5.7. Writing a Function Procedure with a |
1. Enter the following subroutine in the SelectCase module:
Sub DisplayDiscount()
Dim unitsSold As Integer
Dim myDiscount As Single
unitsSold = InputBox("Enter the number of units sold:")
myDiscount = GetDiscount(unitsSold)
MsgBox myDiscount
End Sub
2. Enter the following function procedure:
Function GetDiscount(unitsSold As Integer)
Select Case unitsSold
Case 1 To 200
GetDiscount = 0.05
Case Is <= 500
GetDiscount = 0.1
Case 501 To 1000
GetDiscount = 0.15
Case Is > 1000
GetDiscount = 0.2
End Select
End Function
3. Place the cursor anywhere within the code of the DisplayDiscount procedure and press F5 to run it. Run the procedure several times, entering values to test each Case statement.
The DisplayDiscount procedure passes the value stored in the variable unitsSold to the GetDiscount function. When Visual Basic encounters the Select Case statement, it checks whether the value of the first Case clause expression matches the value stored in the unitsSold parameter. If there is a match, Visual Basic assigns a 5% discount (0.05) to the function name, and then jumps to the End Select keywords. Because there are no more statements to execute inside the function procedure, Visual Basic returns to the calling procedure—DisplayDiscount. Here it assigns the function’s result to the variable myDiscount. The last statement displays the value of the retrieved discount in a message box.
Conditional statements, which were introduced in this chapter, let you control the flow of your procedure. By testing the truth of a condition, you can decide which statements should be run and which should be skipped over. In other words, instead of running your procedure from top to bottom, line by line, you can execute only certain lines. If you are wondering what kind of conditional statement you should use in your VBA procedure, here are a few guidelines:
• If you want to supply only one condition, the simple If…Then statement is the best choice.
• If you need to decide which of two actions to perform, use the If…Then…Else statement.
• If your procedure requires two or more conditions, use the If…Then…ElseIf or Select Case statements.
• If your procedure has a great number of conditions, use the Select Case statement. This statement is more flexible and easier to comprehend than the If…Then…ElseIf statement.
Some decisions have to be repeated. For example, you may want to repeat the same actions for each cell in a worksheet or each sheet in a workbook. The next chapter teaches you how to perform the same steps over and over again.
