VBA Course: Conditions

Conditions are very useful in programming, they will be used to perform actions based on specific criteria (same principle as the IF function).

The main instruction is If, here is how it works:

If [CONDITION] Then '=> IF condition is true THEN
    'Instructions if true
Else '=> ELSE (optional)
    'Instructions if false
End If

Let's go straight to practice and take the example developed in the lesson on variables. Its goal was to display in a dialog box the line of the table corresponding to the number indicated in cell F5.

Source file: conditions.xlsm

If we enter a letter in F5, it generates a bug and we want to avoid that.

Sub example()

    'Variable declaration
    Dim name As String, firstName As String, age As Integer, lineNumber As Integer
        
    'Variable values
    lineNumber = Range("F5") + 1
    name = Cells(lineNumber, 1)
    firstName = Cells(lineNumber, 2)
    age = Cells(lineNumber, 3)
    
    'Dialog box
    MsgBox name & " " & firstName & ", " & age & " years old"
	
End Sub

We will start by adding a condition to check if the value of cell F5 is numeric before executing the code.

The IsNumeric function will be used in this condition:

Sub example()

    'If the value in parentheses (cell F5) is numeric (so if the condition is true) then we execute the instructions placed between "Then" and "End If"
    If IsNumeric(Range("F5")) Then
    
        'Variable declaration
        Dim name As String, firstName As String, age As Integer, lineNumber As Integer
		
        'Variable values
        lineNumber = Range("F5") + 1
        name = Cells(lineNumber, 1)
        firstName = Cells(lineNumber, 2)
        age = Cells(lineNumber, 3)
		
        'Dialog box
        MsgBox name & " " & firstName & ", " & age & " years old"
        
    End If
    
End Sub

Let's also add instructions for the case where the condition is not met:

Sub example()

    'If F5 is numeric
    If IsNumeric(Range("F5")) Then
    
        'Variable declaration
        Dim name As String, firstName As String, age As Integer, lineNumber As Integer
		
        'Variable values
        lineNumber = Range("F5") + 1
        name = Cells(lineNumber, 1)
        firstName = Cells(lineNumber, 2)
        age = Cells(lineNumber, 3)
		
        'Dialog box
        MsgBox name & " " & firstName & ", " & age & " years old"
    
    'If F5 is not numeric
    Else
    
        'Dialog box: warning
        MsgBox "The entry """ & Range("F5") & """ is not valid!"
		
        'Clearing the content of cell F5
        Range("F5") = ""
    
    End If
    
End Sub

Non-numeric values are no longer a problem.

Our table contains 16 rows of data (from row 2 to row 17), so now we will check if the variable lineNumber is greater than or equal to 2 and less than or equal to 17.

But first, here are the comparison operators:

=	Is equal to
<>	Is different from
<	Is less than
<=	Is less than or equal to
>	Is greater than
>=	Is greater than or equal to

As well as other useful operators:

And	[CONDITION 1] And [CONDITION 2] Both conditions must be true
Or	[CONDITION 1] Or [CONDITION 2] At least 1 of the 2 conditions must be true
Not	Not [CONDITION] The condition must be false
Mod	[NUMBER] Mod [DIVISOR] This operator returns the remainder of a division

Let's now add the conditions mentioned above using And as well as the comparison operators detailed above:

Sub example()

    'If F5 is numeric
    If IsNumeric(Range("F5")) Then

        Dim name As String, firstName As String, age As Integer, lineNumber As Integer
        lineNumber = Range("F5") + 1

        'If the number is in the correct range
        If lineNumber >= 2 And lineNumber <= 17 Then
            name = Cells(lineNumber, 1)
            firstName = Cells(lineNumber, 2)
            age = Cells(lineNumber, 3)
            MsgBox name & " " & firstName & ", " & age & " years old"

        'If the number is out of range
        Else
            MsgBox "The entry """ & Range("F5") & """ is not a valid number!"
            Range("F5") = ""
        End If

    'If F5 is not numeric
    Else
        MsgBox "The entry """ & Range("F5") & """ is not valid!"
        Range("F5") = ""
    End If

End Sub

To make our macro more practical, we can still replace 17 with a variable containing the number of rows. This will allow us to add/remove rows from our table without having to modify this limit in the code each time.

For this, let's create a nbRows variable 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 ask this function to count the number of non-empty cells in the first column and then replace 17 with nbRows:

Sub example()

    'If F5 is numeric
    If IsNumeric(Range("F5")) Then

        Dim name As String, firstName As String, age As Integer, lineNumber As Integer, nbRows As Integer
        
        lineNumber = Range("F5") + 1
        nbRows = WorksheetFunction.CountA(Range("A:A")) 'COUNTA function

        'If the number is in the correct range
        If lineNumber >= 2 And lineNumber <= nbRows Then
            name = Cells(lineNumber, 1)
            firstName = Cells(lineNumber, 2)
            age = Cells(lineNumber, 3)
            MsgBox name & " " & firstName & ", " & age & " years old"

        'If the number is out of range
        Else
            MsgBox "The entry """ & Range("F5") & """ is not a valid number!"
            Range("F5") = ""
        End If

    'If F5 is not numeric
    Else
        MsgBox "The entry """ & Range("F5") & """ is not valid!"
        Range("F5") = ""
    End If
End Sub

ElseIf

ElseIf allows to add several conditions in a row:

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

If condition 1 is true, the instructions 1 are executed and we exit the If instruction (which starts with If and ends with End If). If condition 1 is false, we move on to condition 2. If it's true the instructions 2 are executed, otherwise the instructions 3 are executed.

Here is an example, with a grade from 1 to 6 in A1 and a comment based on the grade in B1:

Sub comments()

    'Variables
    Dim grade As Single, comment As String
    grade = Range("A1")
    
    'Comment based on the grade
    If grade = 6 Then
        comment = "Excellent result!"
    ElseIf grade >= 5 Then
        comment = "Good result"
    ElseIf grade >= 4 Then
        comment = "Satisfactory result"
    ElseIf grade >= 3 Then
        comment = "Unsatisfactory result"
    ElseIf grade >= 2 Then
        comment = "Bad result"
    ElseIf grade >= 1 Then
        comment = "Terrible result"
    Else
        comment = "No result"
    End If
    
    'Comment in B1
    Range("B1") = comment

End Sub

Select

An alternative to If instructions containing many ElseIf exists, it's Select (this instruction being more appropriate in such cases).

Here is the same macro with Select:

Sub comments()

    'Variables
    Dim grade As Single, comment As String
    grade = Range("A1")
    
    'Comment based on the grade
    Select Case grade '<= the value to test (here, the grade)
        Case Is = 6
            comment = "Excellent result!"
        Case Is >= 5
            comment = "Good result"
        Case Is >= 4
            comment = "Satisfactory result"
        Case Is >= 3
            comment = "Unsatisfactory result"
        Case Is >= 2
            comment = "Bad result"
        Case Is >= 1
            comment = "Terrible result"
        Case Else
            comment = "No result"
    End Select
    
    'Comment in B1
    Range("B1") = comment

End Sub

Note that we can also enter multiple values:

Case Is = 6, 7 'If the value = 6 or 7
Case Is <> 6, 7 'If the value is different from 6 or 7

Or a range of values:

Case 6 To 10 'If the value = from 6 to 10