DECISION-MAKING (BRANCHING) MECHANISMS

If ... Then ... Else or Elseif ... End If Structures

The Else or the ElseIf parts are actually optional. If they are left out, then we have the If ... Then ... End If structure.

It is used in the following way:

If (LogicalExpression) Then
    statement(s)
End If

where LogicalExpression is a logical expression that evaluates to either True or False, and statement(s) is a single or a group of statements.

If the LogicalExpression evaluates to True, then the statement(s) part is executed, otherwise the statement(s) part is skipped. In either case program control is passed afterwards to the statement immediately following the If ... Then ... End If structure.

For example, if Score is an integer variable and Passing is a boolean variable, then one can write:

Passing = False
If Score >= 60 Then
    Passing = True
End If

This means that in order to pass the course, a student has to have a score of 60 or higher.

The above code can also be written by using the Else option as:

If Score >= 60 Then
    Passing = True
Else
    Passing = False
End If

So there are two possible outcomes depending on the value of a certain logical expression. This second version is preferred since it is clearer to understand.

Multiple (more than two) branches can be obtained by using the ElseIf option a number of times:

If (LogicalExpression1) Then
    statement1
ElseIf (LogicalExpression2) Then
    statement2
ElseIf (LogicalExpression3) Then
    statement3
... etc.
End If

The last ElseIf part can be replaced by Else without an associated logical expression:

If (LogicalExpression1) Then
    statement1
ElseIf (LogicalExpression2) Then
    statement2
... etc.
Else
    defaultStatement
End If

In this case, if none of the logical expression is evaluated to True, then the defaultStatement will be executed.

In all of the above cases, program control is passed afterwards to the statement immediately after the entire If ... structure.

For example, consider writing a computerized number guessing game in which the program picks a random number between 1 and 10, and the player has 3 chances to take a guess at the number. Each time the player's guess is higher than the answer, the program will respond "too high", and if the number is lower than the answer, the program will respond with "too low", and if the guess is the same as the answer, the program responds with "correct!".

Suppose the player's choice is stored in the variable guess, and the correct number is stored in the variable answer. You could code that as follows:

If guess > answer Then
    MsgBox "Too high!"
End If
If guess < answer Then
    MsgBox "Too low!"
End If
If guess = answer Then
    MsgBox "Correct! You've just won a million dollars!"
End If

The code will do the job, but in an inefficient way. There are three separate If statements involving three separate conditions that must always be tested.

A better way is to use a multiway If-Then-ElseIf-statement to write:

If guess > answer Then
    MsgBox "Too high!"
ElseIf guess < answer Then
    MsgBox "Too low!"
Else
    MsgBox "Correct! You've just won a million dollars!"
End If

Note that not all of the three conditions need to be tested. Since the three choices are mutually exclusive (i.e. one and only one of the conditions can be true), the last condition never needs to be tested.

Here is another example. If Score stores the exam score for a particular student, and Grade is a string variable that stores the letter grade for a student in a course, the grade can be determined by:

If Score >= 90 Then
    Grade = "A"
ElseIf Score >= 80 Then
    Grade = "B"
ElseIf Score >= 70 Then
    Grade = "C"
ElseIf Score >= 60 Then
    Grade = "D"
Else
    Grade = "F"
End If

note that one can also do this in the reversed order. First check to see if the Score is less than 60, etc. It would work equally well.

This second choice would be better if there are more low scores than high ones.

The Select Case Statement


The Select Case statement provides an efficient alternative to the series of If-Then-ElseIf statements when we execute particular actions on the basis of the value of a single variable.

The general form of the Select Case structure is

Select Case testExpression
    Case
        statements
    Case Else
        elseStatements
End Select

As you can see the Select Case Statement is very similar to the If Statement in that it will only perform some action if/when a condition is met. However, as you will learn, the Select Case is far more flexible. Let's look at the Select Case Statement in it's simplest form.

Sub TheSelectCase1()
    Select Case Range("A1").Value
        Case 100
        Range("B1") = 50
    End Select
End Sub

Now let us say you need to perform any one of 5 actions depending on the Value of Range A1. If so we could use

Sub TheSelectCase2()
    Select Case Range("A1").Value
    Case 100
        Range("B1").Value = 50
    Case 150
        Range("B1").Value = 40
    Case 200
        Range("B1").Value = 30
    Case 350
        Range("B1").Value = 20
    Case 400
        Range("B1").Value = 10
    End Select
End Sub

This, in my opinion, is a far better structure and easier to read than an If Statement with multiple ElseIf Statements. If none of the above Conditions were met, nothing would occur, unless we use the optional Case Else Statement, like:

