Understanding Operator Precedence





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. Figure 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 Figure 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 following formula:

graphics/04inf01.gif

Listing 4.1 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

Figure.1 shows an Excel worksheet that uses this function. The value in cell B4 is passed to the totalCost argument and the value in cell B1 is passed to the taxRate argument.

1. A function that attempts to calculate the pre-tax cost of an item.

graphics/04fig01.jpg

As you can see, the result is incorrect. What happened? Well, according to the rules of precedence, VBA performs division before addition, so the totalCost value first is divided by 1 and then is added to the taxRate value, which isn't the correct order.

To get the correct answer, you have to override the order of precedence so the addition 1 + taxRate is performed first. You do this by surrounding that part of the expression with parentheses, as in Listing 4.2. Using this revised function, you get the correct answer, as shown in Figure.2.

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

2. The revised function calculates the pre-tax cost correctly.

graphics/04fig02.jpg

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.

caution

graphics/caution_icon.gif

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 left out a parenthesis.


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.


     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows