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

VBA's IIf Function

VBA offers an alternative to the If-Then construct: the IIf function. This function takes three arguments and works much like Excel's IF worksheet function. The syntax is

IIf(expr, truepart, falsepart)

expr: (Required) Expression you want to evaluate.

truepart: (Required) Value or expression returned if expr is True.

falsepart: (Required) Value or expression returned if expr is False.

The following instruction demonstrates the use of the IIf function. The message box displays Zero if cell A1 contains a zero or is empty and displays Nonzero if cell A1 contains anything else.

MsgBox IIf(Range("Al") = 0, "Zero", "Nonzero")

It's important to understand that the third argument (falsepart) is always evaluated, even if the first argument (expr) is True. Therefore, the following statement will generate an error if the value of n is 0 (zero):

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 will always be 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.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 GreetUser()

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 GreetUser()

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 statement-separator 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

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

Select Case structures can also be nested. The following procedure, for example, tests for Excel's window state (maximized, minimized, or normal) and then displays a message describing the window state. If Excel's window state is normal, the procedure tests for the window state of the active window and then displays another message.

Sub AppWindow()

Select Case Application.WindowState

Case xlMaximized: MsgBox "App Maximized" Case xlMinimized: MsgBox "App Minimized" Case xlNormal: MsgBox "App Normal"

Select Case ActiveWindow.WindowState

Case xlMaximized: MsgBox "Book Maximized" Case xlMinimized: MsgBox "Book Minimized" Case xlNormal: MsgBox "Book Normal" End Select End Select End Sub

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 AppWindow()

Select Case Application.WindowState Case xlMaximized: MsgBox "App Maximized" Case xlMinimized: MsgBox "App Minimized" Case xlNormal: MsgBox "App Normal" Select Case ActiveWindow.WindowState Case xlMaximized: MsgBox "Book Maximized" Case xlMinimized: MsgBox "Book Minimized" Case xlNormal: MsgBox "Book Normal" End Select End Select End Sub

Fairly incomprehensible, eh?

Was this article helpful?

0 0

Post a comment