Conditions are very useful in programming because they allow us to perform actions depending on set criteria (the same principle is used as in Excel's IF function).
The most important function that sets a condition is IF and now we will see how it works:
If [CONDITION HERE] Then ' => IF the condition is true THEN
'Instructions if "true"
Else ' => ELSE
'Instructions if "lie"
End If
Let's get practical and go back to the example we used in the variable lesson. The purpose of this procedure was to display a dialog box that would contain the value from the string specified in cell F5:
If you type a letter in the F5 cell, it will cause an error. We want to prevent this.
Sub variables()
'Declaration of 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 window
MsgBox last_name & " " & first_name & ", " & age & " years"
End Sub
Let's add a condition that will check if the value entered in cell F5 is a number before the code is executed.
We will use the IsNumeric function to test the condition:
Sub variables()
'If the value in the parentheses (cell F5) is numeric (Hence the IF CONDITION IS TRUE) then
'execute the instructions following THEN
If IsNumeric(Range("F5")) Then
'Declaration of 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 window
MsgBox last_name & " " & first_name & ", " & age & " years"
End If
End Sub
We also need to write instructions if the condition we set is not fulfilled:
Sub variables()
If IsNumeric(Range("F5")) Then 'If the condition is met
'Declaration of 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 window
MsgBox last_name & " " & first_name & ", " & age & " years"
Else 'If the condition is not met
'Dialog box: warning
MsgBox "Entered value" & Range("F5") & " is not valid!"
'Deleting the contents of cell F5
Range("F5").ClearContents
End If
End Sub
Now a non-numeric value won't cause any problems.
Working with our array that contains 16 rows of data, our next step will be to check if the row_number variable is: "greater than or equal to 2" and "less than or equal to 17".
But first, let's look at the comparison operators:
| = | even |
| <> | not equal |
| < | less than |
| <= | less than or equal to |
| > | more than |
| >= | greater than or equal to |
and these useful operators:
| AND | [condition1] AND [condition2] Two conditions must be fulfilled |
| OR | [condition1] OR [condition2] That at least 1 of 2 conditions must be fulfilled |
| NOT | NOT [condition1] The condition must not be met |
Now let's add one of the above AND conditions between the comparison operators:
Sub variables()
If IsNumeric(Range("F5")) Then 'If a numeric value
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 the number is correct
last_name = Cells(row_number, 1)
first_name = Cells(row_number, 2)
age = Cells(row_number, 3)
MsgBox last_name & " " & first_name & ", " & age & " years"
Else 'If the number is not correct
MsgBox "The entered number " & Range("F5") & " is not correct!"
Range("F5").ClearContents
End If
Else 'If this is not a numeric value
MsgBox "The entered value " & Range("F5") & " is not valid!"
Range("F5").ClearContents
End If
End Sub
If we want to make our macro more practical, we can replace 17 with a variable that would hold the number of lines. This would allow us to add and remove rows from the array without having to change this limit each time.
In order to do this, we need to create a nb_rows variable and add this function.
In this case, we will use the function WorksheetFunction.CountA, which is an analogue of the function COUNTA in Excel itself.
We want this function to count the number of non-empty cells in the first column and write the resulting value to the nb_rows variable:
Sub variables()
If IsNumeric(Range("F5")) Then 'IF NUMBER
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")) 'Function to count the number of rows
If row_number >= 2 And row_number <= nb_rows Then 'IF VALID 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"
Else 'IF NUMBER IS INCORRECT
MsgBox "The entered number " & Range("F5") & " is not correct!"
Range("F5").ClearContents
End If
Else 'IF NOT NUMBER
MsgBox "The entered value " & Range("F5") & " is not valid!"
Range("F5").ClearContents
End If
End Sub
ElseIf allows you to add additional 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, Statement 1 will execute and exit the IF statement (which starts with IF and ends with End If). If CONDITION 2 takes the value "false", then Instruction 2 will be executed, and if it in its queue returns "false", then Statement 3 (under Else) will be executed.
The following is an example with grades 1 through 6 in cell A1 and a comment on those grades in cell B1:
Sub scores_comment()
'Variables
Dim note As Integer, score_comment As String
note = Range("A1")
'Comments based on the rating received
If note = 6 Then
score_comment = "Great 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
'Comment in cell B1
Range("B1") = score_comment
End Sub
There is an alternative to using If with many ElseIf statements, namely the Select command, which is more suited to this kind of situation.
Let's consider an example of a macro with the Select operator:
Sub scores_comment()
'Variables
Dim note As Integer, score_comment As String
note = Range("A1")
'Comments based on the rating received
Select Case note ' <= test score (points)
Case Is = 6 ' <= if value = 6
score_comment = "Great score!"
Case Is = 5 ' <= if value = 5
score_comment = "Good score"
Case Is = 4 ' <= if value = 4
score_comment = "Satisfactory score"
Case Is = 3 ' <= if value = 3
score_comment = "Unsatisfactory score"
Case Is = 2 ' <= if value = 2
score_comment = "Bad score"
Case Is = 1 ' <= if value = 1
score_comment = "Terrible score"
Case Else ' <= if the value is not equal to any of the above
score_comment = "Zero score"
End Select
'Comment in cell B1
Range("B1") = score_comment
End Sub
It is worth noting that we could also use other comparison operators:
Case Is >= 6 'if value >= 6
Examples with different values:
Case Is = 6, 7 'if value = 6 or 7 Case Is <> 6, 7 'if the value is not equal to 6 or 7
Case 6 To 10 'if value = any number from 6 to 10