Excel UDF aka. User Defined Functions are somewhat of an underappreciated feature in Excel in my own opinion. With the recent announcement and release of the Excel LAMBDA function it almost seems like only now are we able to create our own Excel functions – leveraging on a combination of already existing Excel functions.

Let me start with explaining what Excel User Defined Functions (UDF) are.

Excel User Defined Functions are custom VBA Functions that can be made public and available as regular Excel built-in functions.

Why use Excel User Defined Functions (UDF)?

An UDF (User Defined Function) allows you to write a full-blown Macro function leverage on features that are otherwise not available via regular functions like Loops, usage of Objects likes Collections, Regular Expressions (Regex) etc. In some cases an UDF might even make more sense just because an equivalent Excel formula might be simply to complex to right (see nested IF inside nested IF….). What is more you can always Debug your UDF code instead of having to do guess work on what is failing in your Excel formula.

Pros and Cons of Excel UDF

The Pros

Excel User Defined functions have a lot of benefits worth mentioning.

Simplify complex Excel Functions

Excel formulas can be very messy and there is no code formatting in Excel. As VBA is basically a full-blown IDE for programming. You can leverage code indentation and formatting to make your code more clear manage. Additionally you can break your code down into multiple sub-functions to further reduce abstraction.

Debug / Find errors more easily

Excel formulas in cells can’t be debugged, they can only be evaluated. Meanwhile Excel Custom UDF functions allow you to easily debug your code line by line making it very easy to find mistakes and improve. Additionally you can breakdown your UDF into multiple sub-functions to reduce the level of abstractions.

Reuse your UDF is other spreadsheets

Excel formulas are usually tailored for the job and for the spreadsheet. All your hard work usually goes to waste once you move to another spreadsheet. Now UDFs can easily be copied to other workbooks. First of all you can export VBA modules to BAS files which can then be easily imported into your next workbooks. Regardless of this however User Defined Functions by definition are written in a way that is usually agnostic of the underlying spreadsheet.

The additional benefit of User Defined Functions in Excel VBA is also the opportunity to reuse your UDF in other VBA Macros, while being able to easily test it on actual data in your spreadsheet.

Cons

Now Excel User Defined Functions are not perfect probably that is why Microsoft created the LAMBDA function. So let now list some of the cons of using Excel UDF functions.

Performance

The key setback when using Excel UDF is the performance difference. If you are not aware Excel formulas are run in parallel during a refresh i.e. Excel swings up a few processes to calculate the cells. With UDF only a single thread is running.

This might not usually be a big deal as probably you will notice the performance impact only when running very large files.

An Excel User Defined Function runs via VBA – hence it will not leverage on Excels multithreading capabilities and requires saving the file in a macro enabled format like XLSM

File format

Using UDF means basically enabling Excel macros and that translates to having to save the file in a Macro enabled file format like XLSM or XLS/XLSB. When opening these files users might need to be warned they need to enable Macros – as Microsoft has included a security warning like below on all files including VBA Macros.

How to create a Excel UDF?

Now that I properly introduced what Excel User Defined Functions are it is time we show how to define them in Excel and how to use them. An Excel UDF function is not really that different from a regular VBA Function. The only difference it that it cannot be a Private function.

Any function in VBA is a potential “UDF” as long as it is not made Private

To create an UDF first open the VBA Developer ribbon, create a new Module and insert any new Function . See example below. The first example is a Custom UDF! The second function will not be. Why? As I preceded it with Private thus restricting access to it only within to the VBA Module.

'I am a UDF
Function SAY_HELLO1()
    SAY_HELLO = "Hello. I am an UDF"
End Function

'I am not an UDF
Private Function SAY_HELLO2()
    SAY_HELLO = "Hello. I am not a UDF"
End Function

Excel UDF Examples

The sky is the limit when using Excel User Defined Functions. You can leverage on the power of Excel VBA. Below are some examples – simple and more complex.

Example 1: Count words in a sentence

Below function will calculate the number of words separate by a space character.

Function COUNT_WORDS(r As Range)
    Dim s As Variant
    s = Split(r.Value, " ")
    COUNT_WORDS = UBound(s) - LBound(s) + 1
End Function

Notice I am using the VBA Split function which breaks down a string into an array of strings using a delimiter. Thanks to this the code is dead simple and short! In general User Defined Functions are great for complex text/string processing!

Example 2: Excel DateAdd Function

Ever struggled to do a simple date add operation in Excel. Well funny thing is that while VBA has a DateAdd function there isn’t such a function in Excel! However, thankfully we can simulate it using Excel User Defined Functions!

Function DATE_ADD(r As Range, c As String, add As Long) As Date
    DATE_ADD = DATEADD(c, add, r.Value)
End Function

Just one 3 lines of code! It is that simple! See example usage below.

Conclusions

I hope you found this post interesting – let me know you thoughts in the comments. Go also checkout my Excel LAMBDA tutorial to see how else you will be able to create Custom Excel Functions without VBA.