Example
| Token | Name | Description |
|---|---|---|
= | Equal to | Returns True if the left-hand and right-hand operands are equal. Note that this is an overload of the assignment operator. |
<> | Not equal to | Returns True if the left-hand and right-hand operands are not equal. |
> | Greater than | Returns True if the left-hand operand is greater than the right-hand operand. |
< | Less than | Returns True if the left-hand operand is less than the right-hand operand. |
>= | Greater than or equal | Returns True if the if the left-hand operand is greater than or equal to the right-hand operand. |
<= | Less than or equal | Returns True if the if the left-hand operand is less than or equal to the right-hand operand. |
Is | Reference equity | Returns True if the left-hand object reference is the same instance as the right-hand object reference. It can also be used with Nothing (the null object reference) on either side. Note: The Is operator will attempt to coerce both operands into an Object before performing the comparison. If either side is a primitive type or a Variant that does not contain an object (either a non-object subtype or vtEmpty), the comparison will result in a Run-time error 424 - "Object required". If either operand belongs to a different interface of the same object, the comparison will return True. If you need to test for equity of both the instance and the interface, use ObjPtr(left) = ObjPtr(right) instead. |
Notes
The VBA syntax allows for "chains" of comparison operators, but these constructs should generally be avoided. Comparisons are always performed from left to right on only 2 operands at a time, and each comparison results in a Boolean. For example, the expression...
a = 2: b = 1: c = 0
expr = a > b > c
...may be read in some contexts as a test of whether b is between a and c. In VBA, this evaluates as follows:
a = 2: b = 1: c = 0
expr = a > b > c
expr = (2 > 1) > 0
expr = True > 0
expr = -1 > 0 'CInt(True) = -1
expr = False
Any comparison operator other than Is used with an Object as an operand will be performed on the return value of the Object's default member. If the object does not have a default member, the comparison will result in a Run-time error 438 - "Object doesn't support his property or method".
If the Object is unintitialized, the comparison will result in a Run-time error 91 - "Object variable or With block variable not set".
If the literal Nothing is used with any comparison operator other than Is, it will result in a Compile error - "Invalid use of object".
If the default member of the Object is another Object, VBA will continually call the default member of each successive return value until a primitive type is returned or an error is raised. For example, assume SomeClass has a default member of Value, which is an instance of ChildClass with a default member of ChildValue. The comparison...
Set x = New SomeClass
Debug.Print x > 42
...will be evaluated as:
Set x = New SomeClass
Debug.Print x.Value.ChildValue > 42
If either operand is a numeric type and the other operand is a String or Variant of subtype String, a numeric comparison will be performed. In this case, if the String cannot be cast to a number, a Run-time error 13 - "Type mismatch" will result from the comparison.
If both operands are a String or a Variant of subtype String, a string comparison will be performed based on the Option Compare setting of the code module. These comparisons are performed on a character by character basis. Note that the character representation of a String containing a number is not the same as a comparison of the numeric values:
Public Sub Example()
Dim left As Variant
Dim right As Variant
left = "42"
right = "5"
Debug.Print left > right 'Prints False
Debug.Print Val(left) > Val(right) 'Prints True
End Sub
For this reason, make sure that String or Variant variables are cast to numbers before performing numeric inequity comparisons on them.
If one operand is a Date, a numeric comparison on the underlying Double value will be performed if the other operand is numeric or can be cast to a numeric type.
If the other operand is a String or a Variant of subtype String that can be cast to a Date using the current locale, the String will be cast to a Date. If it cannot be cast to a Date in the current locale, a Run-time error 13 - "Type mismatch" will result from the comparison.
Care should be taken when making comparisons between Double or Single values and Booleans. Unlike other numeric types, non-zero values cannot be assumed to be True due to VBA's behavior of promoting the data type of a comparison involving a floating point number to Double:
Public Sub Example()
Dim Test As Double
Test = 42 Debug.Print CBool(Test) 'Prints True.
'True is promoted to Double - Test is not cast to Boolean
Debug.Print Test = True 'Prints False
'With explicit casts:
Debug.Print CBool(Test) = True 'Prints True
Debug.Print CDbl(-1) = CDbl(True) 'Prints True
End Sub
