VBA Comparison Operators

Comparison operators are the operators that are used to compare values. Some of the comparison operators are equal to (=), less than (<), greater than(>) and not equal to(<>).

The user mostly uses comparison operators to compare the values for the purpose of validation. Comparison operators come in handy in situations such as where you want to compare the total amount you invested vs the amount you had in your hand. 

VBA Comparison Operators

Example of VBA Comparison Operators

To perform comparison operations, follow the steps:

1: Open an excel workbook and save it as an excel macro-enabled workbook. 

2:  Add a button using macros. 

In order to create a macro, 

• Go to the developer tab and click on insert from the ribbon.
• Choose the command button.
• Assign a macro by providing a name and click on the new button 

3: Finally, press ok. 

4: Once the code window appears, type the code.

1. Equal To Operator

Example of Equal to Operator in VBA

Sub Button1_Click()
    Dim a As Integer, b As Integer
    a = 5
    b = 5
    If a = b Then
            MsgBox "True", vbOKOnly, "Comparison Operation"
        Else
            MsgBox "False", vbOKOnly, "Comparison Operation"
    End If
End Sub

Code Explanation:

Here, Sub Button1_Click() is the sub procedure.

‘a’ and ‘b’ are integer data type variables. The comparison operator i.e., equal to (=) here checks whether the a and b values are equal. If the values are equal, it returns true. 

The result will appear in a message box with the ‘Comparison Operation’ title.

The user writes the code in the VBA editor.

5: Click on save button or press ctrl+s.

6: Close the vba code editor window.

Your GUI appears as follows:

Once you click on the button, you will get the output:

2. Not Equal To Operator in VBA

Example of VBA Not Equal To Operator

Sub Button1_Click()
    Dim a As Integer, b As Integer
    a = 5
    b = 5
    If a <> b Then
            MsgBox "True", vbOKOnly, "Comparison Operation"
        Else
            MsgBox "False", vbOKOnly, "Comparison Operation"
    End If
End Sub

Code Explanation:

Here, Sub Button1_Click() is the sub procedure.

‘a’ and ‘b’ are integer data type variables. The comparison operator i.e., not equal to (<>) here checks whether the a and b values are not equal. If the values are equal, it returns false. 

The result will appear in a message box with the ‘Comparison Operation’ title. Your GUI appears

Once you click on the button, the following output appears:

3. Less Than Operator in VBA

Example of VBA Less Than Operator

Sub Comparison_Click()
    Dim a As Integer, b As Integer
    a = 5
    b = 5
    If a < b Then
            MsgBox "The value stored in 'a' variable is less than 'b' variable", vbOKOnly, "Comparison Operation"
        Else
            MsgBox "The value stored in 'a' variable is not less than 'b' variable", vbOKOnly, "Comparison Operation"
    End If
End Sub

Code Explanation

Here, Sub Comparison_Click() is the sub procedure.

‘a’ and ‘b’ are integer data type variables. The comparison operator i.e., less than(<) operator here checks whether the ‘a’ value is less than the ‘b’ value. If this condition satisfies, the result appears as ‘The value stored in ‘a’ variable is less than ‘b’ variable’ or else it returns as ‘”The value stored in ‘a’ variable is not less than ‘b’ variable’.

The result will appear in a message box with the ‘Comparison Operation’ title. Your GUI appears.

When you click on the button, the following output appears:

5. Greater Than Operator in VBA

Example of VBA Greater Than Operator

Sub Comparison_Click()
    Dim a As Integer, b As Integer
    a = 5
    b = 5
    If a > b Then
            MsgBox "The value stored in 'a' variable is greater than 'b' variable", vbOKOnly, "Comparison Operation"
        Else
            MsgBox "The value stored in 'a' variable is not greater than 'b' variable", vbOKOnly, "Comparison Operation"
    End If
End Sub

Code Explanation:

Here, Sub Comparison_Click() is the sub procedure.

‘a’ and ‘b’ are integer data type variables. The comparison operator i.e., greater than(>) operator here checks whether the ‘a’ value is greater than the ‘b’ value. If this condition satisfies, the result appears as ‘The value stored in ‘a’ variable is greater than ‘b’ variable’ or else it returns as ‘”The value stored in ‘a’ variable is not greater than ‘b’ variable’.

The result will appear in a message box with the ‘Comparison Operation’ title. Your GUI appears:

When you click on the button, the following output appears:

6. Lesser than or Equal To Operator in VBA

Example of Lesser than or Equal To Operator in VBA

Sub Comparison_Click()
    Dim a As Integer, b As Integer
    a = 5
    b = 5
    If a <= b Then
          MsgBox "The value stored in 'a' variable is lesser than or equal to 'b' variable", vbOKOnly, "Comparison Operation"
        Else
            MsgBox "The value stored in 'a' variable is not lesser than or equal to 'b' variable", vbOKOnly, "Comparison Operation"
    End If
End Sub

Code Explanation:

Here, Sub Comparison_Click() is the sub procedure.

‘a’ and ‘b’ are integer data type variables. The comparison operator i.e., Lesser than or Equal To than(<=) operator here checks whether the ‘a’ value is lesser than or Equal To the ‘b’ value. If this condition satisfies, the result appears as ‘The value stored in ‘a’ variable is lesser than or Equal To ‘b’ variable’ or else it returns as ‘The value stored in ‘a’ variable is not lesser than or Equal To ‘b’ variable’.

The result will appear in a message box with the ‘Comparison Operation’ title. Your GUI appears.

When you click on the button, the following output appears:

7. Greater than or Equal To Operator in VBA

Example of Greater than or Equal to Operator in VBA

Sub Comparison_Click()
    Dim a As Integer, b As Integer
    a = 5
    b = 5
    If a >= b Then
          MsgBox "The value stored in 'a' variable is greater than or equal to 'b' variable", vbOKOnly, "Comparison Operation"
        Else
            MsgBox "The value stored in 'a' variable is not greater than or equal to 'b' variable", vbOKOnly, "Comparison Operation"
    End If
End Sub

Code Explanation:

Here, Sub Comparison_Click() is the sub procedure.

‘a’ and ‘b’ are integer data type variables. The comparison operator i.e., Greater than or Equal To than(>=) operator here checks whether the ‘a’ value is Greater than or Equal To the ‘b’ value. If this condition satisfies, the result appears as ‘The value stored in ‘a’ variable is greater than or Equal To ‘b’ variable’ or else it returns as ‘The value stored in ‘a’ variable is not greater than or Equal To ‘b’ variable’.

The result will appear in a message box with the ‘Comparison Operation’ title. Your GUI appears.

When you click on the button, the following output appears:

Summary

1. The comparison operator helps the user to compare the data values.

2. They are widely helpful in data validation purposes.

3. Some of the comparison operators are equal to (=), less than (<), greater than(>) and not equal to(<>).