Please note javascript is required for full website functionality.
MVP

Blog

VBA Blog: Going Through the Visual Basics – Part 13

16 November 2018

We thought we’d run an elementary series going through the rudiments of Visual Basic for Applications (VBA) as a springboard for newer users.  This blog takes a look at selection control structures.

 

In a programming, a control structure determines the order in which statements are executed.  The selection control structure is used for making decisions and branching statements.  

Here are several examples.

 

SELECT CASE

SELECT CASE is the VBA equivalent to a switch control structure.  A switch statement allows a variable to be tested for equality against a list of values.  Each value is called a case, and the variable being “switched” on is checked for each case:

The SWITCH statement is used as follows: 

Select [ Case ] testexpression 

    [ Case expressionlist 

        [ statements ] ] 

    [ Case Else 

        [ elsestatements ] ] 

End Select 

 

Let’s look at a coding example: 

Sub SWITCHStatement()

    Dim myNumber As Integer

    myNumber = 1

    Select Case myNumber

        Case 0

            Debug.Print "Zero the hero!"

        Case 1

            Debug.Print "Number one!"

        Case 2

            Debug.Print "Number two!"

        Case 3

            Debug.Print "Number three!"

        Case Else

            Debug.Print "Not a podium finish"

    End Select

End Sub

   

IS

SELECT CASE allows the use of the IS keyword to compare values.  If the variable can use comparison operators, then IS is used before the operator.  For example: 

Sub SwitchISStatement()

    Dim myNumber As Integer

    myNumber = 1

    Select Case myNumber

        Case Is < 0

            Debug.Print "Negative"

        Case Is < 10

            Debug.Print "Single Digit Positive Integer"

        Case Is < 100

            Debug.Print "Double Digit Positive Integer"

        Case Else

            Debug.Print "Large number!"

    End Select

End Sub

Note: upon typing an operator (>, < or =) after the keyword CASE, the VBA editor will automatically correct the statement and place the IS keyword after CASE.

 

TO

If the variable assessed can lie within a range, the TO keyword is called upon to denote the range.  For example: 

Sub SwitchTOStatement()

    Dim myNumber As Integer

    myNumber = 1

    Select Case myNumber

        Case Is < 0

            Debug.Print "Negative"

        Case 0 To 9

            Debug.Print "Single Digit Positive Integer"

        Case 10 To 99

            Debug.Print "Double Digit Positive Integer"

        Case Else

            Debug.Print "Large number!"

    End Select

End Sub

 

Combination

Multiple expressions can be used in a single CASE statement for optimum efficiency.  Another example: 

Sub SwitchCombinationStatement()

    Dim myNumber As Integer

    myNumber = 1

    Select Case myNumber

        Case Is < 0

            Debug.Print "Negative"

        Case 1, 2, 3, 4, 5 To 9

            Debug.Print "Single Digit Positive Integer"

        Case 10 To 50, 51, 52, 53, 54, 55 To 99

            Debug.Print "Double Digit Positive Integer"

        Case Else

            Debug.Print "Large number!"

    End Select

End Sub

 

Newsletter