How To Create User-Defined Functions In VBA (With Examples)

If you’re an experienced Excel user, there’s a pretty good chance that you’ve already leveraged Excel’s built-in programming language, VBA.

Even if just simply recording macros to automate mundane tasks; such as writing values to cells, formatting data, saving files, and the like. 

However, there's a completely different and equally useful application of VBA code called user-defined functions or ‘UDF’s’, that most people miss.

What is a UDF?

Simply put, UDFs are functions that work just like standard built-in Excel functions, like SUM or VLOOKUP. The biggest difference though is that they can also be customized to your exact requirements.

Now obviously, if there's a built-in Excel function that can be used to accomplish a certain task - and many times there will be since Excel has so many built-in functions - you should go ahead and use that. Not only will it be simple to use, but built-in functions perform much faster than user-defined functions created with VBA. 

But as awesome as Excel is, sometimes there just isn't an Excel function that does exactly what you need, and in those cases, the ability to “roll your own” custom function with VBA is worth its weight in virtual gold.

So what's the difference between VBA subroutines and functions?

You might be thinking;

“Why would I want to create a function, when the VBA macro recorder could make a subroutine instead?”

Well there's a few reasons, but the main point comes down to how they actually work. You see, the fundamental difference between VBA subroutines (the batches of code generated by the macro recorder) and functions, is that while subroutines do something, functions return something: aka a single value.

For example

Consider how standard Excel functions work. If you enter a function in a cell, that function will return a single value in that cell. And sometimes, that’s exactly what you want to happen - especially if you want your function to reference that value, which is what we'll be looking into in these 2 examples.

So with that said, let's dive right into building our own simple user-defined function.

Example #1: Creating your first User-Designed Function (UDF)

I’m going to walk you through 2 examples, starting with a very basic UDF first so you can see how it all works, and then we’ll get into a more complex one in a second.

For this first example, we’re going to create a simple UDF that works like this. When someone writes their name into a cell, it returns with a Hello and uses their name as reference.

Nothing too exciting but this will help you grasp these concepts before we move on to more complex situations.

So, to demonstrate how this works, let’s head over to the VBA editor by using the keyboard shortcut Alt + F11, and then insert a new module by right-clicking in the Project Window and choosing Insert >> Module:

Then we can rename the module in the Properties Window to something more descriptive, such as “UtilityFunctions”:

And just like that, we're ready to write our first user-defined function!

So the basic syntax goes like this: 

We start off by typing the word "Function”, followed by the name of your function, which in this case will be “Hello”. 

We’ll also need a pair of parentheses () after the function name, regardless of whether the function will take any arguments or not. 

The other mandatory piece of syntax is that our function code should end with the keywords “End Function”.

Here's a cool thing though - we don't actually even have to type that out, because if we just enter down from where we declared our function, VBA types it out for us. 

 

Thanks, VBA!

So unlike macros and subroutines, user-defined functions in VBA typically take pieces of data or 'arguments' that the user passes to the function, and we specify those in between those two parentheses after the function name.

It sounds complex but this is very similar to standard Excel functions, such as passing a SUM function to make a series of numbers add together.

=SUM( X + Y + etc.)

X is one variable and Y is the other (usally we would select a range of cells, but you get the idea).

We’ll also need to specify a data type for each argument we pass in between these parentheses, after which those arguments will be available as variables in the body of the function.

In this case, the “body” is basically everything between the line where you declare the function, and “End Function”. These variables can be then used as part of the calculations that ultimately return a value from the function.

I know that's a lot to get your head around but you can think of them as a way to capture user input and incorporate it into the logic of the function. We want people to be able to enter their name, so our UDF can then use it as the variable during its reply.

So now that we understand the basics, let's go ahead and fill out our function. First up, our function will take an argument called “name”, with a “String” data type.

Note that the syntax for specifying an argument is the argument name, followed by the keyword “As”, then the data type of the argument.

And now for the, well, 'function-ality' of our function.

In simple terms, they enter their name, and it says hello back to them. But in technical terms, our objective is to concatenate whatever name the user passes in as an argument to the function, to the word “hello” (followed by a space).

This means that our function will essentially return a personalized greeting in whatever cell of the spreadsheet that the user enters the function.

