VBA Decision Making

Decision making statements come in two forms in VBA, ‘If’ statements and ‘Case’ statements. Both allow a decision to be made between two or more options.

‘If’ Statements

In its most basic form an ‘If’ statement executes a group of statements if an expression evaluates to true. Its basic syntax is as follows:

If expression Then
   ' Statement(s) will execute if the expression is true.
End If

The following example checks whether the values of two variables are the same and displays a message in cell ‘A1’ of a spreadsheet if they are.

Dim a As Integer
Dim b As Integer

a = 10
b = 10

If a = b Then
   Range("A1").Value = "a is equal to b"
End If

This can be extended to execute a statement or statements if the expression is false as follows.

If expression Then
   ' Statement(s) will execute if the expression is true.
Else
   ' Statement(s) will execute if the expression is false.
End If

The ‘If’ statement can be further extended with the use of ‘ElseIf’. Any number of ‘ElseIf’ statements can be used to extend the decision making process.

If expression Then
   ' Statement(s) will execute if the expression is true.
ElseIf expression Then
   ' Statement(s) will execute if the first expression is false
   ' and the second expression is true.
Else
   ' Statement(s) will execute if the expression is false.
End If

‘If’ statements can also be nested one inside another.

If expression Then
   ' Statement(s) will execute if the expression is true.
   If expression Then
      ' Statement(s) will execute if the expression is true.
   End If
End If

Below are some examples of ‘If’ statements using the logical operators discussed in the previous section on operators.

Dim a As Boolean
Dim b As Boolean
Dim c As Boolean

a = True
b = True
c = False

If a AND b Then

   ' Display a message if 'a' and 'b' are true.
   Range("A1").Value = "Condition 1 is true"
 
End If
If c OR b Then

   ' Display a message if 'c' or 'b' are true.
   Range("A2").Value = "Condition 2 is true"

End If
If c AND b Then

   ' Display a message if 'c' and 'b' are true.
   Range("A3").Value = "Condition 3 is true"

Else

   ' Display a message if 'c' and/or 'b' are not true.
   Range("A3").Value = "Condition 3 is not true"

End If
If NOT(c AND b) Then

   ' Display a message if 'c' and/or 'b' are not true.
   Range("A4").Value = "Condition 4 is true"

End If

The results of running the above will be as follows:

Condition 1 is true
Condition 2 is true
Condition 3 is not true
Condition 4 is true

‘Case’ Statements

Where there are more than two options in a decision, a ‘Case’ statement can be more efficient than using multiple ‘ElseIf’ statements.

Select Case expression

   Case expression-result-option1
      ' Statement(s) to execute.

   Case expression-result-option2
      ' Statement(s) to execute.
 
   Case expression-result-option3
      ' Statement(s) to execute.

   Case Else
      ' Statement(s) to execute.

End Select

A simple example of a ‘Case’ statement would be to display a message on screen depending on the value of a variable. Here a message is displayed in cell ‘A1’ of a spreadsheet depending on whether the value of the variable is one, two or three.

Dim a As Integer
a = 3

Select Case a

   Case 1
      Range("A1").Value = "a equals 1"

   Case 2
      Range("A1").Value = "a equals 2"

   Case 3
      Range("A1").Value = "a equals 3"

   Case Else
      Range("A1").Value = "a is not equal to 1, 2 or 3"

End Select

The message displayed in cell ‘A1’ would be “a equals 3”. There is no limit as to the number of options that a ‘Case’ statement can have. The ‘Case Else’ at the end is a catch all if the value is something other than one, two or three.