Feb. 15, 2011, 2:28 p.m.
posted by dante
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.
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:
Listing 4.1 shows a function that attempts to implement this formula.
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.
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.
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:
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.