Select Case Statement in Excel Vba

In this Excel VBA tutorial lesson, you are going to learn how to use the select case statement.

The syntax

The SELECT CASE statement, just like the IF STATMENT, is a condition. Conditions are very useful in programming because they allow the user to execute actions based on specific criteria (that the user defines). The SELECT CASE STATEMENT just like the IF STATEMENT is used when the user wants to check one or more than one condition. This is another method of making a decision based on specific criteria.

SELECT CASE STATEMENT is very flexible and easy to read and use (some people prefer using the SELECT CASE statement instead of the IF statement. The SELECT CASE statement is an alternative to using an IF statement with lots of Else If instructions: the Select command. It is better suited to these sorts of situations.

Select Case
     Do something
     Do something
     Do something
Case Else
      Do something else
End Select

Note: A SELECT CASE statement just like the IF STATEMENT can have as many cases as you like. Case<Test1>, Case<Test2> is the same as of writing Else If in the IF STATEMENT.

Explanation: First, you write SELECT CASE followed by the expression you would like to test. After that, you write a Case followed by a test like Case 1 or Case A. After this, you define what will happen in Case A. To end the SELECT CASE statement, you write End Select.

Follow these steps:

The code

Click on the Developer tab and select "View Code".

View Code Select Case

A new window (Visual Basic Editor) will open which will have a dialog box in the center. You will write the code in the dialog box.

New VBA Code


Write the following line of code in the dialog box.

Sub selectcase ()
Select Case Range ("A1").Value
Case 100
    Range ("B1").Value = 50
Case 150
    Range ("B1").Value = 40
 Case Else
     Range ("B1").Value = 0
End Select
End Sub

After writing the code, close the window by clicking on the cross(x) icon on the upper right side of the screen.

Explanation: In this code we are simply changing the value in cell B if there is a certain value in cell A. For example if the user entered 100 in cell A1 than the value in cell B1 will be 50. Like this, if the user changes the value in cell A1 to 150, then the value in cell B1 would be 40.

"Case Else" means that if none of the above conditions are true (meaning if the values in cell A1 aren't 100, 150) then the value in cell B1 would be 0. End Select is used to end the SELECT CASE statement.

VBA code Select Case

The result

This is the result if the user has entered 100 in cell A1.

result 100 Select Case

This is the result if the user has entered 150 in cell A1.

result 150

This is the result if the user has entered any value in cell A1 except 100 or 150.

Select Case result except

You have now learned how to use a SELECT CASE statement in Excel VBA. By following the steps outlined above, you can easily create a Select Case statement in Excel VBA and use it to automate your decision-making process.