Select Case Examples

These two examples are helpful for VbaExercises' Exercise 10.

When using Visual Basic's Select Case feature, make sure to indent your code so that it is easy to understand. Here are two examples. The difference is that one has very short code for each case statement, and the other has ordinary length code for each case statement.

Notes: This code could easily be replaced by a database lookup (such as in Access), or by a VLOOKUP in Excel. Also, this code assumes that you have already declared the relevant variables, and have set (or passed in) the input values.

---

Short example:

Note that in this example, each case and its code easily fit on one line.

    'lngPresident is the sequence number for the U.S. Presidents,
    'as used by the U.S. Secret Service.
    '1 = George Washington, the first president under the current U.S. Constitution.
    'One president was counted twice, because his terms were not consecutive.

    Select Case lngPresident
      Case Is < 1:  strPresident = "Invalid" 'Before George Washington
                                          'was President
      Case Is < 40: strPresident = "Not entered yet"
      Case 40:      strPresident = "Reagan"
      Case 41:      strPresident = "Bush, Sr."
      Case 42:      strPresident = "Clinton"
      Case Else:    strPresident = "Unknown" 'recent presidents who
                                             'have not been entered yet,
                                             'and future presidents
    End Select

---

Typical example:

Note that in this coding style, the Case statement is indented 2 spaces, instead of the usual 4 spaces.

    'All dates and times are assumed to be those of
    'the national capital of the United States as of the date in question.
    'The national capital of the United States
    'has been the District of Columbia since the late 1700s.
    '
    'FIX-ME:  This code has subtle bugs just before and on each inauguration date.
    '         What happens if the time is just before a new president is sworn in?
    '         What happens if the time is 3:00 PM on the day before a new president is sworn in?
    '         Who was the president after JFK died, but before LBJ was sworn in?

    Select Case varDate
      Case Is < CDate("Mar 4, 1789")
        strPresident = "Invalid"    'Before George Washington
                                    'became President
      Case Is < CDate("Jan 20, 1981")
        strPresident = "Not entered yet"
      Case CDate("Jan 20, 1981") To CDate("Jan 19, 1989")
        strPresident = "Ronald Wilson Reagan"
      Case CDate("Jan 20, 1989") To CDate("Jan 19, 1993")
        strPresident = "George Herbert Walker Bush"
      Case CDate("Jan 20, 1993") To CDate("Jan 19, 2001")
        strPresident = "William Jefferson Clinton"
      Case Else
        strPresident = "Unknown"    'recent presidents who
                                    'have not been entered yet,
                                    'and future presidents
    End Select

---

---

Discussion

---

See also: VbaExercises, VbClassic, VbFlexibleSelectCase