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.
Get Integrating Excel and Access now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.