| 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 4.4 summarizes the complete order of precedence used by VBA. Table 4.4. 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 4.4 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. Listing 4.1. 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 4.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. Figure 4.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 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 4.2. Listing 4.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 Figure 4.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: | 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  | 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. | 