The Select Case structure

The Select Case structure is useful for decisions involving three or more options (although it also works with two options, providing an alternative to the If-Then-Else structure).

The syntax for the Select Case structure follows:

Select Case testexpression [Case expressionlist-n

[elsestatements]] End Select

Don't be scared off by this official syntax. Using the Select Case structure is quite easy.

A Select Case example

The following example shows how to use the Select Case structure. This also shows another way to code the examples presented in the previous section:

Sub

ShowDiscount3()

Dim Quantity As Integer

Dim Discount As Double

Quantity = InputBox("Enter Quantity: ")

Select Case Quantity

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

In this example, the Quantity variable is being evaluated. The routine is checking for four different cases (0 to 24, 25 to 49, 50 to 74, and 75 or greater).

Any number of statements can follow each Case statement, and they all are executed if the case is true. If you use only one statement, as in this example, you can put the statement on the same line as the Case keyword, preceded

by a colon — the VBA statement separator character. In my opinion, this makes the code more compact and a bit clearer. Here's how the routine looks using this format:

Sub

ShowDiscount4 ()

Dim Quantity As Integer

Dim Discount As Double

Quantity = InputBox("Enter Quantity: ")

Select Case Quantity

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

When VBA executes a Select Case structure, the structure is exited as soon as VBA finds a true case.

When VBA executes a Select Case structure, the structure is exited as soon as VBA finds a true case.

A nested Select Case example

As demonstrated in the following example, you can nest Select Case structures. This routine examines the active cell and displays a message describing the cell's contents. Notice that the procedure has three Select Case structures and each has its own End Select statement.

A nested Select Case example

As demonstrated in the following example, you can nest Select Case structures. This routine examines the active cell and displays a message describing the cell's contents. Notice that the procedure has three Select Case structures and each has its own End Select statement.

Sub CheckCell()

Dim Msg As String

Select Case IsEmpty(ActiveCell)

Case True

Msg = "is blank."

Case Else

Select Case ActiveCell.HasFormula

Case True

Msg = "has a formula"

Case False

Select Case IsNumeric(ActiveCell)

Case True

Msg = "has a number"

Case Else

Msg = "has text"

End Select

End Select

End Select

MsgBox "Cell " & ActiveCell.Address & " '

' & Msg

End Sub

This example is available at this book's Web site.

This example is available at this book's Web site.

The logic goes something like this:

1. Find out whether the cell is empty.

2. If it's not empty, see whether it contains a formula.

3. If there's no formula, find out whether it contains a numeric value or text.

When the routine ends, the Msg variable contains a string that describes the cell's contents. As shown in Figure 10-1, the MsgBox function displays that message.

Figure 10-1:

A message displayed by the CheckCell procedure.

Figure 10-1:

A message displayed by the CheckCell procedure.

You can nest Select Case structures as deeply as you need, but make sure that each Select Case statement has a corresponding End Select statement.

As you can see, indenting makes this potentially confusing code much more understandable. If you don't believe me, take a look at the same procedure without any indentation:

Sub CheckCell()

Dim Msg As String

Select Case IsEmpty(ActiveCell)

Case True

Case Else

Select Case ActiveCell.HasFormula Case True

Msg = "has a formula" Case False

Select Case IsNumeric(ActiveCell) Case True

Msg = "has a number"

Case Else

End Select End Select End Select

MsgBox "Cell " & ActiveCell.Address & " " & Msg End Sub

Fairly incomprehensible, eh?

0 0

Post a comment