Partager via

User-Defined Functions in Excel

  • Article

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:

Function CtoF(Centigrade)
   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).
 
If you want to use a UDF in more than one workbook, you can save your functions in your own custom add-in. Simply save the Excel file that contains your VBA functions as an add-in file (.xla). Then load the add-in (click Tools then Add-Ins...). Note: Be careful about using custom functions in spreadsheets that you need to share with others. If they don't have your add-in, the functions will not work when they use the spreadsheet.

What UDFs can't do
A common mistake made by users is to attempt to create a worksheet function that changes the structure of the worksheet by, for example, copying a range of cells or attempting to return a value to any other cell than the one the UDF is used in. Such attempts will fail without any error message to indicate where the function failed. In addition to the above mentioned, a UDF can't change a characteristic of a cell such as font color or background pattern. In addition, a UDF can call another function procedure or subroutine but that procedure will be under the same constraints as the UDF. And finally, UDFs aren't as efficient as built-in Excel worksheet functions. If UDFs are used extensively in a worksheet, the recalculation time will be longer than if built-in functions are used.

For an excellent explanation of virtually everything you need to know to create your own Excel UDFs, see John Walkenbach's book, Excel 2002 Formulas. The book provides many good user defined function examples, so if you like to learn by example, it is a great resource.

Comments