Microsoft Office Tutorials and References
In Depth Information
Controlling Code Execution
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
Any number of instructions can be written below 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. 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 should check the most likely case first.
Select Case structures can also be nested. The following procedure, for example, uses the
VBA TypeName function to determine what is selected (a range, nothing, or anything else). If a
range is selected, the procedure executes a nested Select Case and tests for the number of
cells in the range. If one cell is selected, it displays One cell is selected . Otherwise, it
displays a message with the number of selected rows.
Sub SelectionType()
Select Case TypeName(Selection)
Case “Range”
Select Case Selection.Count
 
Search JabSto ::




Custom Search