1.7 User-Defined Functions

1.7 User -Defined Functions

The previous example has shown how a table can be made " intelligent " by the inclusion of some fairly complicated formulas. The use of formulas as in the previous example soon runs up against certain limitations ”the formulas become too long and unmanageable. You can avoid this problem by defining new functions yourself. However, your own functions can execute tasks that cannot be accomplished by formulas ”such as changing a number into text (such as the number 12.34 into the character string "one two point three four," which is sometimes necessary, for example, in the preparation of checks in a payroll program.

The definition of one's own functions presupposes a fairly deep knowledge of VBA programming. The recording of macros is unfortunately unsuitable for this purpose, since what is at issue here is a calculation and not a sequence of commands. The following examples are actually quite simply constructed and should, in fact, be comprehensible without knowledge of programming.

Our first function will compute the area of a circle. Before you can use this new function in a table, you must input the code into a module sheet. Therefore, open a new workbook, and execute InsertModule.

 ' Example file function.xls Function CircleArea(radius As Double) As Double   CircleArea = radius ^ 2 * Application.Pi End Function 

The keyword Function introduces a procedure, as did Sub in the earlier examples. The difference is that a Function procedure can return a value. For this reason the function name CircleArea in the second line is linked to the result of a calculation. The term radius is a parameter of the function. If you input the formula = CircleArea(5) in a worksheet, then Excel executes the function and automatically substitutes for the parameter radius the value 5. With Application.Pi you access the number 3.1415927.

Now we proceed to our second function, which is somewhat more useful: It calculates the product of unit price and number of units, and if at least ten units are ordered, it computes an automatic discount of five percent. To deal with this special case an If statement is included.

 Public Function Discount(unitprice As Double, pieces As Double) As Double   If pieces >= 10 Then     Discount = pieces * unitprice * 0.95   Else     Discount = pieces * unitprice   End If End Function 

Note  

Normally, user-defined functions are provided for more demanding calculations. Details for programming user-defined functions are discussed in Chapter 5, in the section on user-defined worksheet functions.