VBA Blogs: Selecting The Right Case

This month, we’re going to talk about the Select Case statement. This week we’re going to introduce the Select Case statement and how it works, then next week we’ll modify it to make it more flexible, and lastly, we’ll answer a client question about the statement’s efficiency in the last VBA blog this month.

The Select Case statement in VBA runs one of a group of statements, depending on the value that has been fed into it. A good analogy is that it is like a VLOOKUP – it searches through a list to find the value that matches, then it reports back on it.

The syntax to use Select Case is as follows:

The idea is, the code has been given the variable “testvalue” as the value it is looking for, and it will compare that value to the variables value1, value2 and value3.  If it matches, it will proceed to run the code that sits between the Case that matches and either the next Case statement or the End Select statement.

For example, we could use the following code to run one of the following scripts:

Note that if our value for “daysinyear” does not match any of the cases we’ve provided, then none of the cases will be run. Maybe next week, we’ll can take a look at some different ways we can amend our statements to give us more flexibility and overcome that problem. See you then!