Creating a Simple Custom Function

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

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

Note

You’ll find the TreeOrders.xlsm file with the other examples on the companion Web site. You can use this file for reference or to practice creating your own custom functions.

In column F, we want to calculate the discount for each item ordered.

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

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 28-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 runs with or without indentation.) After you type an indented line, the Visual Basic Editor assumes your next line should 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
Clicking Insert, Module adds a new module to the workbook.

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

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset