Comparison Operators in VBA

VBA Operators

VBA operators are special tokens that instruct the compiler to “operate” on values or program identifiers. They are also referred to as program symbols or code elements. Also, the program identifiers they operate on (e.g., addition) must be capable of holding values.

You may have heard of the term operand. These are the values (i.e., literals) or identifiers (e.g., variables and constants) that operators act on. Based on their number of operands, operators are either unary or binary.

Unary VBA operators are operators that only act on a single operand at a time. Binary VBA operators are those that only act on two operands at a time.

VBA supports the following categories of operators:

• Assignment Operator;
• Member Access Operators;
• Concatenation Operators;
• Arithmetic Operators;
• Comparison Operators;
• Logical Operators.

This article takes an in-depth look at the VBA comparison operators.

VBA Operator Precedence

Often, an operation involves more than one operator, sometimes of different categories. So, it is worthwhile paying special attention to the order of precedence of the VBA operators. Otherwise, VBA’s default operator precedence applies.

The default precedence is as follows: arithmetic first, then concatenation, followed by comparison, then logical, and finally assignment operators.

Moreover, there is a pre-set precedence for operators within each category. How do you override this default order and remove any ambiguity? Easy, set the preferred precedence with open and close parentheses, ( ).

Comparison Operators

Comparison operators perform relational, object equality, and string match operations on a pair of operands. The table below summarizes the various comparison operators supported by VBA.

As shown in the table, comparison operators all have equal precedence. So, when several of them appear together in an expression, their evaluation proceeds in the order of appearance from left to right.

Also, enclosing a part of an expression in parentheses causes its evaluation ahead of other parts.

Comparison Operators: Relational (=, <>, >, >=, <, and <=)

The sample code below illustrates the =, <>, >, >=, <, and <= comparison operators’ usage.

Comparison Operators: Object Reference (Is)

The sample code below demonstrates the Is (or Object Reference) comparison operator’s usage.

Comparison Operators: String Matching (Like)

String comparisons depend on the Option Compare statement placed atop the module (i.e., before the code for any procedures). The Like comparison operator is not exempt.

Option Compare Binary is the default setting and amounts to case-sensitive comparisons. On the other hand, Option Compare Text leads to case-insensitive comparisons.

Note that the Like operator can’t be used in a Select Case statement.

The sample code below shows the Like comparison operator’s usage.

Using Wildcards with the Like Comparison Operator

VBA supports built-in pattern matching using wildcard characters to turbocharge string comparisons. You can include wildcard characters, character lists, or character ranges (or a mix of these) in the Like operator’s operands to achieve the desired match.

The table below summarizes the valid VBA pattern matching wildcards.

The sample code below illustrates the power of combining wildcards and the Like comparison operator.

Sometimes the wildcard characters, left bracket ‘[,‘ question mark ‘?,’ hash ‘#,’ asterisk ‘*,’ and right bracket ‘],’ themselves may need to be matched in a string.

While the left bracket ‘[,‘ question mark ‘?,’ hash ‘#,’ and asterisk ‘*’ can be matched by placing them inside brackets, “[ ],” the right bracket, ‘],’ cannot be used inside a group (i.e. [charlist]) to match itself.

Instead, as shown in the sample code below, match the right bracket, ‘],’ like any regular individual character, by placing it outside a group.

You’ll often need to match a character inside or outside a character-range to a character in a string. Think of digits (0-9), uppercase letters (A-Z), and lowercase letters (a-z) character-ranges.

In such cases, use the [charlist], or [!charlist], to capture the range. Also, separate the range’s upper and lower bounds with a hyphen, (-).

Moreover, multiple ranges can be written in a single charlist. Delimiters (e.g., space, comma) are invalid between each range.

The sample code below illustrates the foregoing string matching capability.

Note that character ranges must be specified in ascending sort order (i.e., smallest to largest). So, [0-9] is a valid pattern, but [9-0] is not. Lastly, the compiler treats an empty character list, i.e. [], as a zero-length string (“”).