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 Integer2 <= n <= 4 Then
If = 1
BadEx
Else= 0
BadEx
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 Integer2 <= n And n <= 4 Then
If = 1
GoodEx
Else= 0
GoodEx
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> 2 Then n = n+2: DumbFunction = n
If 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-8
Case = -x
AllCases -7 To 4
Case = x
AllCases >= 5
Case Is = 2 * x
AllCases
Case Else= 0
AllCases
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<= 0, 4 To 7, 8
Case Is = 0
ListOfCases
Case Else= 1
ListOfCases
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.
class As String) As Variant
Function ticket(destination As String,
Select Case destination"Manchester"
Case class
Select Case "First"
Case = 200
ticket "Second"
Case = 100
ticket
Case Else= "Not a valid class"
ticket
End Select"Oxford"
Case class
Select Case "First"
Case = 30
ticket "Second"
Case = 12
ticket
Case Else= "Not a valid class"
ticket
End Select
Case Else= "Not a valid destination"
ticket
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.