Branching with Excel VBA

Control structures for branching (conditional execution) allow you to test a condition and, depending on the result of that test, execute one group of statements or another. In VBA, branching is organized using various forms of the If statement and the Select Case statement.

Short Form of If

The simplest short form of the If statement is used to test a single condition and then either execute or skip a statement or a block of statements. The short form of If can be written in a single-line form or a block form.

Single-line form:

If <condition> Then <statement>

Block form:

If <condition> Then
    <statement1>
    <statement2>
    ...
End If

• The condition can be a logical expression that returns True or False, or any arithmetic expression.
• If an arithmetic expression is used, a zero value is equivalent to False, while any nonzero value is equivalent to True.
• If the condition returns False, the statement or block of statements between Then and End If (the body of the short If statement) will not be executed.

Note: When writing the short If statement in a single line, the keywords End If are not used.

Full Form of If

The full form of the If statement is used when there are two alternative blocks of statements, and depending on the result of the condition check, one of them must be executed. This form cannot be written in a single line and always uses the block form:

If <condition> Then
    <blockOfStatements1>
Else
    <blockOfStatements2>
End If

• If the condition is true, the first block (between Then and Else) is executed.
• Otherwise, the second block (between Else and End If) is executed.

TIP

To make the text of a procedure clear and easy to read, it is recommended to use indentation for groups of statements, as shown in the description of their syntax. In VBA there is a convenient tool for changing indentation: pressing the key increases the indentation to the right, while pressing + decreases it.

Sometimes it is necessary to choose one action out of a whole group of actions based on testing several different conditions. For this, you can use a chain of If…Then…ElseIf statements:

If <condition1> Then
    <blockOfStatements1>
ElseIf <condition2> Then
    <blockOfStatements2>
ElseIf <condition3> Then
    <blockOfStatements3>
...
ElseIf <conditionN> Then
    <blockOfStatementsN>
Else
    <blockOfStatementsElse>
End If

Such chains of If…Then…ElseIf statements are very flexible and allow you to solve any problem. However, if the choice among several possibilities is always based on different values of the same expression, it is much more convenient to use the Select Case statement, which is specifically designed for this purpose.

Syntax:

Select Case <testExpression>
    Case <listOfValues1>
        <blockOfStatements1>
    Case <listOfValues2>
        <blockOfStatements2>
    Case <listOfValues3>
        <blockOfStatements3>
    ...
    Case Else
        <blockOfStatementsElse>
End Select

The test expression is evaluated at the start of the Select Case statement. This expression can return a value of any type — logical, numeric, or string.

The list of values can be one or several expressions separated by commas. When the statement is executed, VBA checks whether at least one of the elements in the list matches the value of the test expression. The elements of the value list can take the following forms:

• <expression> — checks whether the test expression is equal to this expression;
• <expression1> To <expression2> — checks whether the test expression lies within the specified range of values;
• Is <logicalOperator> <expression> — the test expression is compared with the specified value using the given logical operator (for example, the condition Is >= 10 is satisfied if the tested value is not less than 10).

If at least one element in the list matches the test expression, the corresponding block of statements is executed, and execution of the Select Case statement ends — the remaining lists of values are not checked. That is, only the first suitable match is found. If none of the lists match, the statements in the Else block are executed (if present).

Examples of Branching Statements

In Determining which interval an entered number belongs to  depending on the value of the entered number, a message is displayed about whether the number belongs to:

• the interval [0, 1];
• the interval (1, 2];
• or neither of these two intervals.

Determining which interval an entered number belongs to

Sub DemoElseIf()
    x = InputBox("Enter a number")
    If 0 <= x And x <= 1 Then
        MsgBox "The number is in the interval [0, 1]"
    ElseIf 1 < x And x <= 2 Then
        MsgBox "The number is in the interval (1, 2]"
    Else
        MsgBox "The number is either negative or greater than 2"
    End If
End Sub

In the example the use of the Select Case statement is demonstrated for displaying a message about which range the entered integer belongs to.

Example of using the Select Case statement

Sub DemoSelect()
    Dim x As Integer
    x = InputBox("Enter an integer")   
    Select Case x
        Case 1
            MsgBox "The number is equal to 1"
        Case 2, 3
            MsgBox "The number is equal to 2 or 3"
        Case 4 To 6
            MsgBox "The number is between 4 and 6"
        Case Is >= 7
            MsgBox "The number is greater than or equal to 7"
    End Select
End Sub