So to return a value from a function, in the last line of code in the function body, we'll need to set the name of the function itself (“Hello” in this case) to whatever value we want to return.

This is almost like the name of the function itself is a variable, and we're setting its value. Sounds weird I know, but once you've written a few user-defined functions you get used to it.

Now our very simple function will only have one line of code in the entire function body, so we don’t need to worry about making it the “last” line, as it is set as this by default!

As such, we can go ahead and set up that return value by typing the name of our function, followed by an equals sign…

Now we can create an expression to concatenate a greeting to whatever name the user passes into our function.

So we'll type “Hello” with a space after it (nested in quotes, since we’re dealing with a text string), then add an ampersand to concatenate the pieces of text together, and finally we'll add the “name” variable, which will capture whatever input the user provides via our function’s argument.

Simple!

And just to make the greeting especially enthusiastic, I'll also concatenate an exclamation mark at the end of the expression, like so:

To reiterate, by assigning the expression we just created to the name of the overall function, we're basically telling VBA that we want the output of this expression to be returned whenever somebody enters this function into a cell in their spreadsheet.

So at this point, our function should be good to take for a test run, so let's head over to Excel.

And now, instead of executing our VBA by running a macro, as you might have done before, we're simply going to invoke the name of our user-defined function like any other Excel function.

So in cell A1, I’ll start typing “=he”...

And as you can see, Excel is smart enough to recommend our user-defined function via autocomplete, just like with standard Excel functions.

So let's hit Tab to select our function, and then for the single argument it takes, I'll just enter my name:

Now if we close the parentheses just like with any other function and then hit enter, it should now pull that argument (my name) and print our personalized greeting.

Huzzah!

Example #2: How to create a more complex UDF

So now that you've created your very first user-defined function, it's time to take on a slightly more complex and practical example, and write a function that calculates compound interest.

This means it will calculate the value of an investment at some point in the future, based on:

