Chapter 6 The If and Select Case constructions

6.1 If Then statements

The basic features of If/Then/Else statements are fairly elementary, and we have covered most of them in lectures. However, there is one common error that often occurs in student code, relating to If statements that depend on a range of values.

Suppose that we wish to encode the function \[f(n)=\left\{\begin{array}{ll} 1 & \text{ if}\ 2\leq n\leq 4\\ 0 & \text{otherwise}\end{array}\right.\] It is not unreasonable to imagine that this could be solved using the first function BadEx shown below.

Function BadEx(n As Integer) As Integer
If 2 <= n <= 4 Then
    BadEx = 1
Else
    BadEx = 0
End If
End Function

However, if you try this function in Excel you will find that for all values \(n\) we have that BadEx(n)=1. This is because VBA does not allow two inequalities to be combined in this way (unlike some other programming languages).

We can easily modify the given code to produce a correct version of the function, as shown in the GoodEx function below. Here we have combined the two inequalities using an And statement and this will now work as we desired.

Function GoodEx(n As Integer) As Integer
If 2 <= n And n <= 4 Then
    GoodEx = 1
Else
    GoodEx = 0
End If
End Function

We can use If statements which do not have Else cases by simply putting all of the commands that follow the If statement on the same line, separated by colons. A very simple example is given below

Function DumbFunction(n As Integer) As Integer
If n > 2 Then n = n+2: DumbFunction = n
End Function

In this example the function DumbFunction returns the value \(n+2\) if \(n>2\). However I strongly recommend that you do not use this form of If statement. In my view it is much less easy to see what is going on here compared to a statement with indented statements and an End If — and more importantly, it makes it more likely that your code will not behave as you expect. In the given example, no value is defined for DumbFunction if \(n<=2\) (and so it will return the default value of \(0\)). This is probably not what was intended, and getting into the habit of including an Else statement will make it less likely that unexpected cases such as this will arise.

6.2 Select Case

We have seen in lectures that the Select Case construction can be used to determine which statements should be executed, depending on the value of some variable. In this section we will consider this construction in a little more detail, and give some further examples.

Recall that the three possible types of test in Select Case are

  • The variable equals a given value.
  • The variable varies between two values.
  • The variable satisfies an inequality.

These three possibilities are all used in the following example.

Function AllCases(x As Single) As Single
Select Case x
    Case -8
        AllCases = -x
    Case -7 To 4
        AllCases = x
    Case Is >= 5
        AllCases = 2 * x
    Case Else
        AllCases = 0
End Select
End Function

This code corresponds to the function \[\text{AllCases}(x)=\left\{\begin{array}{rl} -x & \text{if}\ x=-8\\ x & \text{if}\ -7\leq x \leq 4\\ 2x & \text{if}\ x\geq 5\\ 0 & \text{otherwise.}\end{array}\right.\] Notice that a To b as a type corresponds to \(a\leq x\leq b\), and so includes the end values of the range.

We can also combine different conditions into a single Case, by writing them as a list separated by commas. For example, consider the function \[\text{ListOfCases}(x)=\left\{\begin{array}{rl} 0 & \text{if}\ x\leq 0\\ 0 & \text{if}\ 4\leq x\leq 7\\ 0 & \text{if}\ x=8\\ 1 & \text{otherwise.}\end{array}\right.\] This corresponds to the function

Function ListOfCases(x As Single) As Single
Select Case x
    Case Is <= 0, 4 To 7, 8
        ListOfCases = 0
    Case Else
        ListOfCases = 1
End Select
End Function

Notice that when we give a list of conditions, the case holds when any one of them is true.

If the variable being tested in a Case statement is a string, then the test is case sensitive. Thus in the following example we need to capitalise both the town and the ticket type or else we will get one of the error messages.

Function ticket(destination As String, class As String) As Variant
Select Case destination
    Case "Manchester"
        Select Case class
            Case "First"
                ticket = 200
            Case "Second"
                ticket = 100
            Case Else
                ticket = "Not a valid class"
        End Select
    Case "Oxford"
        Select Case class
            Case "First"
                ticket = 30
            Case "Second"
                ticket = 12
            Case Else
                ticket = "Not a valid class"
        End Select  
    Case Else
        ticket = "Not a valid destination"
End Select
End Function

Notice how in this example we have nested the Select Case construction inside itself. Nesting in this way is quite common, and is one of the reasons why indenting code consistently helps us to follow what is going on.

When VBA works through a Select Case structure, it will only execute the first true case which it finds. Thus it is implicit in a Case statement that none of the conditions of the previous cases are satisfied. Also, if we do not include a Case Else statement then the function will have value \(0\) for any cases not covered by the Case statements.