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 gives a birds eye view of each of these VBA operator categories.
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, ( ).
Assignment VBA Operator
VBA supports a single assignment operator, =. It stores the value of the operand on its right in the operand on its left. Afterward, both operands hold the same value.
Moreover, the operand on the right can comprise several expressions. Note that expressions are combinations of operands and operators.
Meanwhile, the identifier on the left must be a value-holding program identifier.
In any case, both operands must be of the same data type (otherwise, they can’t hold the same values).
So, the assignment operator writes the value of the right-side expression to the memory address of the left-side program identifier.
Check out this detailed article on the assignment operator. It features sample code and vital points on using the assignment operator.
Member Access VBA Operators
Member access operators ease the referencing (i.e., accessing) of class or object’s members (i.e., properties, methods, or events). There are two such VBA operators, the dot (.) and bang or exclamation point (!) operators, as described in the table below.
| Operator | Description | Outcome | Precedence |
|---|---|---|---|
| . | Dot (binary) operator grants access (read/write) to a class, object, or enumeration’s properties, methods, and events. It also allows access to a class or object’s child classes or objects, serving as the only way to traverse the VBA object hierarchy. | Gets or sets a Property-value; Calls or invokes a Method; Triggers an Event. | 1 |
| ! | Bang (binary) operator grants write-only access to a class or object’s default property – whose value must be a String. | Sets the default Property-value to a String | 1 |
As shown in the table, both member access operators have the same precedence. So, when they both appear together in an expression, evaluation proceeds in the order of appearance from left to right.
Besides, enclosing a part of an expression in parentheses prioritizes its evaluation ahead of other parts.
Check out this detailed article on member access operators. It features sample code and vital points on using the member access operators.
Concatenation VBA Operators
Concatenation operators join two String operands together. The result is a single expression with the operand after the operator appended to the operand before it.
The table below summarizes the different VBA concatenation operators.
| Operator | Description | Return Data Type | Precedence |
|---|---|---|---|
| & | String Concatenation (binary) operator, i.e. ampersand (&), appends two String or String Variant operands into a String expression. | String, if both operands are String; String Variant, if at least one operand is not a String; Null, if both operands are Null. | 1 |
| + | String Concatenation (binary) operator, i.e. plus (+), appends two Strings, two String Variants, or one String and any Variant (except Null or numeric types) operands into a String expression. | String, if both operands are Strings; String Variant, if both operands are String Variants; String Variant, if one operand is String and the other is any Variant (except Null or numeric types); Null, if either operand is Null. | 1 |
As shown in the table, both concatenation operators have the same precedence. So, when they both appear together in an expression, evaluation proceeds in the order of appearance from left to right.
Also, enclosing a part of an expression in parentheses causes it to be evaluated ahead of other parts. Moreover, note that the arithmetic plus (+) operator has precedence over concatenation plus (+).
Check out this detailed article on concatenation operators. It features sample code and vital points on using the concatenation operators.
Arithmetic VBA Operators
Arithmetic operators perform simple mathematic operations. Their operations include addition, subtraction, division, multiplication, exponentiation and modulus. The table below summarizes the different VBA arithmetic operators.
| Operator | Description | Return Data Type | Order |
|---|---|---|---|
| ^ | Exponentiation (binary) operator, i.e. caret (^), raises a number to the power of an exponent. | Double or Double Variant (i.e., a Variant holding data of Double data type); Null, if one or each operand is a Null expression. | 1 |
| - | Negation (unary) operator, i.e. dash (-), turns a numeric value negative (or positive, if the value was already negative). | Most precise type for the result – precision increasing in the order: Byte, Integer, Long, Single, Double, Currency, and Decimal; Null, if the operand is a Null expression. | 2 |
| * | Multiplication (binary) operator, i.e. asterisk (*), returns the product of two numbers. | Most precise type for the result – precision increasing in the order: Byte, Integer, Long, Single, Currency, Double, and Decimal; Null, if one or each operand is a Null expression. | 3 |
| / | Division (binary) operator, i.e. forward-slash (/), returns the floating-point result of dividing two numbers. | Double or Double Variant; Null, if one or each operand is a Null expression. | 3 |
| \ | Integer Division (binary) operator, i.e. backslash (\), returns the result (with fractional part cut off) of dividing two numbers (first rounded to Byte, Integer, or Long, if floating-point). | Byte, Byte Variant, Integer, Integer Variant, Long, or Long Variant; Null, if one or each operand is a Null expression. | 4 |
| Mod | Modulus (binary) operator, Mod, returns the remainder (with fractional part cut off) from dividing two numbers (first rounded to Integer, if floating-point). | Byte, Byte Variant, Integer, Integer Variant, Long, or Long Variant; Null, if one or each operand is a Null expression. | 5 |
| + | Addition (binary) operator, i.e. plus (+), returns the sum of two numbers. | Most precise type for the result – precision increasing in the order: Byte, Integer, Long, Single, Double, Currency, and Decimal; Null, if operand is a Null expression; Integer, if both operands are Empty. | 6 |
| - | Subtraction (binary) operator, i.e. dash (-), returns the result of deducting one number from another. | Most precise type for the result – precision increasing in the order: Byte, Integer, Long, Single, Double, Currency, and Decimal; Null, if operand is a Null expression. | 6 |
When several arithmetic operators with the same precedence appear together in an expression, evaluation proceeds in the order of appearance from left to right.
Also, enclosing a part of an expression in parentheses causes it to be evaluated ahead of other parts.
Check out this detailed article on arithmetic operators. It features sample code and vital points on using the arithmetic operators.
Comparison VBA 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.
| Operator | Description | Return Data Type | Order |
|---|---|---|---|
| = | Equality (binary) operator tests whether the values held by its two operands are equal/identical. | Boolean – True (i.e., ≠ 0) if the values held by the operands are equal, otherwise it returns False (i.e., = 0). | 1 |
| < > | Inequality (binary) operator tests whether the values held by its two operands are NOT equal/identical. | Boolean – True (i.e., ≠ 0) if the values held by the operands are NOT equal, otherwise it returns False (i.e., = 0). | 1 |
| > | Greater Than (binary) operator tests whether the value held by the operand to the left of the operator is greater than that held by the operand to its right. | Boolean – True (i.e., ≠ 0) if the value held by left-side operand is greater than the value held by the right-side operand, otherwise it returns False (i.e., = 0). | 1 |
| > = | Greater Than or Equal To (binary) operator tests whether the value held by the operand to the left of the operator is greater than or equal to that held by the operand to its right. | Boolean – True (i.e., ≠ 0) if the value held by left-side operand is greater than or equal to the value held by the right-side operand, otherwise it returns False (i.e., = 0). | 1 |
| < | Greater Than (binary) operator tests whether the value held by the operand to the left of the operator is less than that held by the operand to its right. | Boolean – True (i.e., ≠ 0) if the value held by left-side operand is less than the value held by the right-side operand, otherwise it returns False (i.e., = 0). | 1 |
| < = | Greater Than or Equal To (binary) operator tests whether the value held by the operand to the left of the operator is less than or equal to that held by the operand to its right. | Boolean – True (i.e., ≠ 0) if the value held by left-side operand is less than or equal to the value held by the right-side operand, otherwise it returns False (i.e., = 0). | 1 |
| Is | Object Reference Comparison (binary) operator tests whether two object variables reference (i.e., point to) the same object (i.e., memory address). | Boolean – True (i.e., ≠ 0) if the object variables refer to the same object, otherwise it returns False (i.e., = 0). | 1 |
| Like | String Pattern Comparison (binary) operator tests whether two String operands match, i.e. have the same number of characters and in the same sequence. | Boolean – True (i.e., ≠ 0) if the left-side operand matches the right-side operand, otherwise False (i.e., = 0); Null, if either operand is Null. | 1 |
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 it to be evaluated ahead of other parts.
Check out this detailed article on comparison operators. It features sample code and vital points on using the comparison operators.
Logical VBA Operators
Logical operators perform logical negation, conjunction, disjunction, exclusion, equivalence, and implication operations. They do so on either one operand (unary operation) or a pair of operands (binary operation).
The table below summarizes the various logical operators supported by VBA.
| Operator | Description | Return Data Type | Order |
|---|---|---|---|
| Not | Logical Negation (unary) operator returns the reverse logical result of an expression. | Boolean – True (i.e., ≠ 0) if the expression is False (i.e., = 0); Boolean – False if the expression is True; Null if the expression is Null. | 1 |
| And | Logical Conjunction (binary) operator performs a logical conjunction on two expressions. | Boolean – True if both expressions are True; Boolean – False if either expression is False; Null if either expression is Null. | 2 |
| Or | Logical Disjunction (binary) operator performs a logical disjunction on two expressions. | Boolean – True if either expression is True; Boolean – False if both expressions are False; Null if both expressions are Null, or one expression is Null and the other is False. | 3 |
| Xor | Logical Exclusion (binary) operator performs a logical exclusion on two expressions. | Boolean – True if one, and only one, expression is True; Boolean – False if both expressions are True or False; Null if either expression is Null. | 4 |
| Eqv | Logical Equivalence (binary) operator performs a logical equivalence on two expressions. | Boolean – True if both expressions are True or False; Boolean – False if one expression is True and the other is False, and vice versa; Null if either expression is Null. | 5 |
| Imp | Logical Implication (binary) operator performs a logical implication on two expressions. | Boolean – True if:
Boolean – False if, and only if, the first expression is True, and the second expression is False; Null if either expression is Null. | 6 |
As shown in the table, all VBA logical operators have different precedence. So, when several of them appear in an expression, evaluation proceeds in the order of appearance from left to right.
Also, enclosing a part of an expression in parentheses causes it to be evaluated ahead of other parts.
Check out this detailed article on logical operators. It features sample code and vital points on using the logical operators.
![The While – Wend statement’s syntax. Square brackets, [ ], indicate optional items.](https://masterofficevba.com/wp-content/uploads/2021/03/While_Wend_Statement_Syntaxnew.png)
![The Do – Loop Until statement’s syntax. Square brackets, [ ], indicate optional items while vertical bars, |, indicate mutually exclusive items.](https://masterofficevba.com/wp-content/uploads/2021/03/Do_Loop_Until_Statement_Syntaxnew.png)
![The Do – Loop While statement’s syntax. Square brackets, [ ], indicate optional items while vertical bars, |, indicate mutually exclusive items.](https://masterofficevba.com/wp-content/uploads/2021/03/Do_Loop_While_Statement_Syntaxnew.png)
![The Do Until – Loop statement’s syntax. Square brackets, [ ], indicate optional items.](https://masterofficevba.com/wp-content/uploads/2021/03/Do_Until_Loop_Statement_Syntaxnew.png)
![The While – Wend statement’s syntax. Square brackets, [ ], indicate optional items.](https://masterofficevba.com/wp-content/uploads/2021/03/While_Wend_Statement_Syntaxnew-300x163.png)
![The Do – Loop Until statement’s syntax. Square brackets, [ ], indicate optional items while vertical bars, |, indicate mutually exclusive items.](https://masterofficevba.com/wp-content/uploads/2021/03/Do_Loop_Until_Statement_Syntaxnew-300x163.png)