Читать книгу Excel 2019 Power Programming with VBA - Michael Alexander, Dick Kusleika - Страница 191

The Select Case construct is useful for choosing among three or more options. This construct also works with two options, and it 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 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.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

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 1 or 7). The procedure then displays an appropriate message.

Sub GreetUser1() Select Case Weekday(Now) Case 1, 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() Select Case Weekday(Now) Case 2, 3, 4, 5, 6 MsgBox "This is not the weekend" Case Else MsgBox "This is the weekend" End Select End Sub

Here's another way to code the procedure, using the To keyword to specify a range of values:

Sub GreetUser3() Select Case Weekday(Now) Case 2 To 6 MsgBox "This is not the weekend" Case Else MsgBox "This is the weekend" End Select End Sub

To demonstrate the flexibility of VBA, here is a final example in which each case is evaluated until one of the expressions evaluates to True:

Sub GreetUser4() Select Case True Case Weekday(Now) = 1 MsgBox "This is the weekend" Case Weekday(Now) = 7 MsgBox "This is the weekend" Case Else MsgBox "This is not the weekend" End Select End Sub

Any number of instructions can be written after each Case statement, and they're all 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 statement-separator character, the colon). This technique makes the code more compact. Here's an 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