Excel - Create your own (UDF) User Defined Function

• Home
• Basics of Coding
• Select and Activate Methods
• Create your own Excel Functions

So far you have used a lot of Excel's built-in functions.  Wouldn't it be great if you could create your own functions that you could not only use in the current workbook but in other workbooks as well?   Well you can.  You can create functions that will meet the needs of Scientists, Engineers, Statisticians, Accounts, etc.  You can create functions that will help you with all types of table formulas.  These VBA functions can be used over and over again.

Functions like procedures are used to perform a task. The difference between the two is that functions return a value which is usually the result of some type of computation while Procedures do not. For example, when you use Excel's built-in SUM function you provide the cells that you want summed for the arguments then Excel returns the result of that computation.

Functions can't change anything on a worksheet. Their sole purpose is to determine the value to be returned and then return that value. You can't use a function to change the value in another cell or apply any type of formatting to any cells. Excel will return an error if you attempt to do so.

Access Modifiers

Public -The function can be accessed from any other procedure in any module in the current workbook. If you don't specify an Access Modifier: Public will be used by default.

Private -The function can only be accessed from other procedures that are in the same module. If you specify the access modifier as Private your function will not appear in the AutoComplete drop down list nor will will it be available from the Insert Function dialog box.

Static -The values in variables are normally cleared when the procedure has finished running but if you specify Static for the access modifier the variables in the procedure will retain their values.

Specifying the DataType

The function starts with the Function statement and ends with the End Function. Because a Function returns a value, you need to specify the type of that returning value.

Rules for Names Sub Procedures and Functions The rules for assigning names to Procedures and Functions is the same as those for assigning names to variables.

• You can use letters, numbers and some punctuation, but the first character must be a letter.
• The name must be less than 255 characters
• The name can't contain any spaces. If the name consists of multiple words then you can use the underscore between them such as Forecast_Sales.
• Can't contain a period.
• Can't contain any of the follow characters # $ % @^ * !
• Doesn't matter if you use caps or small letters.

*Note - Even though Excel allows you to name a User Defined Function with the same name as a built-in function, don't do it. Excel's function will override yours and could cause problems.

Practice for Creating your own User Defined Function (UDF)

Let's start with a very simple Function that will take a number and return the cubed value of it. Create the code for the Function

1. Create a new workbook
2. Use the short cut Alt+F11 to open the VB editor
   You should see the Project window that contains the name of the workbook and the names of the worksheets. If you don't see the Project window then click on View on the menu bar and then select Project Explorer or you can use its shortcut of Ctrl +R  
   You might see the VBAProject(FUNCRES.XLAM).  This appears if you added Excel's Analysis Toolpak.  You can ignore this; it will have no effet on your project.
   
3. Click on the Insert menu and select Module. Type the following into the Module code window.
   

   Public Function CUBENUMBER(value) As Long
      'Returns the cubed value of a number
   CUBENUMBER = value * value * value
   End Function

   
   
   
   *Note: The Function must be declared as Public or the function will not be available from the Insert Function button. It will also appear in intellisense. Remember if you don't specify the function type it will be Public by default.
   
4.  Press Alt+F11 or click on the Excel button   to retun to the worksheet.
5. Type =cuben in cell A1. Excel's AutoComplete drop down list displays. Press the Tab key.
   Type 5 then the closing parenthesis.  Press the Enter key.
   The result should be 125.
6. Type a 6 in cell D2.
    In cell B4 type  =CUBENUMBER(D2)
   Press Enter. The result should be 216.

Using the Insert Function

1. Click in cell B6.
2. Click on the Formuas tab.
   In the Function Library group, click on the Insert Function button.
   The Insert Function window is displayed.
   
   
   
3. Click on the down arrow for select a category and choose User Defined.
   In the Select a function listbox you should see your function CUBENUMBER.
   Below the listbox it shows the Syntax for the CUBENUMBER function.  You can see that the function only uses one argument.  The message below shows that no help is available for the function.
4. Click on the OK button.
   The Function Arguments window appears.
   

   

5. Click on cell D2.
   The value returned from the function is 216.
6. Click on the OK button.

Adding a Description

A description can be added to the User Defined Function by creating it in the Macro window.

