Let us consider a more complex example of creating a user-defined function. Suppose you are a manager responsible for wholesale book sales in a publishing house. To attract customers, your publishing house has introduced a progressive pricing scale.
- If 100 to 200 copies of a book are sold, the discount from its retail price is 7%.
- If 201 to 300 copies are sold, the discount is 10%.
- If more than 300 copies are sold, the discount is 15%.
In addition, for regular customers, an additional 5% discount is provided.
Let us create a user-defined function named Cost to calculate the cost of a batch of books. The parameters of this function will be called PricePerBook, Quantity, and Discount. For the Discount parameter, only two values are allowed: 1 — for regular customers, and 0 — for all others.
We define the user-defined function Cost with the following code:
Function Cost(PricePerBook, Quantity, Discount) If Quantity < 100 Then CostWithoutDiscount = PricePerBook * Quantity ElseIf Quantity <= 200 Then CostWithoutDiscount = PricePerBook * Quantity * 0.93 ElseIf Quantity <= 300 Then CostWithoutDiscount = PricePerBook * Quantity * 0.9 Else CostWithoutDiscount = PricePerBook * Quantity * 0.85 End If If Discount = 0 Then Cost = CostWithoutDiscount Else Cost = CostWithoutDiscount * 0.95 End If End Function
So, the user-defined function Cost is created. Since VBA allows English-language names, the program text is clear and easy to understand. This also makes it simple to use the Function Wizard dialog box for this function.
The names of all parameters of the Cost function are displayed in the Function Wizard window, allowing any user to use it, even without knowledge of VBA.
For convenience, it is recommended to predefine the input values for the function parameters (PricePerBook, Quantity, and Discount) on the worksheet. However, this is not mandatory: the required values can also be entered directly in the Function Wizard window.