Blog

In the previous tutorial, we learned about VBA Operators. In this lesson, we’ll explore the concept of Operator Precedence in VBA and how it affects the order of calculations. Understanding this concept is crucial because the order in which operators are executed can completely change the final result of your formula or condition.

Definition of Operator Precedence

Operator precedence is a set of rules that determine which operator should be evaluated first when an expression contains multiple operators. The order of execution in VBA is similar to mathematical rules, except that VBA also includes logical and comparison operators in addition to arithmetic ones.

Numerical Example

x = 12 * 2 + (1 + 5) / 2

If this expression is calculated from left to right without considering operator precedence, the result would be 15. However, following the precedence rules, the correct result is 27.

Step-by-Step Breakdown

1️⃣ First, the parentheses are calculated: (1 + 5) = 6

x = 12 * 2 + 6 / 2

2️⃣ Multiplication (*) and division (/) have higher precedence than addition or subtraction and are evaluated from left to right:

x = 24 + 3

3️⃣ Finally, the addition is performed, giving the result 27.

General Operator Precedence Order in VBA

In VBA, the order of operator evaluation (from highest to lowest precedence) is as follows:

(1) Parentheses ()
(2) Exponentiation ^
(3) Unary negation -
(4) Multiplication and Division * /
(5) Integer Division \
(6) Modulus Mod
(7) Addition and Subtraction + -
(8) String Concatenation &
(9) Comparison Operators = < > <= >= Like Is
(10) Logical Operators Not, And, Or, Xor, Eqv, Imp

As you can see, arithmetic operators are evaluated first, followed by comparison operators, and finally logical operators.

Example: Logical Operators

In the following example, even though Or comes before And, And is evaluated first because it has higher precedence:

Debug.Print True Or False And False   ' Result: True

This means that False And False → False is calculated first, and then True Or False → True.

Important Notes about Operator Precedence

• Grouping: Arithmetic operators are evaluated first, then comparison, and finally logical ones.
• Equal precedence: When multiple operators have the same level, VBA evaluates them from left to right (for example, * and /).
• Parentheses: Parentheses always take priority and force early evaluation of the enclosed expression.
• & Operator: Used for string concatenation, and it appears between arithmetic and comparison operators in precedence.
• Like and Is Operators: Both are considered comparison operators.

To avoid logical errors in formulas or conditional statements, always use parentheses in complex expressions to make the order of evaluation explicit.

Reference

Microsoft Docs: Operator Precedence (VBA)