MS-Excel / General Formatting

---

Understanding Operator Precedence

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

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

The VBA Order of Precedence

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

From this table, you can see that VBA performs exponentiation before addition. Therefore, the correct answer for the expression 3+5^2 (just discussed) is 28.

Notice, as well, that some operators in Table above have the same order of precedence (for example, multiplication and division). This means that it doesn't matter in which order these operators are evaluated. For example, consider the expression 5*10/2. If you perform the multiplication first, the answer you get is 25 (5*10 equals 50, and 50/2 equals 25). If you perform the division first, you also get an answer of 25 (10/2 equals 5, and 5*5 equals 25). By convention, VBA evaluates operators with the same order of precedence from left to right.

Controlling the Order of Precedence

Sometimes you want to override the order of precedence. For example, suppose you want to create an expression that calculates the pre-tax cost of an item. If you bought something for $10.65, including 7 percent sales tax, and you wanted to find the cost of the item less the tax, you'd use the expression 10.65/1.07, which gives you the correct answer of $9.95. In general, the expression to use is given by the formula shown below.

Listing below shows a function that attempts to implement this formula.

A First Attempt at Calculating the Pre-Tax Cost

Function PreTaxCost(totalCost As Currency, taxRate As Single) As Currency
PreTaxCost = totalCost / 1 + taxRate
End Function

The Correct Way to Calculate the Pre-Tax Cost

Function PreTaxCost2(totalCost As Currency, taxRate As Single) As Currency
PreTaxCost2 = totalCost / (1 + taxRate)
End Function

In general, 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 gives you full control over VBA expressions. This way, you can make sure that the answer given by an expression is the one you want.

Caution: One of the most common mistakes when using parentheses in expressions is to forget to close a parenthetic term with a right parenthesis. If you do this,VBA displays an Expected: ) message. To make sure you've closed each parenthetic term, count all the left parentheses and count all the right parentheses. If these totals don't match, you know you've either left out a parenthesis or included too many.