Select Case constructs

The Select Case construct is useful for choosing among three or more options. This construct also works with two options and is a good alternative to If-Then-Else. The syntax for Select Case is as follows:

Select Case testexpression [Case expressionlist-n

[instructions-n]] [Case Else

[default_instructions]] End Select

The following example of a Select Case construct shows another way to code the GreetMe examples that I presented in the preceding section:

Sub GreetMe()

Dim Msg As String Select Case Time Case Is < 0.5

Msg = "Good Morning" Case 0.5 To 0.75

Msg = "Good Afternoon" Case Else

Msg = "Good Evening" End Select MsgBox Msg End Sub

And here's a rewritten version of the Discount example using a Select Case construct. This procedure assumes that Quantity is always an integer value. For simplicity, the procedure performs no error checking.

Sub Discount3()

Dim Quantity As Variant Dim Discount As Double

Quantity = InputBox("Enter Quantity: ") Select Case Quantity Case ""

Exit Sub Case 0 To 24

Discount = 0.l Case 25 To 49

Discount = 0.l5 Case 50 To 74

Discount = 0.25 End Select

MsgBox "Discount: " & Discount End Sub

The Case statement also can use a comma to separate multiple values for a single case. The following procedure uses the VBA WeekDay function to determine whether the current day is a weekend (that is, the Weekday function returns l or 7). The procedure then displays an appropriate message.

Sub GreetUserl()

Select Case Weekday(Now) Case l, 7

MsgBox "This is the weekend" Case Else

MsgBox "This is not the weekend" End Select End Sub

The following example shows another way to code the previous procedure:

Sub GreetUser2()

MsgBox "This is not the weekend" Case Else

MsgBox "This is the weekend" End Select End Sub

Any number of instructions can be written below each Case statement, and they all are executed if that case evaluates to True. If you use only one instruction per case, as in the preceding example, you might want to put the instruction on the same line as the Case keyword (but don't forget the VBA statementseparator character, the colon). This technique makes the code more compact. For example:

Sub Discount3()

Dim Quantity As Variant Dim Discount As Double

Quantity = InputBox("Enter Quantity: ") Select Case Quantity Case "": Exit Sub Case 0 To 24: Discount = 0.1 Case 25 To 49: Discount = 0.15 Case 50 To 74: Discount = 0.2 Case Is >= 75: Discount = 0.25 End Select

MsgBox "Discount: " & Discount End Sub

Tip VBA exits a Select Case construct as soon as a True case is found. Therefore, for maximum efficiency, you should check the most likely case first.

Select Case structures can also be nested. The following procedure, for example, uses the VBA TypeName function to determine what is selected (a range, nothing, or anything else). If a range is selected, the procedure executes a nested Select Case and tests for the number of cells in the range. If one cell is selected, it displays One cell is selected. Otherwise, it displays a message with the number of selected rows.

Sub SelectionType()

Select Case TypeName(Selection) Case "Range"

Select Case Selection.Count Case 1

MsgBox "One cell is selected" Case Else

MsgBox Selection.Rows.Count & " rows" End Select Case "Nothing"

MsgBox "Nothing is selected" Case Else

MsgBox "Something other than a range" End Select End Sub

This procedure also demonstrates the use of Case Else, a catch-all case. You can nest Select Case constructs as deeply as you need, but make sure that each Select Case statement has a corresponding End Select statement.

This procedure demonstrates the value of using indentation in your code to clarify the structure. For example, take a look at the same procedure without the indentations:

Sub SelectionType()

Select Case TypeName(Selection)

Case "Range"

Select Case Selection.Count Case 1

MsgBox "One cell is selected" Case Else

MsgBox Selection.Rows.Count & " rows" End Select Case "Nothing"

MsgBox "Nothing is selected" Case Else

MsgBox "Something other than a range" End Select End Sub

Fairly incomprehensible, eh?

0 0

Post a comment