Pin Me

Microsoft Access Case Syntax for VBA

written by: Alan Jones•edited by: Linda Richter•updated: 4/24/2011

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.

  • slide 1 of 5

    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 wikimedia commons, logo, ZyMos 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.

  • slide 2 of 5

    Case Statement Syntax

    The Visual Basic Case syntax is very simple and looks like this:

    Select Case <VarName>

    Case <Value1>

    <Code to run if VarName = Value1>

    Case <Value2>

    <Code to run if VarName = Value2>


    Case Else (optional)

    <Code to run if VarName doesn't equal any of the other values>

    End Select

  • slide 3 of 5

    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.

  • slide 4 of 5

    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


    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.

  • slide 5 of 5

    Image and Resource

    Image: Wikimedia Commons/ZyMos

    Resource: Based on the writer's experience