Integrating Excel and Access

Select Case Statement

The Select Case statement is one of the most important in VBA, in my opinion. If you have ever had multiple If conditions that were difficult to write and even more difficult to read in code, it is likely that you could have benefited from a Select Case statement. This statement bascially evaluates a condition once, determines which cases meet the condition, and runs only that code assigned to the first case. Here is an example that takes input from a user and goes through two Select Case statements to give feedback to her.

Dim x As String
Dim y As Double
x = InputBox("Enter characters in the box", "Input Required")
Select Case IsNumeric(x)
  Case True
     y = CDbl(x)
     Select Case y
       Case 1 To 10
         MsgBox "Your number was between 1 and 10", vbInformation
       Case 20, 40, 60
         MsgBox "Your number was 20, 40, or 60", vbInformation
       Case Is > 10
         MsgBox "Your number was greater than 10", vbInformation
       Case Else
         MsgBox "Your number was less than 1", vbInformation
     End Select
  Case False
     MsgBox "You did not enter a number", vbInformation
End Select

When your run this code, notice that once a condition is met in a Case statement, it exits the statement. You can test this by typing in 20. You get only the message about the number being 20, 40, or 60. Even though that number is also greater than 10, you won't see that message. If you put the Case 20, 40, 60 line after the Case Is > 10 line, it would never run.