MS-Excel / General Formatting

---

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

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.

Note: 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.

Listing shows how you would use Select Case to handle the Frequency argument problem.

A Procedure That Uses Select Case to Test Multiple Values

Function FutureValue4(Rate As Single, Nper As Integer, Pmt As Currency, Frequency As String) As Currency
    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
90 and over	A
Between 80 and 89	B
Between 70 and 79	C
Between 60 and 69	D
Less than 60	F

Listing shows the LetterGrade procedure, which uses a Select Case statement to make the conversion.

Function LetterGrade(rawScore As Integer) As String
    Select Case rawScore
        Case Is < 0
            LetterGrade = "ERROR! Score less than 0!"
        Case Is < 60
            LetterGrade = "F"
        Case Is < 70
            LetterGrade = "D"
        Case Is < 80
            LetterGrade = "C"
        Case Is < 90
            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 whether rawScore is negative and, if so, the function returns an error message. The next Case statement checks to see whether the score is less than 60, and the function returns the letter grade "F" if it is. The next Case statement looks for a score that is less than 70. If we get this far, we already know (thanks to the preceding Case statement) that the score is at least 60. Therefore, this case is really checking to see whether the score is between 60 and 70 (including 60, but not including 70). 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

You can use the RGB (red,green,blue) VBA function anytime 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	Blue	Green	Result
0	0	0	Black
0	0	255	Blue
0	255	0	Green
0	255	255	Cyan
255	0	0	Red
255	0	255	Margenta
255	255	0	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 shows the VBAColor function, which lets you use names (for example, "red" or "blue") rather than cryptic number combinations to set 16 of the most common colors.

A Function That Accepts a Color Name as a String and Returns 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.

The rest of the function uses Case statements to check for the various color names and return the appropriate RGB value. You can use the ColorTester procedure to give VBAColor a whirl. This procedure just formats the font color of the currently selected worksheet cell.

Note: 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.