# User-Defined Functions in Excel

Excel allows you to create custom functions, called "User Defined Functions" (UDF's) that can be used the same way you would use SUM(), VLOOKUP, or other built-in Excel functions. The Excel user who wishes to use advanced mathematics or perform text manipulation is often seriously disappointed by Excel's limited library of formulas and functions. A UDF is simply a function that you create yourself with VBA. This blog will help you get started with UDFs and show a couple of examples.

Sample UDF
The following is a sample that is a good candidate for a UDF:

CtoF = Centigrade * 9 / 5 + 32
End Function

This function converts degrees Centigrade to degrees Fahrenheit. In a worksheet, you might have a column of degrees Centigrade and a second column that uses the CtoF function to calculate the corresponding temperature in degrees Fahrenheit. The function would appear in the Formula bar.

The function has one input value, called Centigrade, which is used to calculate the return value. The value to be returned is indicated where the function sets its own name to the return value.

Another simple sample
Function MPG(StartMiles As Integer, FinishMiles As Integer, Gallons As Single)
MPG = (FinishMiles - StartMiles) / Gallons
End Function

This function calculates the miles per gallon of a car. The calculation in this function is the number of miles the car has travelled since the last fill-up divided by the number of gallons of  fuel used.

Note: Don't be fooled by these simple examples. Like any function, a UDF can be as simple or as complex as you want.

Creating a UDF
The following steps can be used to create UDFs:
1. Open up a new workbook.
2. Open the Visual Basic Editor by pressing Alt+F11.
3. Click Insert then Module to insert a new module.
4. Copy and Paste the code that makes up the UDF (such as the CtoF or MPG function examples).
5. Press Alt+Q to exit the Visual Basic Editor.
6. Use the function (Appears in the Paste Function dialog box (press Shift+F3), under the "User Defined" category).