Using Custom Functions

Now you're ready to use the new DISCOUNT function. Press Alt+F11 to switch to the worksheet shown in Figure 27-1. Select cell F9, and type the following:

 = DISCOUNT(C9, D9) 

Excel calculates the 10 percent discount on 200 units at $47. 50 per unit and returns $950. 00.

In the first line of your VBA code, Function Discount(quantity, price), you indicated that the DISCOUNT function requires two arguments, quantity and price. When you call the function in a worksheet cell, you must include those two arguments. In the formula =DISCOUNT(C9, D9), C9 is the quantity argument, and D9 is the price argument. Now you can copy the DISCOUNT formula to F10: F15 to get the worksheet shown in Figure 27-3.

Figure 27-3: This worksheet shows the result of the DISCOUNT custom function.

Let's consider how Excel interprets this function procedure. When you press Enter, Excel looks for the name DISCOUNT in the current workbook and finds that it is a procedure in Module1. The argument names enclosed in parentheses-quantity and price-are placeholders for the values on which the calculation of the discount is based.

The If statement in the following block of code examines the quantity argument and determines whether the number of items sold is greater than or equal to 100:

 If quantity >= 100 Then     Discount = quantity * price * 0.1 Else     Discount = 0 End If 

If the number of items sold is greater than or equal to 100, VBA executes the following statement, which multiplies the quantity value by the price value and then multiplies the result by 0.1:

 Discount = quantity * price * 0.1 

The result is stored as the variable Discount. A VBA statement that stores a value in a variable is called an assignment statement, because it evaluates the expression on the right side of the equal sign and assigns the result to the variable name on the left. Because the variable Discount has the same name as the function procedure, the value stored in the variable is returned to the worksheet formula that called the DISCOUNT function.

If quantity is less than 100, VBA executes the following statement:

 Discount = 0 

Finally, the following statement rounds the value assigned to the Discount variable to two decimal places:

 Discount = Application. Round(Discount, 2) 

VBA has no ROUND function, but Excel does. Therefore, to use ROUND in this statement, you tell VBA to look for the Round method (function) in the Application object (Excel). You do that by adding the word Application before the word Round. Use this syntax whenever you need to access an Excel function from a VBA module.