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 Switch function.

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

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

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

I'll take a free VBA Developer Kit

The value user_input is tested against each expression (or case) in succession. Once a true expression is found, the code under that case is run and the 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 Your input is greater than 500.

When no expression is true

Sometimes no expression is true. This is the situation for our above code when the user enters 500. Because no statement tests equality for that integer, it will never produce a MsgBox. To always run some kind of code, you can append an 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.

The VBA 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 Your input is ... is output from the Switch function and fed into the message box.

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, our_output, before being displayed by the message box

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 (500 again for us here). In that case, the VBA Switch function will return Null. This can wreak some serious havoc on your code if you haven’t prepared for it! For instance, our_output has been typed as 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 Switch or 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.

Default Outputs

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 Else command).

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 our_output in every case using Select Case:

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 Switch function. 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.

Conclusion

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.