Sub TheSelectCase3()
    Select Case Range("A1").Value
    Case 100
        Range("B1").Value = 50
    Case 150
        Range("B1").Value = 40
    Case 200
        Range("B1").Value = 30
    Case 350
        Range("B1").Value = 20
    Case 400
        Range("B1").Value = 10
    Case Else
        Range("B1").Value = 0
    End Select
End Sub

So if the value of range A1 is NOT 100,150,200,350 or 400 then place a Value of 0 (zero) in Range B1.

Now while this demonstrates how we can check multiple conditions with the Select Case Statement, what if we want to perform some action if the Range A1 is equal to any one of the Values 100,150,200,350 or 400. If this is the case (no pun intended) we could use:

Sub TheSelectCase4()
    Select Case Range("A1").Value
    Case 100, 150, 200, 350, 400
        Range("B1").Value = Range("A1").Value
    Case Else
        Range("B1").Value = 0
    End Select
End Sub

Let's assume that you only want to perform some action if the range(s) you are checking are between 2 numbers. If this is the case (excuse the pun) then you could use:

Sub TheSelectCase5()
    Select Case Range("A1").Value
    Case 100 To 500
        Range("B1").Value = Range("A1").Value
    Case Else
        Range("B1").Value = 0
    End Select
End Sub

In this instance, if the range A1 contains a number => 100 and =< 500 then cell A1 value will be placed into cell B1.

Now, what about if we needed to check if cell A1 was not only between 100 and 500 but also between 700 and 1000, 1500 and 2000? No problem, with the Select Case you would simply use:

Sub TheSelectCase6()
    Select Case Range("A1").Value
    Case 100 To 500, 700 To 1000, 1500 To 2000
        Range("B1").Value = Range("A1").Value
    Case Else
        Range("B1").Value = 0
    End Select
End Sub

In other words if cell A1 contains a number (eg 600) that does not meet the Case criteria, B1 will equal 0. By-the-way, the limit using a Select Case like this is far from 3 criteria! You can also mix and match the criteria you use in a Select Case Statement, so if you wanted to include some text and/or specific numbers you could use:

Sub TheSelectCase()
    Select Case Range("A1").Value
    Case 100 To 500, 652, 700 To 1000, 1233, 1500 To 2000, "dog", "cat"
        Range("B1").Value = Range("A1").Value
    Case Else
        Range("B1").Value = 0
    End Select
End Sub

Select Case Knows its Alphabet:

The last benefit we will look at with the Select Case Statement is how we can use it on text and determine if it lies between other text in an alphabetical sense

Sub TheSelectCase7()
    Select Case Range("A1").Text
    Case "aardvark" To "elephant"
        Range("B1").Value = "it's between"
    Case Else
        Range("B1").Value = "it's not between"
    End Select
End Sub

What this example will do is check whether the Text in cell A1 falls between "aardvark" and "elephant" in an alphabetic list. So if A1 had the text "zebra" B1 would equal "it's not between", however if it contained "cat" B1 would equal "it's between".

One point that should be made here is that, by default, VBA code like this, is case sensitive. So if cell A1 had the text "Cat" as apposed to "cat" B1 would equal "it's not between". This because the internal binary representations of the characters in Windows are seen in a table something like:

A < B < E < Z < a < b < e < z

This can at times cause unexpected results, but like most parts of Excel we can change this.

Finally we provide an example to compare the If-Then-Else structure with the Select Case structure.

An If-Then-Else structure:

Sub Using_IF()
    ' Dimension the variable.
    Dim x As Integer
    ' Place a value in x.
    x = Int(Rnd * 100)
    ' Test to see if x less than or equal to 10.
    If x <= 10 Then
        ' Display a message box.
        MsgBox "X is <=10"

    ' Test to see if x less than or equal to 40 and greater than 10.
    ElseIf x <= 40 And x > 10 Then
        MsgBox "X is <=40 and > 10"

    ' Test to see if x less than or equal to 70 and greater than 40.
    ElseIf x <= 70 And x > 40 Then
        MsgBox "X is <=70 and > 40"

    ' Test to see if x less than or equal to 100 and greater than 70.
    ElseIf x <= 100 And x > 70 Then
        MsgBox "X is <= 100 and > 70"

    ' If none of the above tests returned true.
    Else
        MsgBox "X does not fall within the range"
    End If

End Sub

This is the same thing written in a Select Case structure:

