Feb. 15, 2011, 2:28 p.m.

posted by dante

## Understanding Operator PrecedenceYou'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 PrecedenceThe 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 PrecedenceSometimes 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. ## A First Attempt at Calculating the Pre-Tax CostFunction 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 ## 1. A function that attempts to calculate the pre-tax cost of an item.As you can see, the result is incorrect. What happened? Well, according to the rules of precedence, VBA performs division before addition, so the To get the correct answer, you have to override the order of precedence so the addition ## The Correct Way to Calculate the Pre-Tax CostFunction 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.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. caution 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. |

- Comment