Select Case Statement

To avoid complex nested If statements that are difficult to follow, you can use the Select Case statement instead. The syntax of this statement is:

Select Case testexpression Case expressionlistl statements if expressionlistl matches testexpression Case expressionlist2

statements if expressionlist2 matches testexpression Case expressionlistN

statements if expressionlistN matches testexpression Case Else statements to be executed if no values match testexpression End Select

You can place any number of cases to test between the keywords Select Case and End Select. The Case Else clause is optional. Use it when you expect that there may be conditional expressions that return false. In the Select Case statement, Visual Basic compares each expressionlist with the value of testexpression.

Here's the logic behind the Select Case statement. When Visual Basic encounters the Select Case clause, it makes note of the value of testexpression. Then it proceeds to test the expression following the first Case clause. If the value of this expression (expressionlistl) matches the value stored in testexpression, Visual Basic executes the statements until another Case clause is encountered and then jumps to the End Select statement. If, however, the expression tested in the first Case clause does not match the testexpression, Visual Basic checks the value of each Case clause until it finds a match. If none of the Case clauses contain the expression that matches the value stored in testexpression, Visual Basic jumps to the Case Else clause and executes the statements until it encounters the End Select keywords. Notice that the Case Else clause is optional. If your procedure does not use Case Else and none of the Case clauses contain a value matching the value of the testexpression, Visual Basic jumps to the statements following End Select and continues executing your procedure.

Let's look at an example of a procedure that uses the Select Case statement. In Chapter 4, you learned about the MsgBox function that allows you to display a message with one or more buttons. You also learned that the result of the MsgBox function can be assigned to a variable. Using the Select Case statement, you can now decide which action to take based on the button the user pressed in the message box.

1. Insert a new module into the current project.

2. Rename the new module SelectCase.

3. Enter the following TestButtons procedure:

Sub TestButtons()

Dim question As String Dim bts As Integer Dim myTitle As String Dim myButton As Integer question = "Do you want to open a new workbook?" bts = vbYesNoCancel + vbQuestion + vbDefaultButton1 myTitle = "New Workbook"

myButton = MsgBox(prompt:=question, buttons:=bts, _ title:=myTitle)

Select Case myButton Case 6

Workbooks.Add Case 7

MsgBox "You can open a new book manually later." Case Else

MsgBox "You pressed Cancel." End Select End Sub

The first part of the TestButtons procedure displays a message with three buttons: Yes, No, and Cancel. The value of the button selected by the user is assigned to the variable myButton.

If the user clicks "Yes," the variable myButton is assigned the vbYes constant or its corresponding value—6. If the user selects "No," the variable myButton is assigned the constant vbNo or its corresponding value—7. Lastly, if Cancel is pressed, the contents of the variable myButton equals vbCancel or 2.

The Select Case statement checks the values supplied after the Case clause against the value stored in the variable myButton. When there is a match, the appropriate Case statement is executed.

The TestButtons procedure will work the same if you use the constants instead of button values:

Select Case myButton Case vbYes

Workbooks.Add Case vbNo

MsgBox "You can open a new book manually later." Case Else

MsgBox "You pressed Cancel." End Select

You can omit the Else clause. Simply revise the Select Case statement as follows:

Select Case myButton Case vbYes

Workbooks.Add Case vbNo

MsgBox "You can open a new book manually later." Case vbCancel

MsgBox "You pressed Cancel." End Select

4. Run the TestButtons procedure three times, each time selecting a different button.

Tip 5-6: Capture Errors with Case Else

Although using Case Else in the Select Case statement isn't compulsory, it's always a good idea to include one, just in case the variable you are testing has an unexpected value. The Case Else clause is a good place to put an error message.

0 0

Post a comment