VBA Select Case Statement

Associated Files Download Links

In VBA, the Select Case Statement is an alternative to the If-Then statement, allowing you to test if conditions are met, running specific code for each condition. The Select Statement is preferable to the If Statement when there are multiple conditions to process.

Select Case Example

This example prompts the user with a YesNoCancel MessageBox and tests which option the user selected:

vba select case

Below we’ve written out the equivalent using an If Statement instead. You’ll notice that the Case Select Statement involves slightly less typing – this benefit is amplified when testing multiple criteria.

Case Statement Syntax

The Select Case Statement syntax is as follows:

Where:

[Test Expression] – Is the value to evaluate. Usually this is a variable.

[Action if condition n is true] – Is just the code to run if the condition is met (just like with an If Statement)

[Condition n] – Is the condition to test. There are a lot of different ways to test conditions. We will discuss them below.

The Case Statement will execute the code for the FIRST condition that is found to be TRUE.  If no condition is met then no code will be executed, unless the Else clause is added.

Select Case Criteria

Select Cases can be used to evaluate both numerical values and text.  First we will discuss how to use Select Cases to evaluate numerical expressions.

Exact Match – Numbers

You can easily test for an exact match with a Case Statement:

or add commas to test for exact matches with multiple numbers:

Ranges

You can test if a number falls within a range like so:

This procedure will generate a letter score for a student based on their numerical score:

You can also test ranges with the Case Is

Select Case Is

Remember that the Case Statement will only execute code for ONLY the first match.

This procedure will calculate a student’s grade using the Case Is instead of Case To.

Case Else

You can add “Case Else” to the end of your Case Statement to do something if no conditions are met:

See the end of the previous code example to see how Case Else can be used.

Select Case – Text & the Like Operator

So far our Select Case examples have worked only with numbers.  You can also use Select Case statements with text.

Exact Match – Text

You can test if the expression matches an exact phrase like this:

Or use commas to test if the expression exactly matches more than one phrase:

Putting it together looks like:

Upper and Lower Case

By default, VBA is Case Sensitive. This means that VBA considers “Text” different than “text”. To turn case-sensitivity off add Option Compare Text to the top of your module:

This example will make the Case Select case-insensitive when working with text:

Case Like

The Like Operator allows you to make inexact comparisons.  If the text matches, Like returns TRUE, if it doesn’t match it returns FALSE.  This makes the Like operator easy to use with If Statements, however it won’t work as easily with Case Statements.

Case Like – A Failed Test

The following code demonstrates that the Like Operator doesn’t work with Select Case:

Case Like – The Correct Way

However, we can add in the TRUE expression to make the Select Statement work with the Like Operator:

Case – Colon

When using a Case Statement you can add as many lines of code as you’d like to run with each condition.  However, if you only need to run one line of code. You can use a Colon ( : ) to write everything on the same line.

Here is the same student grade example as before, except using a colon to shorten the code:

 

Case Select – And / Or – Multiple Conditions

You can use the And / Or Operators to test additional criteria along with the Select Case.

In this example we are using a Select Case on the variable ‘age’, but we also want to test sex. So we use the And Operator to perform the more complex test:

Nested Case Statements

Just like If Statements, you can nest Case Statements inside each other:

Case Statement vs. If Statement

The more conditions to test, the more useful the Case Statement is compared to an If Statement. Let’s look at an example.

Here is the code required to test if a worksheet name equals a set of values using an If Statement:

Here is the same code using a Select Statement instead:

You can see it’s much easier to use a Select Statement in this scenario. It’s significantly less typing, and it’s much easier to read.

VBA Select Case Examples

Ex 1. Case Statement User Defined Function (UDF)

Let’s replicate our grade calculation example above and create a UDF to calculate a student’s score:

Now we can use Function GetGrade in our Excel worksheet to quickly calculate student grades:

vba case select

 

Ex 2. Test Sheet Name / Loop Case Statement

This code will loop through all worksheets in a workbook, UnProtecting sheets that meet certain criteria:

Ex 3. Select Case – Cell Value

This example will test a student’s score in a cell, outputting the letter grade directly to the cell to the right.

Ex 4. Select Case – Dates

This Case Select example is a Function that tests which quarter a date fall into.

Because it’s a function, you can use it as a function inside Excel:

vba case select date example

Ex. 5 Check if Number is Odd or Even

This example tests if a number is odd or even.

Ex. 6 Test if Date is on Weekday or Weekend

These examples will test if a date falls on a weekday or a weekend.

You may also like some of this related content...

Advertisements
Automate Excel
Left Menu Icon