Listing 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.3 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.3

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

Figure 4.3

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

Chapter04

_

S

1 w

A

B

C

D

E

F

fi

Sales Tax Rate:

7%

2

3

Price

Pre-Tax Cost

-1

4

110.65 1 S10.72

5

6

7

S

-

H

► * Without Precedence Control With Pre

Hi

H

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.4.

0 0

Post a comment