Write Your Own User-Defined Function in Excel

Excel offers hundreds of functions out-of-the-box * SUM(), VLOOKUP(), NPV() – to name a few * What if you need a function to do something unique, not included in the book? The answer: write it yourself! Here is how.

Functions in Excel

One of the first things you learned in Excel was calling built-in functions.

The basic concept of a function can be summarized in three points:

• It is called and optionally provided with arguments by the caller.
• It implements some logic – its purpose.
• It returns a single data object as a result.

Lets take the LEFT() function to better understand the anatomy of a function.

LEFT(“Mor Sagmon”, 3)

The above function is expecting two arguments (or parameters): a string type and a numeric type.

Its purpose is to cut away some characters of the passed string and return only the 3 left-most characters to the caller as a String-type, in this case: “Mor”.

By the way, its second argument is optional, and it is assumed to be 1 as a default, if not provided by the caller.

The NOW() function expects no arguments at all, as it has all it needs to perform its job: return the current date and time.

The SUM() function can take one or more arguments.

When passing arguments to a function, it is important to match the passed value to the type of the argument as defined by the function.

The LEFT() function is defined something like that:

Function LEFT(<String>, <Integer>)

When specifying a cell address, or a range as a value to be passed to a function, Excel knows to pass over the content (value) of that cell or range. For example:

LEFT(A1, A2)

Writing your own User-Defined-Function

Equipped with basic understanding of functions, you are now ready to write your own, custom function, or UDF as it is called in Excel: User-Defined Function.

The function is implemented in VBA, so you need to know VBA enough to be able to write the code required to implement your function. I teach VBA from zero to the highest possible level in my online courses.

Open the VBA editor from Excel with ALT+F11.

On the left you’ll find the VBA Project explorer, showing the Workbook and Worksheets objects of your Excel Workbook. Let’s add a VBA module:

Right-click on any item in the project explorer and select: Insert -> Module.

Module1 is the default name given to your first module, but you can change that in the Properties window below the Project Explorer (press F4 if for some reason it is not visible).

The main area of the editor, currently an empty white space, is where the VBA code goes. If it already contains a statement or two, such as Option Explicit – that’s OK, just ignore it for now and start writing your function in a new line below.

Your function starts with the function definition and ends with the End Function statement.

Let’s write a function that returns your name:

Function MyName() As String

    MyName = "Mor Sagmon"

End Function

The function has a name: MyName, parenthesis for any arguments (if required) and a definition of the data type it returns, String in our case.

The parenthesis is mandatory even if no arguments are needed, as in this example.

In between the definition statement and the closing statement, you write whatever VBA code you need. The function will return whatever you assign to the function name (acting as a variable).

The equal sign in this context acts as an assignment operator: it assigns the result of whatever expression it finds to its right, into the variable mentioned to its left.

In my example, I’m assigning a string containing my name to the variable name representing the function – that is the string that will be returned from the function.

When saving a Workbook with VBA code, change its File Type when saving to be: Excel Macro-Enabled Workbook (.xlsm). You may need to click the “more options…” link below the file name box to work with the rich “Save As” dialog box to be able to change the “Save as Type” value.

Using your custom function in Excel

In any cell of any Worksheet from which you opened the VBA editor, call MyName function as you would call any other built-in function:

=MyName()

Voila!!

As with any function, you can use your UDF’s as many times as you want, as part of any more complex expression, and across Workbooks – for the most part, it behaves just like any other built-in function.

If MyName() is defined in Book1.xlsm, you can reference it from another workbook like that:

=’Book1.xlsm’!MyName()

Handling arguments in our UDF

Next, let’s change our UDF to return the first and last name passed to it as two String type arguments. Our function should now look like this:

Function MyName(FirstName As String, LastName As String) As String

    MyName = FirstName & " " & LastName

End Function

And we must now pass two String-types when calling it:

=MyName("Mor","Sagmon")

We now calculate the returned value to be a concatenation of three strings: the string stored in FirstName, a single space and the string stored in Last Name. We use the “&” operator to concatenate strings in VBA.

What happens when we point to cells in our Worksheet for the arguments we pass over to our UDF?

=MyName(A1, B1)

Excel guesses you want to really pass over the values in those cells, therefore it makes the necessary adjustments for you automatically, behind the scenes.

By the way, Excel will go out of its way to save you from dealing with errors, and even if those target cells contain non-string values, such as numbers, Excel will automatically convert them to strings.

How convenient!

Optional Arguments in our UDF

Remember the LEFT() function that has an optional second argument? It is not required from the caller, and has a default value for such cases.

Let’s render our function to require only one argument and optionally handle the second one. Here’s how it is declared:

Function MyName(FirstName As String, Optional LastName As String) As String

    MyName = FirstName & " " & LastName

End Function

The second argument is declared Optional, thus calling the function with only one argument works fine:

=MyName(A1)

The default value is assumed to be an empty string, or 0 for numeric arguments. Let’s explicitly specify a default value for the last name, to be used by the function in case it is not provided by the caller:

Function MyName(FirstName As String, Optional LastName As String = "Top Excel") As String

    MyName = FirstName & " " & LastName

End Function

Your function can have multiple optional arguments; however, they must be defined at the end of the arguments definition. This will not work:

Function MyName(Optional FirstName As String, LastName As String)

Handling unknown number of arguments in our UDF

As with the SUM() function, our UDF can also be defined to handle a different number of arguments. This is achieved by ending the arguments definition list with the ParamArray definition:

Function MyName(ParamArray args()) As String

    For Each arg In args

        MyName = MyName & arg & " "

    Next arg

    MyName = Trim(MyName)

End Function

Here, args() is an array variable (name it anything you want), taking all of the passed arguments as its members and automatically dimensioned to fit the number of arguments passed.

With ParamArrays we usually loop over the array holding the arguments, as exemplified here.

I added a Trim function call applied to our resulting MyName after having concatenating all arguments, to get rid of the ending extra space left by the last iteration of the loop.

If you want to understand more about Arrays, I have just the Blog post for you.

If you want to understand more about ParamArray(), I have just the Blog post for you.

Try calling your new function, passing in different number of arguments each time…

You can combine ParamArray() with regular arguments in defining your UDF, but as with Optional arguments, the ParamArray() must be the last in the list. This is OK:

Function MyName(id as Integer, ParamArray args()) As String

This is NOT OK:

Function MyName(ParamArray args(), id as Integer) As String

I omitted some good programming habits in this post, not even dimensioning variables. I know, this is not the usual “me”, but I wanted to make VBA newcomers feel comfortable too.

Now you are asked to share this Blog post with any Excel user you know – they will thank you!