Sub Using_Case()

    ' Dimension the variable.
    Dim x As Integer
    ' Place a value in x.
    x = Int(Rnd * 100)

    ' Start the Select Case structure.
    Select Case x

    ' Test to see if x less than or equal to 10.
    Case Is <= 10
    ' Display a message box.
        MsgBox "X is <=10"

    ' Test to see if x less than or equal to 40 and greater than 10.
    Case 11 To 40
        MsgBox "X is <=40 and > 10"

    ' Test to see if x less than or equal to 70 and greater than 40.
    Case 41 To 70
        MsgBox "X is <=70 and > 40"

    ' Test to see if x less than or equal to 100 and greater than 70.
    Case 71 To 100
        MsgBox "X is <= 100 and > 70"

    ' If none of the above tests returned true.
    Case Else
        MsgBox "X does not fall within the range"
    End Select

End Sub

Nested If/Then/ElseIf structures

An If/Then/ElseIf structure can be nested inside another If/Then/ElseIf structure. Here is an example of such a case involved in the determination of the roots of a quadratic equation:

f(x) = a * x^2 + b * x + c

given the values of the coefficients a, b, and c.
Your program must work no matter what values they have (for example including 0).

There are in general two roots, r1 and r2, given by

r1 = (-b + Sqr(d)) / (2*a)

r2 = (-b - Sqr(d)) / (2*a)

where the discriminate d = b^2 - 4 * a * c, and Sqr(d) is the positive square root of d.

We can use these formulas as long as a is not equal to 0, and if d is positive.

Thus we must first see if a = 0.

If a = 0, then the equation becomes:

b * x + c = 0

Then the equation has at most one root. Now if b is not equal to 0, then the root is given by:

r1 = -c / b

On the other hand if b is also equal to 0, then the equation becomes:

c = 0

The solution is called trivial because if c is also given by 0, then we really do not have an equation at all, and if c is not given to be 0, then the equation has no solution at all.

If a is not equal to zero, we can use the quadratic formula to find the two roots. However the Sqr function in VBA (and in most other languages) cannot handle the case where its argument happens to be negative. So we need to handle that case ourselves

Mathematically when d < 0, its square root is given by i*Sqr(-d) and -i*Sqr(-d), where i is defined to be the square root of -1.

Thus if d < 0, then we have a pair of complex roots. Their real parts are both given by

r1 = r2 = -b / (2 * a)

and their imaginary parts are given by
i1 = Sqr(-d) / (2 * a)
i2 = - i1


A "top-down" approach is a powerful way to handle this kind of problem.

First we must see if a = 0, if so, we have "weird cases" (e.g. either we have a single root or the problem is trivial), otherwise we have the conventional cases that can be handle using the quadratic formula.

This "big-picture" version can be programmed as

If a = 0 Then
    'weird cases
Else
    'quadratic formula
End If
Nest we take a "smaller-picture" view to refine the code to handle the "weird cases":
If b <> 0 Then
    'single root
    r1 = -c / b
Else
    'trivial solution
    MsgBox "Trivial solution for the given parameters."
End If


Next we handle the conventional cases with the quadratic formula:

    'quadratic formula
    'first compute the discriminant
    d = b^2 - 4 * a *c
    If d > 0 Then
        '2 real roots
    Else
        'a pair of complex roots
    End If


Putting in the code of the case of "2 real roots", and the code for the case of "a pair of complex roots", we have

    'quadratic formula
    'first compute the discriminant
    d = b^2 - 4 * a *c
    If d > 0 Then
        '2 real roots
        r1 = (-b + Sqr(d)) / (2*a)
        r2 = (-b - Sqr(d)) / (2*a)
    Else
        'a pair of complex roots
        'their real parts are
        r1 = -b / (2 * a)
        r2 = r1
        'their imaginary parts are
        i1 = Sqr(-d) / (2 * a)
        i2 = - i1
    End If


We finally substitute these blocks back into the simple "big-picture" framework to give the final result:

If a = 0 Then
    'weird cases
    If b <> 0 Then
        'single root
        r1 = -c / b
    Else
        'trivial solution
        MsgBox "Trivial solution for the given parameters."
    End If
Else
    'quadratic formula
    'first compute the discriminant
    d = b^2 - 4 * a *c
    If d > 0 Then
        '2 real roots
        r1 = (-b + Sqr(d)) / (2*a)
        r2 = (-b - Sqr(d)) / (2*a)
    Else
        'a pair of complex roots
        'their real parts are
        r1 = -b / (2 * a)
        r2 = r1
        'their imaginary parts are
        i1 = Sqr(-d) / (2 * a)
        i2 = - i1
    End If
End If

You should test the program, for example, using the following data:

a = 1, b = 1, c = -2: 2 real roots
a = 0, b = 0, c = 1: trivial solution
a = 0, b = 5, c = 3: 1 real root
a = 1, b = -2, c = 3: a pair of complex roots

For certain sets of parameters, the above program does not give very accurate answers due to scaling and cancelation problems.