User Defined Functions (UDFs in short) are, as the name suggests, functions whose properties are custom defined by the user. They can be tailored to specific user needs and greatly improve productivity as they can help you do things built-in Excel features can’t. For example, if you find yourself needing to calculate the area of hexagon frequently, you can create an UDF to find it using only one function and entering the parameters you’ve defined before. In other words, UDFs allow you to create your own functions, with your own pre-defined parameters and options! Let’s take a closer look at how this works and how you can create your own custom formulas. Feel free to download our sample workbook below.

How to Create User Defined Functions

Module

First, we need to prepare the environment before creating User Defined Functions. Since a UDF is a code block in VBA, we need to open the VBA window and add a module. A module is a simple text editor that you can enter and save your code. Follow the steps below to open the VBA Editor and add a module into your workbook.

1. Press the Alt + F11 key combination when your workbook is open. Alternatively, you can click the Visual Basic icon under the DEVELOPER
2. In the VBA window, go to Insert > Module

Function

Now, we are ready to create our code. To do this, we’re going to need a procedure to create User Defined Functions. Procedures are defined as code blocks that can run multiple times. There are 2 types of procedures:

1. Subroutines (Sub)
2. Functions

The main difference between the two is how they return a value. While subroutines only run codes, functions can return a value as well. Function is the procedure type we’re going to be using to create a UDF.

A function should start with a Function statement and a name should follow, and then end with an End Function statement. All codes should go into these statements. Subroutines, on the other hand, use Sub and End Sub statements.

Another important point is defining the return value. To return a value from a function, assign the result to the name of the function. Here is a very basic function that returns the number 6.

Function MyFunction
    MyFunction = 6
End Function

If you copy this code block into your module, you can use this function, just like Excel's own built-in functions.

Argument

MyFunction can work without any arguments, similar to NOW, RAND, or PI functions. To use a function with an argument(s), they should be defined in the code as well.

Arguments are defined next to the function's name, between brackets. Argument names can be used as constants in the code. Our next sample function returns the area of a circle with a specified radius. The radius is the argument of the function and is referred as r. Multiplying the radius with itself, and Pi number gives us the area of the circle.

Function Circles(r)
    Circles = Application.WorksheetFunction.Pi * r ^ 2
End Function

Some of Excel's pre-defined functions can be used in a VBA code as well. Call them with the Application.WorksheetFunction collection.

Add this code into your module and try a UDF with an argument. You can use commas (,) to separate arguments.

Optional Argument

Arguments of functions can be made optional. A common example is the VLOOKUP function. You can use the function with 3 or 4 arguments. The last argument, range_lookup, is an optional argument that you can chose to omit, if you intent to use its default value. If you omit the last argument, the VLOOKUP function takes this as TRUE (or 1) and perform the calculations accordingly.

The same logic can be used with a UDF too. To make an argument optional, you should use Optional statement before argument name and define its default value.

Let's improve our Circles function and add an ability to calculate circumference as well. However, a function can only return a single value. As a result, the behavior will need to specified by user. What if the function had a second "optional" argument to determine what calculation is performed?

Function Circles(r As Double, Optional t As Byte = 1) As Double
    If t = 1 Then
        Circles = Application.WorksheetFunction.Pi * r ^ 2
    ElseIf t = 2 Then
        Circles = Application.WorksheetFunction.Pi * r * 2
    Else
        Circles = 0
    End If
End Function

Argument t represents the type of calculation and its default value is 1. The If…ElseIf…Else… statement determines the return value by checking value of t. If t is 1, then the function returns the area. If t is 2, then circumference is calculated. For other values of t, the function returns 0.

Comments

Macro codes can get complicated pretty easily and the code strings can become hard to read. Using comments will help you and others better understand what a piece of code does.

Use a quote character (') to start a comment. Once you start a comment, you cannot add a code into that line, so place them wisely. Here is a sample use of comments in our latest function.

Function Circles(r, Optional t = 1)
    'This function returns area or curriculum of a circle
    'r = radius
    't = 1 : area
    't = 2 : circumference
    If t = 1 Then
        Circles = Application.WorksheetFunction.Pi * r ^ 2
    ElseIf t = 2 Then
        Circles = Application.WorksheetFunction.Pi * r * 2
    Else
        Circles = 0
    End If
End Function

Save and distribute

You wrote your function and tested. The next step is saving them for future use or even send them to another users. First of all, a UDF is VBA code. So you need to treat them as you should treat macro included files. You have a few options:

Use in a specific workbook

You can save the file as an XLSM file which means an Excel file with macros. This makes UDF is specific to that file. You cannot use your custom function in other files.

Use with all workbooks

You can save your custom functions in an add-in file, XLAM. You can think add-in files as VBA containing files that running at the background. You can set an add-in file to open automatically with Excel. So, you can use your functions every time.

We suggest you to create a new blank workbook just for your functions or other codes. Save this code containing file as an XLAM file into AddIns directory.

Tip: Excel automatically locates the Addins directory when you select Excel Add-In (*.xlam) option in Save As dialogue.

To make an add-in run at start:

1. Go to FILE > Options > Add-Ins
2. Select Excel Add-ins from the Manage drop-down
3. Click Go
4. Check your add-in (if it isn't in the list, click Browse and find it)
5. Click OK

Use with all workbooks (Alternative way)

You can use custom functions in all your workbooks. Excel has a template file that opens with Excel every-time. This template is called as Personal.xlsb. Personal.xlsb is a personal macro workbook that you can save your macros in and use with every opened workbook. Because the UDFs are macros, you can save your functions into Personal.xlsb as well. However, you should use Personal.xlsb when calling your function. For example, if you save the Circles function into Personal.xlsb, you need to call the function as =Personal.xlsb!Circles(r,1) instead of =Circles(r,1).

The Personal.xlsb file can be found in C:\Users\[user name]\AppData\Local\Microsoft\Excel\XLStart folder. Do note replace [user name] with your own.