Today we’ll continue exploring custom Excel functions. As you already know how to create UDFs (and, I hope, you’ve also tried applying them in your Excel), let’s dig a bit deeper and learn how to use and store user-defined functions in Excel.
Moreover, we’ll show you how to easily save your functions in an Excel add-in file to use them later in a few clicks.
So, here's what we're going to talk about:
Different ways of using UDF in Excel
Using UDFs in worksheets
Once you have verified that your UDFs are working correctly, you can use them in Excel formulas or in VBA code.
You can apply custom functions in an Excel workbook in the same way as you use regular functions. For example, just write in a cell the formula below:
UDF can be used in combination with regular functions. For example, add text to the calculated maximum value:
= CONCATENATE("Maximum value between 10 and 50 is ", GetMaxBetween(A1: A6,10,50))
You can see the result in the screenshot below:
You can find the number which is both maximum and is in the range from 10 to 50.
Let’s check another formula:
= INDEX(A2:A9, MATCH(GetMaxBetween(B2:B9, F1, F2), B2:B9,0)), the
The custom function GetMaxBetween checks the range B2:B9 and finds the maximum number between 10 and 50. Then, using INDEX + MATCH, we get the product name that matches this maximum value:
As you can see, usage of custom functions is not too different from regular Excel functions.
When doing this, remember that a user-defined function can only return a value, but cannot perform any other actions. Read more about the restrictions of user-defined functions.
Using UDF in VBA procedures and functions
UDFs can also be used in VBA macros. Below you can see the macro code that looks for the maximum value in the range from 10 to 50 in the column containing the active cell.
The macro code contains the custom function
GetMaxBetween(.Cells, 10, 50)
It finds the maximum value in the active column. This value will be highlighted then. You can see the result of the macro in the screenshot below.
A custom function can also be used inside another custom function. Earlier in our blog, we looked at the problem of converting a number to text using the custom function named SpellNumber.
With its help, we can get the maximum value from the range and immediately write it down as text.
To do this, we will create a new custom function in which we will use the functions GetMaxBetween and SpellNumber that are already familiar to us.
As you can see, the GetMaxBetween function is an argument to another custom function, SpellNumber. It defines the maximum value, as we have done many times before. This number is then converted to text.
In the screenshot above, you can see how the SpellGetMaxBetween function finds the maximum number between 100 and 500 and then converts it to text.
Calling UDF from other workbooks
If you have created UDF in your workbook, this, unfortunately, does not mean that you won’t face any problems at all.
In my experience, most users sooner or later create their personal collection of macros and custom functions to automate individual processes and calculations. And here the problem arises - the code of user defined functions in Visual Basic needs to be stored somewhere in order to be used later in work.
To apply the custom function, the workbook where you saved it must be open in your Excel. If it is not, you will get the #NAME! error when trying to use it. This error indicates that Excel does not know the name of the function that you want to use in the formula.
Let's take a look at the ways in which you can use the custom functions you create.
Method 1. Add the workbook name to the function
You can specify the name of the workbook in which it is located before the name of the function. For example, if you saved a custom function GetMaxBetween() in a workbook named My_Functions.xlsm, then you must enter the following formula:
Method 2. Store all the UDFs in one common file
Save all custom functions in one special workbook (for example, My_Functions.xlsm) and copy the desired function from it into the current workbook, if necessary.
Each time you create a new custom function, you need to duplicate its code in the workbook in which you will use it. With this method, several inconveniences may arise:
- If there are a lot of working files, and the function is needed everywhere, then the code will have to be copied into each book.
- Remember to save the workbook in a macro-enabled format (.xlsm or .xlsb).
- When opening such a file, protection against macros will display a warning each time, which must be confirmed. Many users get scared when they see a yellow bar warning that asks them to enable macros. To avoid seeing this message, you need to disable Excel protection completely. However, this may not always be correct and safe.
I think you will agree with me that all the time opening a file and copying the code of user-defined functions from it or writing the name of this file in a formula is not the best solution. thus, we came to the third way.
Method 3. Create an Excel add-in file
I think the best way is to store frequently used custom functions in an Excel add-in file. Advantages of using the add-in:
- You need to connect the add-in to Excel just once. After that, you can use its procedures and functions in any file on this computer. You do not need to save your workbooks in .xlsm and .xlsb formats since the source code will not be stored in them but in the add-in file.
- You won't be bothered by macros protection anymore as add-ins always refer to trusted sources.
- An add-in is a separate file. It is easy to transfer it from computer to computer, to share it with colleagues.
We'll talk more about creating and using an add-in later.
Using add-ins to store custom functions
How do I create my own add-in? Let's go through this process step by step.
Step 1. Create the add-in file
Open Microsoft Excel, create a new workbook, and save it under any suitable name (for example, My_Functions) in add-in format. To do this, use the menu File - Save As or the F12 key. Be sure to specify the file type Excel Add-in:
Your add-in will have the extension .xlam.
Tip. Please note that by default Excel stores add-ins in the C:\Users\[Your_Name]\AppData\Roaming\Microsoft\AddIns folder. I recommend that you accept the default location. If you’d like, you can specify any other folder. But then, when connecting the add-in, you will need to find and specify its new location manually. If you save it in the default folder, you don't have to look for the add-on on your computer. Excel will automatically list it.
Step 2. Connect the add-in file
Now the add-in we have created needs to be connected to Excel. Then it will be loaded automatically when the program starts. To do this, use the menu File - Options - Add-Ins. Make sure Excel Add-Ins is selected in the Manage field. Click on the Go button at the bottom of the window. In the window that appears, mark our add-in My_Functions. If you don't see it in the list, click the Browse button and browse to the location of your add-in file.
If you are using an add-in to store custom functions, there is one simple rule to follow. If you are transferring the workbook to another people, be sure to also transfer a copy of the add-in that contains the functionality you want. They should connect it in the same way as you did now.
Step 3. Add custom functions and macros to the add-in
Our add-in is connected to Excel, but it doesn't have any functionality yet. To add new UDFs to it, open the Visual Basic Editor by pressing Alt + F11. Then you can add new modules with VBA code as described in my Create UDFs tutorial.
Select your add-in file (My_Finctions.xlam) in the VBAProject window. Use the Insert - Module menu to add a custom module. You need to write custom functions into it.
You can either type the code of a user defined function manually or copy it from somewhere.
That's all. Now you have created your own add-in, added it to Excel and you can use the UDF in it. If you want to use more UDFs, just write the code in the add-in module in the VBA editor and save it.
That's it for today. We've learned how to use user defined functions in your workbook. We really hope you find these guidelines helpful. If you have any questions, write in the comments to this article.