While not offering the flexibility of If…Then…Else, using a Case statement in your VBA code can be a more efficient way of dealing with multiple scenarios.
When to Use a Case Statement
How can you formulate Microsft Access/Visual Basic Case syntax? If you’re only dealing with a single input variable that can control many
possible outputs depending on its value, then your first instinct can be to use an If statement, but you can improve readability and efficiency of your code by making use of the Case statement.
Let’s say you have a form with an option group on it (the boxes that contain several choices with radio buttons next to them, so you can select a single choice). When you created the group, you gave it a name (let’s assume it’s called optGroup) and each radio button has a value assigned to it. Now add a button to the form, and you want to code that button so that when you click on it, something different happens depending on which option the user has selected. This is where the Case statement comes in.
Case Statement Syntax
The Visual Basic Case syntax is very simple and looks like this:
Select Case
Case
Case
…
Case Else (optional)
<Code to run if VarName doesn’t equal any of the other values>
End Select
Case Statement Example
Let’s go back to our first section, where we had an option group called optGroup. Let’s say it has three radio buttons, which have values 1, 2 & 3. You could then code your button as follows:
intGroup = me!optGroup - This passes the value from the option group into a variable called intGroup
Select Case intGroup
Case 1
msgbox “You chose option 1”
Case 2
msgbox “You chose option 2”
Case 3
msgbox “You chose option 3”
Case Else
msgbox “Please choose an option”
End Select
Note that you should use the Case Else statement depending on what values might be passed to the variable you are working on. With an option group, you can set a default value, which means that apart from in the event of an error, it would be impossible for the variable to have a value that wasn’t 1, 2 or 3. You could always use Case Else to cover the default option you’d like to choose in the event of an error.
Other Uses of the Case Statement
The Case statement doesn’t have to take single values. Using our option group example, we could have the following code.
Select Case intGroup
Case 1, 2
msgbox “You chose one of the first two options”
Case Else
msgbox “You chose option 3 (or something went wrong)”
End Select
An equivalent piece of code would be to replace the line Case 1, 2 with
Case Is <= 2
or
Case 1 To 2
Obviously, you’re not just limited to taking the output of an Option Group; so now that you know how to use it, you can start to make use of the Case statement in VBA.
Image and Resource
Image: Wikimedia Commons/ZyMos
Resource: Based on the writer’s experience
