Microsoft Office Tutorials and References
In Depth Information
You will want to become familiar with Select Case . It is simple to use, and it is easier to follow
in your code than an extensive If structure. Similar to If and ElseIf keywords, you use the Case
keyword in a Select Case structure to test for the True evaluation of a particular condition or set
of conditions. You can have as many Case statements as you want, and only the code associated
with the first Case that evaluated to True will be executed.
The best way to understand Select Case is to see it in action with a few examples. The following
macro named WeekdayTestSelectCase is actually the previous WeekdayTest macro, which
accomplishes the same result, but uses Select Case instead of If…Then…ElseIf :
Sub WeekdayTestSelectCase()
Select Case Weekday(VBA.Date)
Case 2 ‘Monday
MsgBox “Ugghhh - - Back to work.”, , “Today is Monday”
Case 3 ‘Tuesday
MsgBox “At least it’s not Monday anymore!”, , “Today is Tuesday”
Case 4 ‘Wednesday
MsgBox “Hey, we’re halfway through the work week!”, , “Today is Wednesday”
Case 5 ‘Thursday
MsgBox “Looking forward to the weekend.”, , “Today is Thursday”
Case 6 ‘Friday
MsgBox “Have a nice weekend!”, , “Today is Friday!”
Case 1, 7 ‘Saturday or Sunday
MsgBox “Hey, it’s currently the weekend!”, , “Today is a weekend day!”
End Select
End Sub
You’ll notice less redundancy of each condition (each Case ), because the primary item of interest,
Weekday(VBA.Date) , needs to be named only once in the Select Case statement, instead of in
every ElseIf statement. Also, each Case is very clear, and the entire macro is just easier to read.
A useful tactic with Select Case is the ability to group several different conditions into a single
Case if it satisfies a particular test. For example, if your company operates its budget on a calendar
year basis, that means the months of January, February, and March belong to Quarter 1; April,
May, and June belong to Quarter 2, and so on.
With Select Case , you can group different conditions into the same Case in order to arrive at a
common result. It is not just that January has a one-to-one association with Quarter 1, because the
months of February and March also comprise Quarter 1. If you want to produce a message box that
displays the current Quarter, this macro shows how to group the months into cases.
Sub CurrentQuarter()
Select Case Month(VBA.Date)
Case 1 To 3: MsgBox “Quarter 1”
Case 4 To 6: MsgBox “Quarter 2”
Search JabSto ::

Custom Search