• Its current value
• The rate of return on the investment
• And how many periods the investment can appreciate over (that is to say, how many years into the future we're trying to project the value of the investment for)

Here’s the formula for compound interest, assuming that interest is calculated annually.

The formula can be a little more complicated if you're factoring in the possibility of calculating interest monthly or over some other period, so to keep things straightforward we're only going to worry about interest that compounds yearly. 

So let's break down what this means. In this formula:

• A represents the future value of our investment after it has appreciated 
• P stands for the principal, which is basically the initial amount we're investing 
• i is the interest rate, or rate of return 
• And N is the number of years over which that interest is compounded – basically, the time horizon of interest for our investment 

And as you can see, the formula to get to our final amount A is: 1 plus the interest rate i, raised to the power of the number of years 'n' over which the investment is compounded, all multiplied by the initial principal 'p'. 

Here's how to set it all up

When translating a formula like this into VBA code, or most any other programming language, the first step in assembling the “algorithm” is typically to identify the variables (or in our specific case, function arguments), and declare them with appropriate data types.

So let’s stand up the frame of our function, and then dig into those arguments.

First up, right below our “Hello” function definition in the same module, I'll create an empty shell of a function named “CompoundInterest”:

The next step, as we saw earlier, will be to define our function arguments with appropriate data types.

Our first argument will be for the initial amount of the investment, which was P in our formula.

Since that investment could potentially have a decimal component (i.e., dollars AND cents), we'll define it with a double data type. 

Next up is our interest rate argument i, which could also obviously have a decimal component, so we'll make it a double as well. 

Important: Note that multiple arguments in the function definition need to be separated by commas.

Then there’s N, which represents the number of years over which our investment is compounding. To keep things simple, we'll only worry about whole numbers of years, so we can make that argument an integer.  

With our function arguments set up, there’s actually not all that much work left to do. It’s really just a matter of replicating that formula for compound interest, but using our function arguments as the respective variables/inputs to the formula, like so:

But just like with our “Hello” function, we’ll also need to make sure the output of that formula/expression is explicitly assigned as the return value of the function, which we can do by preceding the expression with the name of our function - CompoundInterest – followed by the equals sign: 

At this point, our function should be fully operational (cue Star Wars theme), so now let's see it in action! 

Flipping back to Excel in our workbook, in a random cell I’ll type “=comp”; note that I’m NOT entering the entire name of the function. 

As you can see, Excel is once again smart enough to “guess” that we wanted to use our CompoundInterest function, in the same way that it might “guess” VLOOKUP if we typed “=VL”.

So I’ll just arrow down to select it, and then hit Tab to lock it in.   

And now for the arguments – a.k.a., just how rich do I want to be in this hypothetical scenario? 

• For the initial principal, I'll enter $10,000
• Then, let's assume a very optimistic rate of return of 12%, or 0.12
• Finally, we'll see what $10,000 at a 12% rate of return does over the course of 25 years

Now closing the parentheses and hitting enter... 

It looks like the final value of our $10,000 investment would be about $170,000 – not too shabby!

And just to prove that this function responds dynamically to different inputs, if I change the rate of return to a more reasonable, say, 8%… 

Our final value drops by about $100,000, which is kind of a bummer, but at least we know our function is working!  

How to package UDFs as Add-Ins

Now that we know how to create user-defined functions, it's worth considering how we can access those functions outside the workbooks that we originally created them in. After all, a function that would be useful in one workbook would probably be useful in many, right?

So to share a collection of user-defined functions with our team (or anybody else who might use them), the best option is to package our functions as a VBA add-in, which will allow anyone who installs the add-in to use the functions as though they were already built into Excel.

And the good news is that the process of doing this is super simple! So let's go ahead and package the two user-defined functions we've created as our very own add-in.

The first step will be to save the Excel file we've been working in – but not with the standard .xlsx extension, or even as a .xlsm “macro enabled file” file.

Instead, we're going to save it as a .xlam file, which specifically denotes it as an add-in.

So let’s try saving our file As “Utility Functions.xlam”…

Note here that Excel is recommending the file be saved to a designated “AddIns” folder, as a result of us choosing the Excel Add-in/.xlam file type. This is where Windows stores Microsoft add-ins by default, meaning that Excel “knows” to look here for such add-ins. As such, I’ll stick with the recommended defaults and hit “Save”.

At this point it will look like our file has disappeared, but it has not; rather, it’s simply been converted to an add-in.

The logical next step then, is to officially install this add-in to Excel. Once that’s been done, whenever we open up Excel on the same machine that the add-in is saved to, those functions in our add-in will be available to us in the same way as standard functions like SUM or VLOOKUP.

So to install the add-in, in any Excel workbook (it doesn’t matter which one), we'll select the Developer tab on the Ribbon…

And then in the “Add-ins” section I'll hit the “Excel Add-ins” button:

This presents us with a list of four optional add-ins built right into Excel, but we're interested in our own custom add in, so we'll click “Browse”…

This takes us directly to the “AddIns” folder that our add-in file was saved to by default:

Now if we simply select the file and click OK…

We see our “Utility Functions” add-in listed among the other available add-ins, but with a check mark next to it. Seems like a pretty good sign, so let's go ahead and click OK once again.

Testing the Add-In works

Now, to prove our add-in can be used in any Excel file, I’ll close out of Excel altogether, then re-open it and create a new blank file.

The only thing left at this point is to see if those functions that we built into our add-in are available to us in this new instance of Excel. Let’s try our “Hello” function first:

So far, so good!

Next up is testing our CompoundInterest UDF.

Let's see what would happen if we invested $20,000 at a 10% rate of return for 22 years…

And it works. Awesome!

Conclusion

As you can see, UDF’s are incredibly helpful for any data analyst. And I again want to emphasize that these functions will be available in any spreadsheet, or on any computer in which the add-in has been installed to Excel.

How handy is that!?

And by packaging custom UDFs as an add-in, you can effectively make them usable as standard, built-in Excel functions to anyone you want.

The only real limitation is your VBA programming skills, and your creativity in conceiving new ideas for functions.

I can’t help you with the coming up with new function ideas, but if you want to take your UDF-creation skills to the next level by learning more about VBA, go ahead and check out my VBA Programming course.

Unlike the usual “recipe” style tutorials you’ll find on the web, this is a serious programming course that will teach you exactly how to program in VBA, and is loaded with projects and exercises to make sure you master every concept.

Trust me. Not only will learning VBA help make your life easier and your Excel tasks faster and automated, but your co-workers will thank you after they’ve installed your first add-in!