Incredibly Useful Examples Of Excel User Defined Functions in VBA

In this post, we're going to see how we can build our own custom functions in Excel with VBA, and we will look at some incredibly useful Excel User Defined Function examples that you can try in your everyday work.

VBA has a special type of procedure which allows you to build your own functions for use in the workbook. These allow you to create custom calculations that can be used repeatedly throughout your workbooks.

Let's take a look!

What is a function?

In abstract terms, a function is something that will take an input and return an output. The function is the rule or calculation that's applied to the input to get the output result.

Let's take a look at a very simple example in math called the successor function. It takes any natural number {1,2,3,4,...} and returns the successor or next number in the sequence. This is usually represented by f(x)=x+1 in mathematical notation.

• For this function the input is x and the output is x+1.
• So if the input is 1 the output of the successor function will be 2.
• If the input is 9, the output will be 10.
• If the input is 100, the output will be 101. You get the point.

It's a simple rule that can be applied to any number. You probably see examples of functions in real life all the time but just don't think of them as functions. A function doesn't necessarily have to have numerical inputs and outputs. They can be any type of data.

Take Google maps as an example. You can input an address and it will return a location on the map. In the abstract sense, this is exactly what a function does!

Functions in Excel

Excel has a ton of functions. Last time I counted, there were 479 functions available in Excel. That's a lot of functions! If you've used Excel for any amount of time, you're very likely to come across some of these. They allow a user to perform many types of operations such as time and date, financial, logical, lookup, statistical, or text calculations.

• Looking up related values in another table using the VLOOKUP function.
• Changing lowercase text to upper case text using the UPPER function.
• Calculating the internal rate of return from a series of cash flows using the IRR function.
• Performing a logical test using the IF function.
• Calculating the average of a set of numbers using the AVERAGE function.

Some of these functions require many inputs to return the output. In Excel and some other programming languages, the inputs are also called arguments. For Example, VLOOKUP(lookup_value, table, index_num, [lookup_type]) has 3 required arguments and 1 optional argument. These inputs determine the value that VLOOKUP will return as its result.

Excel UDF: What is a User Defined Function?

With all these functions available in Excel, there still may be a time when none of them can do the calculation you want. Excel allows us to create our own custom functions using VBA. These custom functions in Excel are known as User Defined Functions (UDF for short). They allow you to code your own functions to do just about any type of operation.

Opening the Visual Basic Editor

To create an Excel UDF, you're going to need to open up the Visual Basic Editor (VBE for short). There are a couple ways to open the VBE.

Opening the Visual Basic Editor from the Ribbon

To open the VBE from the ribbon, you first need to enable the Developer tab. It's hidden by default.

1. Right-click anywhere in the ribbon.
2. Select Customize Ribbon from the menu.
3. In the resulting Excel Options window, check the box next to Developer listed in the Main Tabs area.
4. Press the OK button.

You will now have a new tab featured in the Excel Ribbon. Click on the Developer tab then click on the Visual Basic icon to open the VBE.

Opening the Visual Basic Editor from the Quick Access Toolbar

To open the VBE from the Quick Access Toolbar (QAT), you first need to add it to the QAT.

1. Right-click anywhere in the Excel ribbon.
2. Select Customize Quick Access Toolbar from the menu.
3. In the Excel Options window, select the Developer Tab from the drop-down menu.
4. Select the Visual Basic Editor command.
5. Press the Add button to add the command to the QAT. You should now see it listed in the right-hand pane.
6. Press the OK button.

The command will now appear in the QAT and you can click on the small icon to open the VBE.

Opening the Visual Basic Editor with a keyboard shortcut

The simplest way to open the VBE is with a keyboard shortcut. It's quick and always available without messing around with customizing either the ribbon or the quick access toolbar. Press Alt + F11 on your keyboard to open up the VBE. Press Alt + F11 again when the VBE is open to switch back to Excel.

Inserting a Module to store your User Defined Functions

After opening the VBE, you're going to need to add in a module. This is where you're going to create any UDF's in the workbook. Right-click within the VBA Project Explorer and select Insert then choose Module from the menu.

Creating your first User Defined Functions

Now you can create your first user defined function. Let's try creating our simple successor function example. Place the following code into the newly created module.

Function Successor(x As Integer)

    Successor = x + 1

End Function

Now, you can use this like any other Excel function in the workbook. Start with an equal sign like when creating a regular Excel function. When you start to type out the name of the UDF, it will appear in the function intellisense list like all the other functions.

You can then reference any cell in the workbook as the input for the function. The cell which contains the function then returns the successor value.

Syntax of a User Defined Function

What is the syntax needed to create a UDF? There are 5 main parts:

1. You need to declare the VBA code as a function. This means it has to start with the Function declaration and end with the End Function declaration.
2. The function needs a name. You can use any name you want, but it can't contain certain special characters like a space and you can't use reserved names.
3. The function will need some inputs. They are defined within parenthesis after the function name. This can be left empty as it's possible to create a UDF with no inputs.
4. You can declare the output data type of the function. This part is optional and if it's omitted the function output will default to the Variant type.
5. You will need to assign the function some sort of value to return.

Now that we know how to create a user defined function, let's take a look at a few simple Excel user defined function examples that are actually useful.

Example #1: Get the sheet name from a referenced cell

Function SheetName(CellReference As Range)

    SheetName = CellReference.Parent.Name

End Function

While it's possible to use a combination of functions to get the name of a worksheet, it's definitely not an easy formula. This UDF is simple and easy to understand.

Example #2: Extract a comment from a cell

Function ExtractComment(CellReference As Range) As String

    ExtractComment = CellReference.Comment.Text

End Function

Getting the text out of a comment is a common issue, but there's no Excel function to do this. The only way is to edit the comment then copy and paste the contents into Excel. You can use this UDF to extract and return the text of the comment for you.

Example #3: Does a file exist

Function DoesFileExist(FilePath As String) As Boolean

    DoesFileExist = Not (Dir(FilePath) = vbNullString)

End Function

With this user defined function you can tell if a file exists. This function will take a file path as a text string (complete file name) and return TRUE if the file exists at the specified location and FALSE if it doesn't.

To see all of these examples in action, grab a sample workbook here: file download

Register a User Defined Function with Insert Function

Registering a UDF will allow you to add a description to appear in the Insert Function dialog box and categorize it with the other native Excel functions. This can be a handy way to document what your function does so that other users will know.

Sub RegisterMyFunction()

    Dim fnDesc As String
    fnDesc = "This function will return TRUE is the file exists and FALSE otherwise."
    
    'The various possible function categories
    '1  Financial
    '2  Date & Time
    '3  Maths & Trigonometry
    '4  Statistical
    '5  Lookup & Reference
    '6  Database
    '7  Text
    '8  Logical
    '9  Information
    '15 Engineering
    '16 Cube
    
    Application.MacroOptions macro:="DoesFileExist", Description:=fnDesc, Category:=9
    
End Sub

Place the above code in any module of the workbook and run it (Developer Tab > Macros > select the RegisterMyFunction macro > Run).

When you add a function to the workbook using the Insert Function command next to the formula bar, your UDF will appear in the resulting dialog box.

The Insert Function dialog box will show your UDF in the category selected. In our case we added the function to the Information category. The function syntax and description is also shown when the function is selected.

Creating a Volatile Function

Notice with the SheetName function from example #1, when you change the referred sheet's name, the output of the function doesn't update to the new name? This is because UDF's only recalculate when a dependent cell is changed by default. If we want this function or any other UDF to update when anything is changed, we need to make it a volatile function by adding the Application.Volatile command to the UDF.

Saving the UDF in the Personal Macro Workbook

The UDF's you create are only available for use in the workbook they were created in. If you try and use them in any other Excel workbook, they will return a #NAME? error since they are unknown to that workbook.

What if you want to use them in another workbook? You can do this by saving them in the personal macro workbook instead. Any VBA saved here will be available in any workbook you open up on your computer. Be aware though, anyone else that opens a workbook won't have access to your personal macro workbook. Any UDF's in the workbook that reference your personal macro workbook will return #NAME? errors for other users! This is one drawback of using the personal macro workbook over saving UDF in the workbook.

Conclusions

We learned what UDF are, how to create them, the syntax needed and looked at a couple useful examples. We also learned how to enhance the experience when using UDF's by adding them to the Insert Function dialog box in a given category or turning them into volatile functions when needed.

User defined functions can be a great way to customize and extend the ability of Excel beyond Excel's many native functions. If you’d like to learn more about creating powerful functions to automate your tasks, check out our Macros and VBA course.

Want to show off your advanced-level Excel skills? Get certified with a GoSkills Advanced Excel Certification.

Ready to start learning? Become an Excel ninja today with GoSkills

Ready to become a certified Excel ninja?

Start learning for free with GoSkills courses

Start free trial