Home and Learn - Free Excel VBA Course
Another way to select a value from a list of possible values is with a Select Case statement. For example, suppose you had three colours, Red, Blue and Green. You want to test which value a user has selected. You could use an IF Statement. Like this:
If User_Choice = "R" Then
ElseIf User_Choice = "G" Then
ElseIf User_Choice = "B" Then
But you can use Select Case for this instead. Examine the following:
Select Case User_Choice
A Select Case statement begins with the words Select Case. You then type the thing you're testing for. This can be a variable, a number, text in double quotes, and even a built-in Excel function. For each possible answer, you then have one Case. Each Case is evaluated to TRUE or FALSE. If it's TRUE then the code for that Case gets executed. Only one Case per Select statement will get executed. The whole thing ends with the words End Select.
In the code above, we're saying "If it's the Case that the variable User_Choice contains the letter R Then display a message box saying Red." If it's not TRUE then VBA drops down to the next Case and check if that's TRUE. You can have an optional Case Else to catch anything else that the value at the beginning could be. So if User_Choice does not contain an R, a G or a B then we display a message saying "None".
You can check for more than one value by using the word To. For example, if you want to check a range of ages you can do it like this:
Case 0 To 35
Now, VBA will check if the value is 0 to 35.
You can also check several values at once. Simply separate each one with a comma:
Case 10, 20, 30, 40
VBA will now only see the above Case as TRUE if the value you're selecting for is 10, 20, 30, or 40. Any other values and this Case will evaluate to FALSE.
Let's have a look at a more practical example, though. We'll go back to our student score spreadsheet so that you can get some practice with Select Case in Excel VBA. We'll do that in the next lesson below