Creating a Simple Custom Function

Suppose your company offers a quantity discount of 10 percent on the sale of a product, provided the order is for more than 100 units. In the following paragraphs, you'll build a function to calculate this discount.

The worksheet in Figure 27-1 shows an order form that lists each item, the quantity, the price, the discount (if any), and the resulting extended price.

Figure 27-1: In column F, we want to calculate the discount for each item ordered.

On the CD You'll find the  Treeorders.xlsm file in the Sample Files section of the companion CD. You can use this file for reference or to practice creating your own custom functions.

To create a custom DISCOUNT function in this workbook, follow these steps:

1. Press Alt+F11 to open the Visual Basic Editor, and then click Insert, Module. A new module appears, as shown in Figure 27-2.

2. In the new module, type the following code. To make the code more readable, use the Tab key to indent lines. (The indentation is for your benefit only and is optional. The code will run with or without indentation.) After you type an indented line, the Visual Basic Editor assumes your next line will be similarly indented. To move out (that is, to the left) one tab character, press Shift+Tab.

    Function Discount(quantity, price)     If quantity >=100 Then         Discount = quantity * price * 0.1     Else         Discount = 0     End If     Discount = Application. Round(Discount, 2) End Function 

Figure 27-2: Clicking Insert, Module adds a new module to the workbook.