This tutorial explains the difference between the VBA Switch function and the VBA Select Case statement. The VBA Select Case statement is sometimes called Switch Case, which can make distinguishing it from the Switch function rather confusing!
Knowing when to use VBA Switch and when to use VBA Select Case (or Switch Case) can greatly improve your programming skills.
Sometimes all we need is an if-then statement to implement our logic. If-then statements are perfect for simple yes/no boolean questions. However, sometimes our logic requires a multiple yes-no answers, and we might be better able to code these with a
Select Case statement or the implementation of a
These two options usually simplify the code for the programmer (and the poor soul who must debug five years after the code was first written), but they do require a specific logical structure and may not be suitable for all purposes. In fact, sometimes it’s actually necessary to write out explicit if-then statements.
Most VBA programmers I know actually haven’t even heard of the Switch function. The VBA Switch function is definitely less popular than it’s Select Case cousin, but we’re going to discuss both in this tutorial.
- Statements and Functions
- VBA Select Case Statement
- VBA Switch Function
- Knowing which to use
Statements and Functions
The first, and most notable, difference between
Select Case and
Switch is that the former is a statement while the latter is a function. In esssence, in programming, a statement declares something, while a function calculates something.
A statement is kind of like saying “I command you to do some action,” while a function is more like saying “I am giving you some input, so please send me back some output.”
VBA Select Case Statement
The VBA Select Case statement essentially gives the machine a list of options to choose from based on whether or not a particular case is true.
A really simple example might be the following code:
Sub select_case_example() 'Greater 1000 case will not trigger since cases are done in order. 'Must change order if you want to check both conditions 'Include else statement at bottom to capture other conditions Dim our_input As Integer our_input = InputBox("Enter an integer") Select Case our_input Case Is < 500 MsgBox ("Your input is less than 500") Case Is > 500 MsgBox ("Your input is greater than 500") Case Is > 1000 MsgBox ("Your input is greater than 1000") End Select End Sub
Make powerful macros with our free VBA Developer Kit It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.
It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.
Select Case block is exited. That means if two conditions, or cases, are true, only the first one will be executed.
In our example, the second case
Case Is > 500 is true for any number greater than 500, including numbers greater than 1000. That means if we input 1050, we will only see
When no expression is true
Sometimes no expression is true. This is the situation for our above code when the user enters
MsgBox. To always run some kind of code, you can append and
Else to your
Select Case statement before
End Select but after the last case, like in this snippet:
Case Else MsgBox ("You entered a number not caught by any case (500)")
VBA Switch Function
At first, the VBA Switch function seems very similar to the
Select Case statement. The key difference is that
Switch is a function, which means it returns a value whenever it’s executed.
The syntax is
Switch(case1 **condition**, value if case1 is true), and any (reasonable) number of case/value pairs can be strung together. Since everything is separated by a comma, both within each case-value pair and between pairs, keeping track of the order is critical.
You can mimic the select case code we demonstrated above using VBA switch by executing this short macro:
Sub using_switch_to_mimic_select_case() 'Greater 1000 case will not trigger since function resolves in order. 'Must change order if you want to check both conditions. Dim our_input As Integer our_input = InputBox("Enter an integer") MsgBox (Switch(our_input < 500, "Your input is less than 500", our_input > 500, "Your input is greater than 500", our_input > 1000, "Your input is greater than 1000")) End Sub
How is this different? To start, it is written in one line, which can certainly make reading the code many years later much more difficult than
Select Case. On the other hand, it’s written in one line (so cool!), so your code appears much more concise!
Seriously, though. A more important difference is what’s happening in the machinery, so to speak. In
Select Case, the machine checked each case sequentially, and when it found one to be true, it ran the code matching that case. We made a simple message box appear, but you could have written any code.
Switch function, on the other hand, was evaluated first, then the
MsgBox was executed. The Switch function must output some value before the
MsgBox can be run. In this example, the string
We could have used this code block instead and generated the same result:
Sub using_switch_function_result() 'Greater 1000 case will not trigger since function resolves in order. 'Must change order if you want to check both conditions. Dim our_input As Integer Dim our_output As String our_input = InputBox("Enter an integer") our_output = Switch(our_input < 500, "Your input is less than 500", our_input > 500, "Your input is greater than 500", our_input > 1000, "Your input is greater than 1000") MsgBox (our_output) End Sub
Notice how the contents of the switch function is stored in variable,
In either instance, the greater than 1000 case is never triggered. Even though the switch function evaluates the expression first, it still tries to “bin” that solution in the order it appears in the
Switch command. You would need to reorder the arguments if you wanted to properly evaluate both conditions.
Here’s where things start to get fun. You can place singular expressions (like
our_input*2) into the return value for Switch functions if you need some basic calculations! This is kind of like functional programming, which we’ll discuss below. Take a look:
our_output = Switch(our_input < 500, "Your input times two is " & our_input * 2, our_input > 500, "Your input divided by two is " & our_input / 2)
When no expression matches
Just like with our
Select Case example, there may be times when the
Switch function’s inputs are never true (
Null. This can wreak some serious havoc on your code if you haven’t prepared for it! For instance,
String, so there will be a mismatch if you enter 500.
Knowing which to use
Sometimes, as in the above examples, we can use either
Select Case in our macros. The choice is largely left to preference and readability. However, there are times you might want to choose one over the other.
Running Multiple Lines of Code
The first and perhaps most obvious issue is when you need to run multiple lines of code. The
Switch function allows some data manipulation, but it must be writeable in a single line as a single expression. That severely restricts your degree of freedom and adaptability. Fortunately,
Select Case will allow however many lines of code you want.
The arithmetic operations above (divide or multiply by 2) are simple enough to enter on one line, as each is a single expression, but even simple code, like this for-loop, cannot be entered on one line because it is a set of instructions rather than a single instruction:
For i = 0 To 10 j = j + i Next i
In other words, without creating your own function, you can’t run the loop above inside a Switch function, but you can using Select Case.
The default output is another issue. For
Switch, the programmer must account for every possibility or run the risk of facing a return value of
Null. Select Case is much more forgiving in this regard, because there is an explicit and simple way to run default code (other than a null output) when no pre-programmed case is true (the
Need for a single value
The last two points were decided in favor of
Select Case, but don’t count out VBA Switch just yet. When you need a single value,
Switch can certainly be a better option.
Consider when you have static country IDs. For example, you might have this hardcoded into your program:
Sub switch_for_value() Dim vID As Integer Dim our_output As String vID = InputBox("Enter the country ID (1-4)") our_output = Switch(vID = 1, "France", vID = 2, "Croatia", vID = 3, "Belgium", vID = 4, "England") MsgBox (our_output) End Sub
This same code would require you write
Sub sc_is_worse() Dim vID As Integer Dim our_output As String vID = InputBox("Enter the country ID (1-4)") Select Case vID Case 1 our_output = "France" Case 2 our_output = "Croatia" Case 3 our_output = "Belgium" Case 4 our_output = "England" End Select MsgBox (our_output) End Sub
Simple case/value switching is perfect for the aptly named
Select Case is rather verbose and introduces many places to make mistakes in typing out the code.
A single output is also extremely useful if your Switch is embedded in another function. Writing functions inside functions is called, rather uncreatively, functional programming, and it offers a compact way to write code. Switch can be implemented in a piece of functional code whereas Select Case must reside outside functions, as it is a statement itself.
A very simple VBA Switch code block using functional programming might look like this:
Sub functional_prog_switch() s0 = Int(InputBox("Enter one number")) s1 = Int(InputBox("Enter another number")) our_output = Switch(s0 > s1, adder(s0), s0 < s1, adder(s1), s0 = s1, s1) MsgBox (our_output) End Sub Function adder(t0) For i = 0 To t0 num = num + i Next i adder = num End Function
This program just sums the higher input and every number less than it. However, if both numbers are the same, it only outputs the original input. This is a simple functional programming example, but you can see how quickly a Switch could be made more complex by using multiple functions.
If your code’s logic is relatively simple, you can probably get away with using either Switch or Select Case. If you just need a single output value, though, it could be better to use a VBA Switch function instead.
Conversely, if you have complex logic, you will not be able to implement it via the Switch function. Instead, Select Case (or switch case) may work out quite nicely for you. Keep in mind, complex means as little as just two lines of code, since the VBA Switch function can only handle one calculation per case.
The exception, of course, is if you want to step into the world of functional programming. Functional programming allows you to write functions as the output values in
Switch, like we did on this tutorial. Just understand this could quickly make your programs cumbersome and more difficult to debug later.
Looking for more? Check out our cheat sheet if you want to learn more about VBA conditional statements. In it, you’ll find 30 useful VBA tips covering over a dozen VBA conditional and loop topics. You’ll also gain access to over 15 helpful VBA macros demonstrating how to use loops and conditional statements to enhance your macros.
If you haven’t already done so, subscribe to my free wellsrPRO VBA Training Program using the form below. We have some unique macros and tips we only send our subscribers.