User-defined functions

User-defined functions in Excel, also known as UDFs, are powerful custom functions that provide solutions for advanced tasks.

What is a User-defined function?

User Defined Functions (UDFs) are small VBA codes that contain powerful custom functions. With its help, you can expand the built-in Excel function library and provide solutions for advanced tasks.

Excel has hundreds of great functions, but sometimes we face almost impossible challenges in the case of string manipulations or lookups.

UDF Example for Excel

In this section, I’ll show you how to write your own UDF. The demonstrated Excel user-defined function removes duplicates from a comma-separated string. Then, it splits the input string, checks each value against a list of unique values, and adds new ones. The output is a string of unique values joined and returned by the function.

Function RemoveDuplicates(v As Variant) As String
Dim aSplit As Variant, aUnique() As Variant, vMatch As Variant, a As Variant, X as Integer

aSplit = Split(v, ",")

ReDim Preserve aUnique(0 To X)
aUnique(0) = Application.WorksheetFunction.Rept("|^|", 20)

For Each a In aSplit
    vMatch = Application.Match(Trim(a), aUnique, 0)

    If IsError(vMatch) Then
        X = X + 1
        ReDim Preserve aUnique(0 To X)
        aUnique(X) = Trim(a)
    End If
Next a

RemoveDuplicates = Join(filter(aUnique, aUnique(0), False), ",")
End Function

Here is an example of how the UDF works:

remove duplicates udf excel

But first, let’s analyze the function:

First, we must declare the input parameter “v” as a variant data type.

Function RemoveDuplicates(v As Variant) As String 

After that, we declare four variables.

Dim aSplit As Variant, aUnique() As Variant, vMatch As Variant, a As Variant, X as Integer

Splits the input string into an array of individual values, separated by a comma, and stores the result in the “aSplit” variable.

aSplit = Split(v, ",") 

The ReDim Preserve statement resizes the array that contains unique values; “aUnique(0)” is assigned the result of the Application.WorksheetFunction.Rept function. The string is a separator used to separate the elements in the array.

ReDim Preserve aUnique(0 To X)
aUnique(0) = Application.WorksheetFunction.Rept("|^|", 20)

The next section fills the array, which contains strings separated by a comma. If the item does not appear in the unique array, the function trims the value and adds it to the array.

For Each a In aSplit vMatch = Application.Match(Trim(a), aUnique, 0) 
If IsError(vMatch) Then X = X + 1 
ReDim Preserve aUnique(0 To X) aUnique(X) = Trim(a) 
End If 
Next a

The “Join” function concatenates the elements using a comma separator. Finally, the “Filter” function filters the array and returns only the unique elements.

RemoveDuplicates = Join(filter(aUnique, aUnique(0), False), ",")
End Function

How to save and store a user-defined function in an Excel Workbook?

The best practice is to create a new module and save the Workbook in a .xlsm format. Using this method, you can share the function. Press Alt+F11 to open VBA Editor. Create a new module and copy the code into the module. Finally, save the Workbook.

insert a code

Alternatively, you can save the UDF to your Personal.xlsb file.

Intellisense-support (ScreenTips) for user-defined functions

One of the most advantages of the built-in Excel functions is Intellisense. If you have great programming skills, it is possible to create a connector between the .NET-based Excel-DNA framework and a simple VBA add-in. Take a look at the example below!

You can learn more about the Excel-DNA library here.

We built an add-in that provides Intellisense for UDFs. In addition, our favorite lookup function, XLOOKUP, is now available for all Excel versions with screen tips. So, you can use the ScreenTip feature for user-defined functions.

user-defined-function-in-excel-Intellisense

Here is an example:

The DXLOOKUP user-defined function provides help for the users to understand each argument and the main syntax while typing the function. Looks great!

Advantages of using UDFs in Excel

Here are some advantages of using user-defined functions:

  • Portable: Every user-defined function is reusable. Store the code in the Workbook.
  • Simple: You can perform complex calculations faster than the built-in functions.
  • Creating a library: You can collect and implement UDFs into modules.
  • Efficient: Functions can perform complex calculations faster than regular formulas.
  • Intellisense-support: UDF can display native screen tips (Intellisense) like built-in functions using the Excel-DNA framework.

Disadvantages of user-defined functions

  • Writing an optimized UDF (fast execution and error handling) requires deep knowledge of Excel programming.
  • In the case of add-ins, it is not recommended the change the original path

Final words

User-defined functions can be handy in some situations, but good to know that there are disadvantages.

Additional resources:

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.