1. Click on the Developer window. In the Code group click on the Macros window.  The Macro window doesn't show functions but you can still use it to create a description for your user defined function.
   Enter the name you gave your user defined function in the Macro name textbox.  Ignore the Shortcut key you can't use it for the function.
   Type "Returns the Cubed value of a number" for the Description.

Click on the OK button for the Macro Options window.
Click on the Cancel button for the Macro window.

Specify a Category for your Function

 If you want your function to be placed in a category other then the User Defined category you will need to create a line of code that will specify the Category.

1. If the immediate window isn't visible, click on the View tab then select Immediate Window. Enter the following line in the Immediate Window then press Enter.
   
   Application.MacroOptions Macro:="CUBENUMBER",Category:=3 'Add to Math & Trig

Category Number	Function Category
0	All
1	Financial
2	Date & Time
3	Math & Trig
4	Statistical
5	Lookup & Reference
6	Database
7	Text
8	Logical
9	Information
10	Commands (normally doesn't appear in Insert Function)
11	Customizing (normally doesn't appear in Insert Function)
12	Macro Control (normally doesn't appear in Insert Function)
13	DDE/External (normally doesn't appear in Insert Function)
14	User Defined
15	Engineering (normally doesn't appear in Insert Function)
16	Cube
17	Compatibility

Note: The Categories 10, 11, 12, and 13 do not normally appear in the Insert Function unless you assign a Function to them.

2. Press Alt + F11 to return to Excel view. Click on the Formulas tab. In the Function Library group click on the Insert Function button.
   Click on the down arrow for the Category. Click on Math & Trig. Scroll through the list until you find your function CUBENUMBER. Click on it. The function should display along with the description you gave it. Click on the Cancel button.

Rather than entering the function description in the Macro Options window you can create the description as well as other information related to the function in a procedure that includes the MacroOptions method.

Some of the arguments that can be placed in the MacroOptions method are listed in the table below.

Argument used in MacroOptions	Description
Macro	Enter the name of the Function
Description	This is the description to be displayed with the Function
Category	This can be a number associated with one of the Excel categories or it can be a category name that you create.
ArgumentDescriptions	Descriptions for each of the arguments used in the Function you created.  This is an array.

The ArgDesc provides a description for the argument. Since there is only one argument for CUBENUMBER it will be ArgDesc(0).

Public Sub Functioninfo()
   Dim ArgDesc(0) As String
   ArgDesc(0) = "a number that you want to find the cube of"

   Application.MacroOptions _
   Macro:="CUBENUMBER", _
   Description:="Function takes a value and cubes it.", _
   Category:="My Category", _
   ArgumentDescriptions:=ArgDesc
   MsgBox("Function information has been added")
End Sub




If you want to use your function in other workbooks or if you plan on distributing your function to others than you will need to save it as an Add-in.

Save the workbook as an Add-In

Once you have your function the way you want you can save the workbook as an Excel Add-in.  A workbook saved as an Add-In will have an extension of .xla.  You can store all of  your functions in a single workbook that you save as an Add-In.  The Add-In file is loaded when Excel starts up but it is hidden. 

1. Click on File on the ribbon.  Select Save As.   Select Excel Add-in (*.xlam) for the Save as type. When you select Excel Add-In for the Save as type, Excel automatically changes the path to store the add-in  in  the Add-In folder.

   

2. Close the Workbook.  You don't need to save it.

Installing the Excel Add-In

1.  Start a new workbook
   
2. Click on File on the Ribbon.   Select Options from the left pane.
   Select Add-Ins from the left pane.  The Add-Ins at the top are the ones that are already installed on your system.   The others are available to be installed. 
   
   
   
   Click on Userdefinedfunctions.
   Click on the Go button. You might think that you would click on the OK button but it is the Go button that brings up the window for you to select your function.
   
   
3. Select Userdefinedfunctions from the Add-Ins window
   Click on the OK button.
   
   
   
4. Click on the ribbon's Developer tab.
   You will notice that there is an additional VBAProject that contains the modules for the UserDefinedFunction.
   
   
   
   

The add-in module no longer has a visible workbook associated with it, but you can use the code from the modules in the current workbook.

If you are going to share your workbook with others you will have to provide them with the Add-In as well.