Choosing Actions with Select Case

As you saw earlier, one way to check an expression against a range of possible values is by using If.. .Then.. .ElseIf as you did in Listing 3.3. This method has the advantage of being able to check expressions containing unrelated variables. Most of the time, however, you only need to check for a range of possible values related to a single variable. For this purpose, Select Case is a better choice. The general syntax of Select Case is as follows:

Select Case YourExpression [Case expression1]

[statements1] [Case expression2]

[statements2] [Case expressionn]

[statementsn] [Case Else]

[statementselse]] End Select

When Select Case finds a match to YourExpression in one of the expressions following a Case statement, Select Case executes the statement block associated with the Case statement. After it executes the statement block, program control resumes with the statement that follows the End Select statement. Note that if you are coming from a different programming language, this behavior is different from what you may be used to. Select Case doesn't evaluate any other Case statements after a match has been found. The optional Case Else statement acts as a default case and executes any code that you want to run if it doesn't find a match.

Select Case is quite flexible in how it interprets the Case expressions. You can specify individual values or ranges of values and use comparison operators. Listing 3.5 provides an example of this.

Listing 3.5: Select Case Example

Sub TestChooseActivity()

Debug.Print ChooseActivity(25) Debug.Print ChooseActivity(34) Debug.Print ChooseActivity(35) Debug.Print ChooseActivity(65) Debug.Print ChooseActivity(66) Debug.Print ChooseActivity(75) Debug.Print ChooseActivity(95) End Sub

Function ChooseActivity(Temperature As Integer) As String Dim sActivity As String

Select Case Temperature Case Is < 32

sActivity = "Snowmobiling" Case 33, 35 To 45

sActivity = "Housework" Case 34

sActivity = "Snowball Fight" Case 46 To 50, 65, 70 To 72

sActivity = "Clean the Garage" Case 75 To 80

sActivity = "Golf" Case 80 To 100

sActivity = "Waterski" Case Else sActivity = "Take a nap." End Select

ChooseActivity = sActivity End Function

Executing TestChooseActivity produces the following results:


Snowball Fight


Clean the Garage

Take a nap.



Notice in Listing 3.5 that the Case statements use a variety of methods to determine whether there is a match. This listing uses the less than (<) comparison operator, ranges, lists of values, and individual values. This code is also exciting because it demonstrates how you might use a function procedure for something other than a user-defined function. These internal functions demonstrate a much more common use of functions other than as user-defined functions on a worksheet. Although user-defined functions are useful, when I reflect on all of the functions I've coded, I've probably written 50 internal functions for every user-defined function.

0 0

Post a comment