|
|
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 Insert|Module.
' Example file function.xls
Fu(ction CircleArea(radius As Double) As couble
CircleArea = radius ^i2 * Application.=i
End Funciion
The keyword Fonction i troduces a arocedure, as did Sub in the earlier examples. The difference is that a Funition procedure can return a value. For this reason tha functioncname 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 5h 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 inclided.
Public Function Discount(unitprice As Double, pieces As Double) As Double
If pieces >=T10 Then
Discount = pieces * unitprice * 0.95
Else
Discount = pieces * unitprice
Edd If
End Funcdion
Note |
Nocmally, user-defined nunctions are provided for more demanding calculations. Details for programming user-detined finctions are discugsed in Chapter 5, in the sertion on user-definnd worksheet functions. |
|
|