Comparison Operators

Used to compare two expressions.

Syntax

result = expr1 operator expr2

Remarks

Comparison operators, also known as relational operators, compare two expressions.  The following table contains a list of the comparison operators and the conditions that determine whether result is True, False, or Null:

 Operator Meaning True if False if Null if

 < Less than expr1 < expr2 expr1 >= expr2 expr1 or expr2 = Null <= Less than or equal to expr1 <= expr2 expr1 > expr2 expr1 or expr2 = Null > Greater than expr1 > expr2 expr1 <= expr2 expr1 or expr2 = Null >= Greater than or equal to expr1 >= expr2 expr1 < expr2 expr1 or expr2 = Null = Equal to expr1 = expr2 expr1 <> expr2 expr1 or expr2 = Null <> Not equal to expr1 <> expr2 expr1 = expr2 expr1 or expr2 = Null

When comparing two expressions, you may not be able to easily determine whether the expressions are being compared as numbers or as strings.  The following table shows how the expressions are compared or what results when either expression is not a Variant:

 If Then

 Both expressions are numeric data types (Integer, Long, Single, Double, or Currency) Perform a numeric comparison. Both expressions are String Perform a string comparison. One expression is a numeric data type and the other is a Variant of VarType 2-7 (a numeric data type) or VarType 8 (String) that can be converted to a number Perform a numeric comparison. One expression is a numeric data type and the other is a Variant of VarType 8 (String) that can't be converted to a number A Type Mismatch error occurs. One expression is a String and the other is a Variant of any VarType except Null Perform a string comparison. One expression is Empty and the other is a numeric data type Perform a numeric comparison, using 0 as the Empty expression. One expression is Empty and the other is a String Perform a string comparison, using a zero-length string as the Empty expression. One expression is a numeric data type and the other is a Variant of VarType 8 (String) A Type Mismatch error occurs.

If expr1 and expr2 are both Variant expressions, their VarType determines how they are compared.  The following table shows how the expressions are compared or what results from the comparison, depending on the VarType of the Variant:

 If Then

 Both Variant expressions are of VarType 2-7 (numeric data types) Perform a numeric comparison. Both Variant expressions are of VarType 8 (String) Perform a string comparison. One Variant expression is of VarType 2-7 (a numeric data type) and the other is of VarType 8 (String) The numeric expression is less than the String expression. One Variant expression is Empty and the other is of VarType 2-7 (a numeric data type) Perform a numeric comparison, using 0 as the Empty expression. One Variant expression is Empty and the other is of VarType 8 (String) Perform a string comparison, using a zero-length string as the Empty expression. Both Variant expressions are Empty The expressions are equal.

Note   If a Currency is compared with a Single or Double, the Single or Double is converted to a Currency.  This causes any fractional part of the Single or Double value less than 0.0001 to be lost and may cause two values to compare as equal when they are not.  Such a conversion can also cause an Overflow error if the magnitude of the Single or Double is too large.  When a Single is compared to a Double, the Double is rounded to the precision of the Single.

Arithmetic Operators

Concatenation Operators

Like Operator

Logical Operators

Operator Precedence

Option Compare Statement

Other Operators

VarType Function

Comparison Operators Example

The example shows a typical use of a comparison operator to evaluate the relationship between variables A and B.  An appropriate message is displayed depending on whether the A < B, A = B, or A > B.  The other comparison operators can be used in a similar way.  To try this example, paste the code into the Declarations section of a form.  Then press F5 and click the form.

Sub Form_Click ()

Dim A, B, Msg                         ' Declare variables.

A = InputBox("Enter a value for A.")  ' Get first variable.

B = InputBox("Enter a value for B.")  ' Get second variable.

If A < B Then                         ' Evaluate relationship.

Msg = "A is less than B."          ' Create correct message.

ElseIf A = B Then

Msg = "A is equal to B."

Else

Msg = "A is greater than B."

End If

MsgBox Msg                            ' Display results.

End Sub