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 in Excel?
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 string manipulations or lookups sometimes present almost impossible challenges.
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:
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.
Alternatively, you can save the UDF to your Personal.xlsb file.
Intellisense-support (ScreenTips) for UDFs
One of the most important 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.
Here is an example:
The DXLOOKUP user-defined function helps users 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.
Additional resources: