The problem with If...Then...Else is that normally you can make only a single decision. The statement calculates a single logical result and performs one of two actions. However, plenty of situations require multiple decisions before you can decide which action to take. For example, the FutureValue procedure discussed in the preceding section probably should test the Frequency argument to make sure it's either Monthly or Quarterly and not something else. The next few sections show you three solutions to this problem. Using the And and Or Operators One solution to our multiple-decision problem is to combine multiple logical expressions in a single If...Then statement. From Chapter 4, you'll recall that you can combine logical expressions by using VBA's And and Or operators. In our example, we want to calculate the future value only if the Frequency argument is either Monthly or Quarterly . The following If...Then statement uses the And operator to test this: If Frequency = "Monthly" Or Frequency = "Quarterly" Then As shown in Listing 6.3, if Frequency equals either of these values, the entire condition returns True, and the procedure runs the calculation in the usual way. Otherwise, if Frequency doesn't equal either value, then the procedure returns a message to the user . Listing 6.3. A Procedure that Uses the Or Operator to Perform Multiple Logical Tests Function FutureValue2(Rate, Nper, Pmt, Frequency) If Frequency = "Monthly" Or Frequency = "Quarterly" Then If Frequency = "Monthly" Then FutureValue2 = FV(Rate / 12, Nper * 12, Pmt / 12) Else FutureValue2 = FV(Rate / 4, Nper * 4, Pmt / 4) End If Else MsgBox "The Frequency argument must be either " & _ """Monthly"" or ""Quarterly""!" End If End Function Note that this procedure isn't particularly efficient because you end up testing the Frequency argument in two places. However, that just means that this example isn't the best use of the And and Or operators. The overall principle of using these operators to perform multiple logical tests is a useful one, however, and you should keep it in mind when constructing your decision-making code. tip | In Listing 6.3, if Frequency equals either Monthly or Quarterly, the result of the first If...Then...Else is true, and the procedure then executes a second If...Then...Else structure. This is called nesting one control structure within another. This is very common in VBA procedures, but it can also get very confusing very quickly. To help you keep things straight, not only indent the statements within the first If...Then...Else , but double-indent the statements within the second If...Then...Else (refer to Listing 6.3 for an example). | Using Multiple If...Then...Else Statements There is a third syntax for the If...Then...Else statement that lets you string together as many logical tests as you need: If condition1 Then [ condition1 TrueStatements ] ElseIf condition2 Then [ condition2 TrueStatements ] <etc.> Else [ FalseStatements ] End If condition1 | A logical expression. | condition1 TrueStatements | The statements to run if condition1 returns True. | condition2 | A different logical expression. | Condition2 TrueStatements | The statements to run if condition2 returns True. | FalseStatements | The statements to run if both condition1 and condition2 return False. | VBA first tests condition1 . If this returns True, VBA runs the group of statements between If...Then and ElseIf...Then . If it returns False, VBA then tests condition2 . If this test is True, VBA runs the group of statements between ElseIf...Then and Else . Otherwise, VBA runs the statements between Else and End If . Here are two things you should note about this structure: Listing 6.4 shows FutureValue3 , a revised version of FutureValue that makes allowances for an improper Frequency argument. Listing 6.4. A Procedure that Uses Multiple If...Then...Else Statements Function FutureValue3(Rate, Nper, Pmt, Frequency) If Frequency = "Monthly" Then FutureValue3 = FV(Rate / 12, Nper * 12, Pmt / 12) ElseIf Frequency = "Quarterly" Then FutureValue3 = FV(Rate / 4, Nper * 4, Pmt / 4) Else MsgBox "The Frequency argument must be either " & _ """Monthly"" or ""Quarterly""!" End If End Function As before, the If...Then statement checks to see if Frequency equals Monthly and, if it does, calculates the future value accordingly . If it's doesn't, the ElseIf...Then statement checks to see if Frequency equals Quarterly and calculates the future value if the expression returns True. If it returns False, the user entered the Frequency argument incorrectly, so a warning message is displayed. Using the Select Case Statement Performing multiple tests with If...ElseIf is a handy technique ”it's a VBA tool you'll reach for quite often. However, it quickly becomes unwieldy as the number of tests you need to make gets larger. It's okay for two or three tests, but any more than that makes the logic harder to follow. For these situations, VBA's Select Case statement is a better choice. The idea is that you provide a logical expression at the beginning and then list a series of possible results. For each possible result ”called a case ”you provide one or more VBA statements to execute should the case prove to be true. Here's the syntax: Select Case TestExpression Case FirstCaseList [ FirstStatements ] Case SecondCaseList [ SecondStatements ] <etc.> Case Else [ ElseStatements ] End Select | If more than one CaseList contains an element that matches the TestExpression , VBA runs only the statements associated with the CaseList that appears first in the Select Case structure. | TestExpression | This expression is evaluated at the beginning of the structure. It must return a value (logical, numeric, string, and so on). | CaseList | A list of one or more possible results for TestExpression . These results are values or expressions separated by commas. VBA examines each element in the list to see whether one matches the TestExpression . The expressions can take any one of the following forms: Expression Expression To Expression Is LogicalOperator Expression The To keyword defines a range of values (for example, 1 To 10). The Is keyword defines an open -ended range of values (for example, Is >= 100). | Statements | These are the statements VBA runs if any part of the associated CaseList matches the TestExpression . VBA runs the optional ElseStatements if no CaseList contains a match for the TestExpression . | Listing 6.5 shows how you would use Select Case to handle the Frequency argument problem. Listing 6.5. A Procedure that Uses Select Case to Test Multiple Values Function FutureValue4(Rate, Nper, Pmt, Frequency) Select Case Frequency Case "Monthly" FutureValue4 = FV(Rate / 12, Nper * 12, Pmt / 12) Case "Quarterly" FutureValue4 = FV(Rate / 4, Nper * 4, Pmt / 4) Case Else MsgBox "The Frequency argument must be either " & _ """Monthly"" or ""Quarterly""!" End Select End Function A Select Case Example: Converting Test Scores to Letter Grades To help you get a better feel for the Select Case statement, let's take a look at another example that better showcases the unique talents of this powerful structure. Suppose you want to write a procedure that converts a raw test score into a letter grade according to the following table: Raw Score | Letter Grade | 80 and over | A | Between 70 and 79 | B | Between 60 and 69 | C | Between 50 and 59 | D | Less than 50 | F | Listing 6.6 shows the LetterGrade procedure, which uses a Select Case statement to make the conversion. Listing 6.6. A Procedure that Uses Select Case to Convert a Raw Test Score into a Letter Grade Function LetterGrade(rawScore As Integer) As String Select Case rawScore Case Is < 0 LetterGrade = "ERROR! Score less than 0!" Case Is < 50 LetterGrade = "F" Case Is < 60 LetterGrade = "D" Case Is < 70 LetterGrade = "C" Case Is < 80 LetterGrade = "B" Case Is <= 100 LetterGrade = "A" Case Else LetterGrade = "ERROR! Score greater than 100!" End Select End Function The rawScore argument is an integer value between 0 and 100. The Select Case structure first checks to see if rawScore is negative and, if so, the function returns an error message. The next Case statement checks to see if the score is less than 50, and the function returns the letter grade "F" if it is. The next Case statement looks for a score that is less than 60. If we get this far, we already know (thanks to the preceding Case statement) that the score is at least 50. Therefore, this case is really checking to see if the score is between 50 and 60 (including 50, but not including 60). If so, the letter grade "D" is returned. The rest of the Case statements proceed in the same manner. The Case Else checks for a score greater than 100 and returns another error message if it is. Another Example: Taming the RGB Function In Chapter 5, "Working with Objects," I mentioned briefly that you can use the RGB ( red,green,blue ) VBA function any time you need to specify a color for a property. Each of the three named arguments ( red, green , and blue ) are integers between 0 and 255 that determine how much of each component color is mixed into the final color. In the red component, for example, 0 means no red is present, and 255 means that pure red is present. If all three values are the same, you get a shade of gray. Here are some sample values for each component that produce common colors: Red | Green | Blue | Result | | | | Black | | | 255 | Blue | | 255 | | Green | | 255 | 255 | Cyan | 255 | | | Red | 255 | | 255 | Magenta | 255 | 255 | | Yellow | 255 | 255 | 255 | White | However, rather than memorize these combinations, let's put VBA and Select Case to work to make choosing colors easier. Listing 6.7 shows the VBAColor function, which lets you set 16 of the most common colors using names (for example, "red" or "blue") rather than cryptic number combinations. Listing 6.7. A Function That Accepts a Color Name as a String and Return the Corresponding RGB Value Function VBAColor(colorName As String) As Long Select Case LCase(Trim(colorName)) Case "black" VBAColor = RGB(0, 0, 0) Case "white" VBAColor = RGB(255, 255, 255) Case "gray" VBAColor = RGB(192, 192, 192) Case "dark gray" VBAColor = RGB(128, 128, 128) Case "red" VBAColor = RGB(255, 0, 0) Case "dark red" VBAColor = RGB(128, 0, 0) Case "green" VBAColor = RGB(0, 255, 0) Case "dark green" VBAColor = RGB(0, 128, 0) Case "blue" VBAColor = RGB(0, 0, 255) Case "dark blue" VBAColor = RGB(0, 0, 128) Case "yellow" VBAColor = RGB(255, 255, 0) Case "dark yellow" VBAColor = RGB(128, 128, 0) Case "magenta" VBAColor = RGB(255, 0, 255) Case "dark magenta" VBAColor = RGB(128, 0, 128) Case "cyan" VBAColor = RGB(0, 255, 255) Case "dark cyan" VBAColor = RGB(0, 128, 128) End Select End Function Sub ColorTester() ActiveCell.Font.Color = VBAColor("red") End Sub VBAColor takes a single argument, colorName , which is the name of the color you want to work with. Notice how the Select Case statement massages the argument to prevent errors: Select Case LCase(Trim(colorName)) The Trim function removes any extraneous spaces at the beginning and end of the argument, and the LCase function converts colorName to lowercase. This ensures that the function is not case sensitive, which means it doesn't matter whether you send black, BLACK , or Black : the function will still work. | VBA also defines eight color constants that you can use when you just need the basic colors: vbBlack , vbBlue , vbCyan , vbGreen , vbMagenta , vbRed , vbWhite , and vbYellow . | The rest of the function uses Case statements to check for the various color names and return the appropriate RGB values. You can use the ColorTester procedure to give VBAColor a whirl. This procedure just formats the font color of the currently selected worksheet cell . |