Types and precedence of VBA operators

operator to perform a specific type of operation on an element in a formula. Microsoft Excel contains four types of operators: arithmetic, comparison, literal, and reference operators.

Type of operator

Arithmetic operators to complete basic mathematical operations, such as addition, subtraction, and multiplication, connecting numbers, and generating numeric results, use the following arithmetic operators:

Arithmetic operator meaning (sample)

+ (PLUS) addition operation (3+3)

– (minus) subtraction operation (3–1)

Negative (–1)

* (asterisk) multiplication operation (3*3)

/(forward slash) Division operations (3/3)

% (Percent semicolon) percentage (20%)

^ (caret) power operation (3^2)

Comparison operators can use the following operators to compare two values. When you compare two values with an operator, the result is a logical value, either TRUE or FALSE.

Comparison operator meaning (sample)

= (equal sign) equals (A1=B1)

> (greater than) greater than (A1>B1)

< (less than) less than (A1<B1)

>= (greater than equals number) greater than or equal to (A1>=B1)

<= (less than equals number) less than or equal to (A1<=B1)

<> (Not equal) unequal (A1<>B1)

The text concatenation operator joins or joins one or more text strings using the and number (&) to produce a string of text.

Literal operator meaning (sample)

& (and number) Connect or concatenate two text values to produce a continuous text value ("North" & "Wind")

Reference operators use the following operators to consolidate a range of cells.

Reference operator meaning (sample)

: (colon) area operator, producing a reference to all cells that are included between two references (B5:B15)

, (comma) union operator, merging multiple references into a single reference (SUM (B5:B15,D5:D15))

(space) crossover operator produces a reference to a cell that is common to two references. (B7:d7 C6:c8)

Order of operations in a formula

Formulas calculate values in a particular order. Formulas in Excel are usually started with an equal sign (=) to indicate that the following characters are formulas. followed by the equals sign is the element (operand) that needs to be evaluated, separated by an operator between the operands. Excel calculates the formula from left to right based on the specific order of the operators in the formula.

Operator Precedence

If you use more than one operator in a formula, Excel operates in the order shown in the following table. If the formula contains operators of the same precedence, for example, the multiplication and division operators are included in the formula, Excel evaluates from left to right.

Operator description

: (colon)

(Single space)

, (comma)

Reference operators

– Minus sign (for example – 1)

% percent

^ exponentiation

* and/multiplication and division

+ and – plus and minus

& Connect two text strings (connect)

= < > <= >= <> comparison operator

Use parentheses

To change the order of the evaluation, enclose the part of the formula that you want to calculate first in parentheses. For example, the result of the following formula is 11, because Excel makes the addition operation after multiplication. Multiply 2 with 3, then add 5, and you get the result.

=5+2*3

On the contrary. If you use parentheses to change the syntax, Excel first uses 5 plus 2, then multiplies the result by 3, and gets the result 21.

= (5+2) *3

In the following example, the parentheses in the first part of the formula indicate that Excel should first compute b4+25 and then divide the sum of the values in cell D5, E5, and F5.

= (b4+25)/sum (D5:F5)