VBA Course: Conditions

Conditions are very useful in programming because they allow us to execute actions based on specific criteria (it's the same principle as the IF function).

The most important conditional function is If, and now we'll take a look at how it works:

If [CONDITION HERE] Then ' => IF condition is validated, THEN
    'Instructions if true
Else ' => OTHERWISE
    'Instructions if false
End If

Let's move right on to practice and return to the example that we used in the lesson on variables. The purpose of this procedure was to display a dialog box containing the data from the row whose number is indicated in cell F5:

Source file: conditions.xls

If you enter a letter in cell F5, it will cause a bug. We want to prevent that from happening.

Sub variables()
    'Declaring variables
    Dim last_name As String, first_name As String, age As Integer, row_number As Integer
        
    'Assigning values to variables
    row_number = Range("F5") + 1
    last_name = Cells(row_number, 1)
    first_name = Cells(row_number, 2)
    age = Cells(row_number, 3)
    
    'Dialog box
    MsgBox last_name & " " & first_name & ", " & age & " years old"
End Sub

Let's begin by adding a condition that will verify that the value of cell F5 is numerical before the code is executed.

We'll use the function IsNumeric to test this condition:

Sub variables()

    'If the value in parentheses (cell F5) is numerical (AND THEREFORE IF THE CONDITION IS TRUE) then
    'execute the instructions that follow THEN
    If IsNumeric(Range("F5")) Then
    
        'Declaring variables
        Dim last_name As String, first_name As String, age As Integer, row_number As Integer
        'Values of variables
        row_number = Range("F5") + 1
        last_name = Cells(row_number, 1)
        first_name = Cells(row_number, 2)
        age = Cells(row_number, 3)
        'Dialog Box
        MsgBox last_name & " " & first_name & ", " & age & " years old"
        
    End If
    
End Sub

We also need to add instructions to execute in case the conditions are not met:

Sub variables()

    If IsNumeric(Range("F5")) Then 'IF CONDITION TRUE
    
        'Declaring variables
        Dim last_name As String, first_name As String, age As Integer, row_number As Integer
        'Values of variables
        row_number = Range("F5") + 1
        last_name = Cells(row_number, 1)
        first_name = Cells(row_number, 2)
        age = Cells(row_number, 3)
        'Dialog box
        MsgBox last_name & " " & first_name & ", " & age & " years old"
        
    Else 'IF CONDITION FALSE
    
        'Dialog box: warning
        MsgBox "Your entry" & Range("F5") & " is not valid !"
        'Deleting the contents of cell F5
        Range("F5").ClearContents
    
    End If
    
End Sub

Now non-numerical values won't cause any problems.

Working with our array, which contains 16 rows of data, our next step will be to test whether the variable row_number: "greater than or equal to 2" and "less than or equal to 17".

But first, have a look at this list of comparison operators:

And these other useful operators:

Now let's add the conditions that we mentioned above, using AND along with the comparison operators listed above:

Sub variables()
    If IsNumeric(Range("F5")) Then 'IF NUMERICAL
        Dim last_name As String, first_name As String, age As Integer, row_number As Integer
        row_number = Range("F5") + 1

        If row_number >= 2 And row_number <= 17 Then 'IF CORRECT NUMBER
            last_name = Cells(row_number, 1)
            first_name = Cells(row_number, 2)
            age = Cells(row_number, 3)
            MsgBox last_name & " " & first_name & ", " & age & " years old"
        Else 'IF NUMBER IS INCORRECT
            MsgBox "Your entry " & Range("F5") & " is not a valid number !"
            Range("F5").ClearContents
        End If
        
    Else 'IF NOT NUMERICAL
        MsgBox "Your entry " & Range("F5") & " is not valid !"
        Range("F5").ClearContents
    End If
End Sub

If we wanted to make our macro a bit more practical, we could replace 17 with a variable that contained the number of rows. This would let us add or remove lines from our array without having to change this limit each time.

In order to do this, we have to create a variable nb_rows and add this function:

In this case, we'll use WorksheetFunction.CountA which is the function COUNTA which you may already be familiar with ...

We want this function to count the number of non-empty cells in the first column and then replace 17 with nb_rows:

Sub variables()
    If IsNumeric(Range("F5")) Then 'IF NUMERICAL
        Dim last_name As String, first_name As String, age As Integer, row_number As Integer
        Dim nb_rows As Integer
        
        row_number = Range("F5") + 1
        nb_rows = WorksheetFunction.CountA(Range("A:A")) 'NBVAL Function
        
        If row_number >= 2 And row_number <= nb_rows Then 'IF CORRECT NUMBER 
            last_name = Cells(row_number, 1)
            first_name = Cells(row_number, 2)
            age = Cells(row_number, 3)
            MsgBox last_name & " " & first_name & ", " & age & " years old"
        Else 'IF NUMBER IS INCORRECT
            MsgBox "Your entry " & Range("F5") & " is not a valid number !"
            Range("F5").ClearContents
        End If

    Else 'IF NOT NUMERICAL
        MsgBox "Your entry " & Range("F5") & " is not valid !"
        Range("F5").ClearContents
    End If
End Sub

ElseIf

ElseIf makes it possible to add more conditions after the IF command:

If [CONDITION 1] Then ' => IF condition 1 is true, THEN
    'Instructions 1
ElseIf [CONDITION 2] Then ' => IF condition 1 is false, but condition 2 is true, THEN
    'Instructions 2
Else ' => OTHERWISE
    'Instructions 3
End If

If condition 1 is true, Instructions 1 will be executed, and we will leave the If command (which begins with If and ends with End If). If condition 1 is false, we continue to condition 2. If this condition is true, Instructions 2 will be executed, and if it is false, then Instructions 3 (under Else) will be executed.

Here is an example, with a score between 1 and 6 in cell A1 (without decimals in this case) and a score_comment in cell B1 based on the score:

Sub scores_comment()
    'Variables
    Dim note As Integer, score_comment As String
    note = Range("A1")
    
    'Comments based on the score
    If note = 6 Then
        score_comment = "Excellent score !"
    ElseIf note = 5 Then
        score_comment = "Good score"
    ElseIf note = 4 Then
        score_comment = "Satisfactory score"
    ElseIf note = 3 Then
        score_comment = "Unsatisfactory score"
    ElseIf note = 2 Then
        score_comment = "Bad score"
    ElseIf note = 1 Then
        score_comment = "Terrible score"
    Else
        score_comment = "Zero score"
    End If
    
    'Comments in B1
    Range("B1") = score_comment
End Sub

Select

There is an alternative to using If with lots of ElseIf instructions: the Select command, which is better suited to these sorts of situations.

Here is an example of the same macro written with Select:

Sub scores_comment()
    'Variables
    Dim note As Integer, score_comment As String
    note = Range("A1")
    
    'Comments based on the score
    Select Case note    ' <= the value to test (the score, in this case)
    Case Is = 6         ' <= if the value = 6
        score_comment = "Excellent score !"
    Case Is = 5         ' <= if the value = 5
        score_comment = "Good score"
    Case Is = 4         ' <= if the value = 4
        score_comment = "Satisfactory score"
    Case Is = 3         ' <= if the value = 3
        score_comment = "Unsatisfactory score"
    Case Is = 2         ' <= if the value = 2
        score_comment = "Bad score"
    Case Is = 1         ' <= if the value = 1
        score_comment = "Terrible score"
    Case Else           ' <= if the value isn't equal to any of the above values
        score_comment = "Zero score"
    End Select
    
    'Comment in B1
    Range("B1") = score_comment
End Sub

Please note that we could also have used other comparison operators, for example:

Case Is >= 6         'if the value is >= 6

Examples with different values:

Case Is = 6, 7       'if the values is = 6 or 7
Case Is <> 6, 7      'if the value is different than 6 or 7

Case 6 To 10         'if the value is = any number between 6 and 10