User-Defined Functions in Excel

Jun 11, 2022

You might have heard from some of your expert Excel friends that you can create a custom function within Excel using VBA.

But how do you create custom functions in Excel?

VBA has a specific procedure you can follow to build your user-defined function in your spreadsheets.

This article will show you how to do that and what you need to consider.

What is an Excel Normal and Custom Function?

In simple terms, an Excel function requires input and returns an output. The function is the calculation or rule applied to the input to receive the required output.

Functions in Excel

Microsoft Excel has a range of functions built-in, well over 400. That's quite a few functions! Some are more common than others and allow you to execute your operations, such as financial, statistical, or text calculations.

Some functions need more than one input to return the output.

Excel UDF: What is a User-Defined Function?

Even with the various functions available within Microsoft Excel, there are times when these pre-defined functions don't execute the calculations you need.

Microsoft Excel allows you to build your custom function via VBA code. These custom functions within Microsoft Excel are called User Defined Functions (UDF). A UDF allows you to code custom functions to complete almost any operation.

Opening the Visual Basic Editor

It would be best to open the Visual Basic Editor (VBE) to build your Excel user-defined function. There are a few ways to do this.

Open the Visual Basic Editor via the Ribbon

To open the VBA from your Excel ribbon, you must enable the Developer tab, which is hidden by default.

Right-click on your Ribbon,
Choose to Customize Ribbon from the pop-up menu
From the Excel Options window, tick the box alongside Developer within the Main Tabs area.
Hit the OK button.

The Developer tab will now appear within your Ribbon. Click on it, then select the Visual Basic icon to open your Editor.

Opening the Visual Basic Editor from the Quick Access Toolbar

To open your Visual Basic Editor via the Quick Access Toolbar, add it to the toolbar. Here's how to add your Visual Basic Editor to the Quick Access Toolbar:

Right-click on your Excel ribbon
Choose to Customize Quick Access Toolbar
From the Excel Options window, choose the Developer Tab
Choose the Visual Basic Editor option
Select the Add button to add your VBE to the QAT.
Hit OK

Opening the Visual Basic Editor with a Keyboard Shortcut

The easiest way to open your VBE is via a keyboard shortcut. It's fast and easy to do. Hit Alt + F11 on your keyboard to open VBA. Press those keys again to switch back to Microsoft Excel.

Adding a Module to Save Your User-Defined Functions

After you have opened the VBE, you need to add a module from the module window. You create custom functions or UDFs within your workbook in this area. Right-click in your VBA Project Explorer, choose Insert, then select Module from the menu.

Syntax of a User-Defined Function

One crucial question we must answer is what syntax you need to create a custom function. In total, there are five main parts to consider:

Firstly, you must declare the VBA code as a function. , your code must start with the function declaration and finish with the End function declaration.
Next, you need to add a function name. You can name your custom function, but it must not contain special characters like a space. This is a common mistake for those making their first user-defined function.
Next, your custom function must have some inputs. Your inputs are defined within the parenthesis that follows your function name. Sometimes, you could leave this section empty, as no custom Excel function requires input.
For custom user-defined functions, you also have the power to declare the output data type of your function. This step is optional; your function will default to the Variant type if left blank.
Finally, you must assign your function a value to return.
You will need to assign the function some value to return.

Register Your User-Defined Function with Insert Function

Registering your UDF will let you include a description in the Insert Function box and archive it with other Excel functions. This way is great to document how your function works for others to use it.

When you add your function to your workbook with the Insert Function command within your formula bar, your UDF will show in the dialog box.

Summary and Key Takeaways

Now you know what a user-defined function is, how to create your custom function, and the syntax required to create custom functions in Excel.

We also covered how to enhance your experience using a user-defined function by adding it to the Insert Function dialog box.

Custom functions are fantastic for customizing and extending the capabilities within Excel, far past the native functions on offer.

Frequently Asked Questions About User-Defined Functions:

What are user-defined functions in Excel?

A user-defined function is a function that can get used within a worksheet in a similar way to how you use a normal Excel function like AVERAGE, SUM, and IF.

What are the five main functions used in Excel?

IF - The IF function is easy to understand, and it is the basic fundamental for many function codes, whether you create custom functions or use pre-made Excel user-defined functions.
INDEX - INDEX is a handy function for returning any value of a specific position.
INDEX and MATCH - Combining the INDEX and MATCH formulas will completely revamp how you build financial models
SUM - Almost everyone knows the SUM formula, which lets people sum multiple values.
SUMPRODUCT - SUMPRODUCT is an excellent example of a function with plenty of versatility.

How do I save a user-defined function in Excel?

If you plan on keeping a custom function in one workbook, you must save your file as an XLSM. You can see this option under the Save As section.