Making Multiple Decisions

     

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

graphics/tip_icon.gif

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:

  • You can have as many ElseIf conditions as you need.

  • You don't have to use the Else part if you don't need it.

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 
graphics/note_icon.gif

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.

graphics/note_icon.gif

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 .



Absolute Beginner's Guide to VBA
Absolute Beginners Guide to VBA
ISBN: 0789730766
EAN: 2147483647
Year: 2003
Pages: 146

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net