VBA Not Equal to, Greater Than or Equal To & Other Comparison Operators
Written by
Reviewed by
In this Article
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.
Main Comparison Operators in VBA
These are the main comparison operators used in VBA:
Comparison Operator |
Explanation |
= |
Equal to |
<> |
Not Equal to |
> |
Greater than |
>= |
Greater than or Equal to |
< |
Less than |
<= |
Less than or Equal to |
Not Equal To (<>)
The Not Equal to operator is <>. It checks if two values are not equal and returns TRUE or FALSE. It’s a combination of the Less Than and Greater Than operators.
This example will test if 5 does not equal 3 and return FALSE in a MessageBox:
MsgBox 5 <> 3
Cell Value Not Equal To
There are several ways you might compare numbers. In the previous example, we hard-coded 5 and 3 into our code. Let’s demonstrate two other ways to compare values.
This example will test if two cell values are not equal to each other:
MsgBox Range("A1").Value <> Range("B1").value
Another way to compare values is with Variables.
Sub NotEqualTo ()
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
End Sub
In this example, we want check if Integer Variable intA is not equal to intB. If this is true, the value of Boolean variable 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:
Equal To
The Equal to operator works exactly the same. It checks if two values are equal and returns True or False. Here is the example code:
Sub EqualTo ()
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
End Sub
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:
Greater Than / Less Than
The Greater Than (>) and Less Than (<) operators work exactly the same way.
Greater Than
MsgBox 5 > 3
Less Than
MsgBox 5 < 3
Greater Than or Equal To / Less Than or Equal To
To test if a value is Greater Than or Equal To, combine the Equal To (=) operator and the Greater Than (>) / Less Than (<) operators.
Greater Than or Equal To
MsgBox 5 >= 3
Less Than or Equal To
MsgBox 5 <= 3
Let’s use Greater Than or Equal To in the same procedure we have been using:
Sub GreaterThanEqualTo ()
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
End Sub
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:
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 more about how to use logical operators, click here: VBA Logical Operators