About User Defined Functions
Excel provides the user with a large collection of ready-made functions, more than enough to satisfy the average user. Many more can be added by installing the various add-ins that are available.
Most calculations can be achieved with what is provided, but it isn't long before you find yourself wishing that there was a function that did a particular job, and you can't find anything suitable in the list. You need a UDF.
A UDF (User Defined Function) is simply a function that you create yourself with VBA. UDFs are often called "Custom Functions". A UDF can remain in a code module attached to a workbook, in which case it will always be available when that workbook is open. Alternatively you can create your own add-in containing one or more functions that you can install into Excel just like a commercial add-in.
UDFs can be accessed by code modules too. Often UDFs are created by developers to work solely within the code of a VBA procedure and the user is never aware of their existence.
Like any function, the UDF can be as simple or as complex as you want. Let's start with an easy one...
A Function to Calculate the Area of a Rectangle
Yes, I know you could do this in your head! The concept is very simple so you can concentrate on the technique.
Suppose you need a function to calculate the area of a rectangle. You look through Excel's collection of functions, but there isn't one suitable. This is the calculation to be done:
AREA = LENGTH x WIDTH
Open a new workbook and then open the Visual Basic Editor (Tools > Macro > Visual Basic Editor or ALT+F11).
