Understanding Operator Precedence

Submitted by ravisagar on Sun, 07/06/2008 - 06:03.

book
VBA

You'll often use simple expressions that contain just two values and a single operator. In practice, however, many expressions you use will have a number of values and operators. In these more complex expressions, the order in which the calculations are performed becomes crucial. For example, consider the expression 3+5^2. If you calculate from left to right, the answer you get is 64 (3+5 equals 8 and 8^2 equals 64). However, if you perform the exponentiation first and then the addition, the result is 28 (5^2 equals 25 and 3+25 equals 28). As this example shows, a single expression can produce multiple answers depending on the order in which you perform the calculations.

To control this problem, VBA evaluates an expression according to a predefined order of precedence. This order of precedence lets VBA calculate an expression unambiguously by determining which part of the expression it calculates first, which part second, and so on.

The order of precedence that VBA uses is determined by the various expression operators I outlined in the preceding Table.



Operator Operation Order of Precedence
^ Exponentiation First
- Negation Second
* and / Multiplication and division Third
\ Integer divison Fourth
Mod Modulus Fifth
+ and - Addition and subtraction Sixth
& Concatenation Seventh
= < > <= >= <> Comparison Eighth
And Eqv Imp Or Xor Not Logical Ninth

Parantheses
you can use parentheses to control the order that VBA uses to calculate expressions. Terms inside parentheses are always calculated first; terms outside parentheses are calculated sequentially (according to the order of precedence). To gain even more control over your expressions, you can place parentheses inside one another; this is called nesting parentheses, and VBA always evaluates the innermost set of parentheses first. Here are a few
sample expressions:



Expression First Step Second Step Third Step Result
3^(15/5)*2–5 3^3*2–5 27*2–5 54–5 49
3^((15/5)*2–5) 3^(3*2–5) 3^(6–5) 3^1 3
3^(15/(5*2–5)) 3^(15/(10–5)) 3^(15/5) 3^3 27

Notice that the order of precedence rules also hold within parentheses. For example, in the expression (5*2–5), the term 5*2 is calculated before 5 is subtracted. Using parentheses to determine the order of calculations allows you to gain full control over VBA expressions. This way, you can make sure that the answer given by an expression is the
one you want.