How to use Case Statements in VBA

The VBA Select Case statement is another way to analyze a condition in Excel and can be used to replace the traditional If-Then-Else statement in excel.

The Select Case Statement in VBA is the equivalent to the Switch Case statement in other programming languages.

The traditional IF Then Else statement with a few conditions would look something like this:

The same action can be done with the VBA Case statement avoiding the repetition. The structure is as follows:

Let’s take a few examples and see how this works in practice:

Example 1: VBA Select Case statement to check a String value

We will use the excel VBA Case statement to check the value of cell C3 and depending on this value, we will write the continent in cell D3.

Open your VB Editor, and let’s start by declaring two String variables:

Let's assign a value to the myCity variable :

The Select Case statement will be as follows:

How this code works:

When you run the code, the first condition to be checked is the first one:

Is the value of cell C3 “Italy”

If this condition is true, the rest of the conditions will not be tested. The macro will exit the Excel Case statement and the value that will be assigned to cell D3 will be “Europe”:

If the value of the cell C3 is not Italy, the next condition will be checked:

Is the value of Cell C3 “Morocco”

Again if this condition is met, the rest of the code will not be checked and the block of code under the second condition will run.

If the condition is not met VBA will continue to check each condition in order and if none of them are true, it will run the code under the VBA Case Else statement.

This block of code will only run if none of the above conditions are met.

It is NOT mandatory to have a Case Else block but it is always a good practice so you know that your Case statements were tested successfully but none of them were true.

In our example above if none of the conditions are met, in cell D3 will be written:

"The continent for this country is unknown"

To check this remove the Case Else block and rerun the code. Notice no value will be assigned to cell D3.

You can test different conditions with the VBA Select-Case statement, not limited to strings, please see a few examples below:

Example 2: VBA Select Case statement to check numbers

We can use Case 5 or Case Is =5, they are exactly the same.

Example 3: VBA Select Case statement to check multiple values at once

We can also check multiple values at once:

Example 4: VBA Select Case statement to check the value is within a range

We can use the excel VBA Case statement to check a range of numbers:

If you would like to read more about this topic, check out the Microsoft guide to using it here:

Select Case statement (VBA) | Microsoft Docs

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit