Select Case Constructs

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:

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Select Case testexpression
[Case expressionlist-n
[instructions-n]]
[Case Else
[default_instructions]]
End Select
[/sourcecode]

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.


Case example 1

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
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”
Msg = “Good Evening”
End Select
MsgBox Msg
End Sub
[/sourcecode]


Case example 2

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
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
[/sourcecode]


Case example 3

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub WhenIsIt()
Select Case Weekday(Now)
Case 1, 7
MsgBox “This is the weekend”
Case Else
MsgBox “This is not the weekend”
End Select
End Sub
[/sourcecode]


Case example 4

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
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
[/sourcecode]