Excel VBA Select Case Statement

Basic Description

Another way to select a value from a list of possible values is with a Select Case statement. For example, suppose you had three colors, 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
MsgBox “Red”
ElseIf User_Choice = “G” Then
MsgBox “Green”
ElseIf User_Choice = “B” Then
MsgBox “Blue”
Else
MsgBox “None”
End If

But you can use Select Case for this instead. Examine the following:

Select Case User_Choice
Case “R”
MsgBox “Red”
Case “G”
MsgBox “Green”
Case “B”
MsgBox “Blue”
Case Else
MsgBox “None”
End Select

 

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

 

Select Case Practice

Add another name to your spreadsheet from practice 1 and practice 2. This time, type the name in cell C1. In cell C2, enter a score. What we’ll do is to put a grade in cell C3 and a some text in cell C4. We’ll use Select Case. Add another Sub to your code. Call it SelectCaseTest. We start the coding the same as before:

Dim score As Integer
score = Range(“B1”).Value

We then start our Select Case:

Select Case score

The Select Case will examine what is in the variable called score. The first Case to evaluate as either TRUE or FALSE is this:

Case 0 To 35

This says, “is it the Case that score has a value from 0 to 35?”.

If the score is indeed 0 To 35 then we can execute some code:

Range(“C1”).Value = “F”
Range(“D1”).Value = “Terrible – needs attention”

The first two lines are the same as before. The third line puts the text “Terrible – needs attention” in the cell next to the grade. We can fill out the possible score values in the same way as for the first Case. Here’s the full code:

Select Case1

 

Select Case Practice

Try it out. Put another button on your form. Select your new Sub from the Assign Macro dialogue box. Enter a name in cell A2. Enter a score in cell B2.  Click your button and you should go from this:

Select Case practice

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment