Integrating Excel and Access

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.