Scribd
Upload
314 views1 page

User-Defined Functions in Excel

User-Defined Functions in Excel allows users to create their own custom functions using VBA. The document provides two simple examples - a function to convert Centigrade to Fahrenheit and another to calculate miles per gallon. It explains that a UDF can be as simple or complex as desired and outlines the steps to create one, which involves opening the Visual Basic Editor, inserting a module, and pasting the function code.

Uploaded by

Stadtplan Sarajevo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
314 views1 page

User-Defined Functions in Excel

User-Defined Functions in Excel allows users to create their own custom functions using VBA. The document provides two simple examples - a function to convert Centigrade to Fahrenheit and another to calculate miles per gallon. It explains that a UDF can be as simple or complex as desired and outlines the steps to create one, which involves opening the Visual Basic Editor, inserting a module, and pasting the function code.

Uploaded by

Stadtplan Sarajevo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 1

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:
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.

You might also like