VBA Comparison Operators – Not Equal to & More

Associated Files Download Links

VBA allows you to use comparison operators to compare values. By using these operators, you can compare values and return a Boolean True or False as a result.

The List of the Main Comparison Operators in VBA

First, we will display the list of comparison operators that can be used in VBA code:

Comparison Operator

Explanation
=

Equal to

<>

Not Equal to

>

Greater than

>=

Greater than or Equal to

<

Less than

<=

Less than or Equal to

Equal To

The Equal to operator checks if two values are equal and returns True or False. Here is the example code:

Dim intA As Integer
Dim intB As Integer
Dim blnResult As Boolean

intA = 5
intB = 5 

If intA = intB Then
    blnResult = True
Else
    blnResult = False
End If

In this example, we want to check if intA is equal to intB. If this is true, the value of Boolean blnResult will be True, otherwise, it will be False.

First, we set values of intA and intB to 5:

intA = 5
intB = 5

After that, we use the = operator in the If statement to check if the values of intA and intB are equal:

If intA = intB Then
    blnResult = True
Else
    blnResult = False
End If

Both variables are equal to 5, therefore the blnResult returns True:

vba comparison operators equal to

Image 1. Using the Equal To operator

 

Not Equal To

The Not Equal to operator checks if two values are not equal and returns True or False. Here is the example code:

Dim intA As Integer
Dim intB As Integer
Dim blnResult As Boolean

intA = 5
intB = 6

If intA <> intB Then
    blnResult = True
Else
    blnResult = False
End If

In this example, we want to check if intA is not equal to intB. If this is true, the value of Boolean blnResult will be True, otherwise, it will be False.

We use the <> operator in the If statement to check if the values of intA and intB are different:

If intA <> intB Then
    blnResult = True
Else
    blnResult = False
End If

The value of intA is 5 and the value of intB is 6, the variables are not equal, therefore the blnResult returns True:

vba comparison operators not equal to

Image 2. Using the Not Equal To operator

 

Greater Than

The Greater than operator checks if the first value is greater than the second value and returns True or False. Here is the example code:

Dim intA As Integer
Dim intB As Integer
Dim blnResult As Boolean

intA = 6
intB = 5

If intA > intB Then
    blnResult = True
Else
    blnResult = False
End If

In this example, we want to check if intA is greater than intB. If this is true, the value of Boolean blnResult will be True, otherwise, it will be False.

Furthermore, we use the > operator in the If statement in order to check if the value of intA is greater than intB:

If intA > intB Then
    blnResult = True
Else
    blnResult = False
End If

In conclusion, as intA is greater than intB, the blnResult returns True:

vba comparison operators greater than

Image 3. Using the Greater Than operator

 

Greater Than or Equal To

The Greater than or Equal to operator checks if the first value is greater than or equal to the second value and returns True or False. Here is the example code:

Dim intA As Integer
Dim intB As Integer
Dim blnResult As Boolean

intA = 5
intB = 5

If intA >= intB Then
    blnResult = True
Else
    blnResult = False
End If

In this example, we want to check if intA is greater than or equal to intB. If this is true, the value of Boolean blnResult will be True, otherwise, it will be False.

We use the >= operator in the If statement to check if the value of intA is greater than or equal to intB:

If intA >= intB Then
    blnResult = True
Else
    blnResult = False
End If

In conclusion, as both variables are equal to 5, the blnResult returns True:

vba comparison operators greater than or equal to

Image 4. Using the Greater Than or Equal To operator

 

Less Than

The Less than operator checks if the first value is less than the second value and returns True or False. Here is the example code:

Dim intA As Integer
Dim intB As Integer
Dim blnResult As Boolean

intA = 5
intB = 6

If intA < intB Then
    blnResult = True
Else
    blnResult = False
End If

In this example, we want to check if intA is less than intB. If this is true, the value of Boolean blnResult will be True, otherwise, it will be False.

We use the < operator in the If statement to check if the value of intA is less than intB:

If intA < intB Then
    blnResult = True
Else
    blnResult = False
End If

As intA is less than intB, the blnResult returns True:

vba comparison operators less than

Image 5. Using the Less Than operator

 

Less Than or Equal To

The Less than or Equal to operator checks if the first value is less than or equal to and returns True or False. Here is the example code:

Dim intA As Integer
Dim intB As Integer
Dim blnResult As Boolean

intA = 5
intB = 5

If intA <= intB Then
    blnResult = True
Else
    blnResult = False
End If

In this example, we want to check if intA is less than or equal to intB. If this is true, the value of Boolean blnResult will be True, otherwise, it will be False.

We use the <= operator in the If statement to check if the value of intA is less than or equal to intB:

If intA <= intB Then
    blnResult = True
Else
    blnResult = False
End If

In conclusion, as both variables are equal to 5, the blnResult returns True:

vba comparison operators less than or equal to

Image 6. Using the Less Than or Equal To operator

Is Operator

The Is Operator tests if two object variables contain the same object:

Sub CompareObjects()
Dim ws1 As Worksheet, ws2 As Worksheet
 
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
 
If ws1 Is ws2 Then
    MsgBox "Same WS"
Else
    MsgBox "Different WSs"
End If
 
End Sub

Like Operator

The Like Operator can be used to find inexact text matches. This example will test if a string starts with “Mr.”

Sub LikeDemo()

Dim strName As String
Dim blnResult As Boolean

strName = "Mr. Michael James" 

If strName Like "Mr*" Then
    blnResult = True
Else
    blnResult = False
End If

End Sub

 

If you want to learn how to compare strings, click here: VBA Compare Strings – StrComp

If you want to learn how to use comparison operators, click here: VBA Logical Operators