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
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.