How to use and store custom functions in Excel

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:

= GetMaxBetween(A1:A6,10,50)

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:
Using a UDF along with a native function

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:
Get the value associated with the maximum number in the range.

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.

Sub MacroWithUDF() Dim Rng As Range, maxcase, i As Long With ActiveSheet.Range(Cells(ActiveCell.CurrentRegion.Row, ActiveCell.Column), Cells(ActiveCell.CurrentRegion.Rows.Count _ + ActiveCell.CurrentRegion.Row - 1, ActiveCell.Column)) maxcase = GetMaxBetween(.Cells, 10, 50) i = Application.Match(maxcase, .Cells, 0) .Cells(i).Interior.Color = vbRed End With End Sub

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.
VBA macro uses UDF.

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.

Function SpellGetMaxBetween(rngCells As Range, MinNum, MaxNum) SpellGetMaxBetween = SpellNumber(GetMaxBetween (rngCells, MinNum, MaxNum)) End Function

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.
Nest several UDFs to find the maximum value in a range and spell it.

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:

= My_Functions.xlsm!GetMaxBetween(A1:A6,10,50)

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:
Save the add-in to be filled with UDFs.

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.
Add add-ins to Excel.

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.
Store UDF to Excel add-in.

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.

17 comments

  1. I think I get the general direction of these instructions except I'm on a Mac and the instructions for Windows don't really match. I *think* I have created the add-in and connected it to Excel (step 2). And I have created a function in VBA called DISCOUNT. But my function is showing up in the functions list as PERSONAL.XLSX!DISCOUNT. I would like to not have to pre-pend all of my functions with PERSONAL.XLSX!. Can you help me with this?

    Thanks,
    LauraKL

  2. Thankyou for the invaluable assistance provided by you

  3. hi Alexander, just wanted to thank you as I spent hours on searching how to 'transfer' my vba function as simply as possible and your add-in way fits this need the most

  4. After successfuly creating addin files and selecting the addin in the workbook that wants to use the adding code, I get "function not found errors" . That is, even when the addin shows up in the vba editor project pane.

    I am now using Office365. After going thru the process of adding a Reference, the Reference module no longer shows.
    Could you suggest what might be going wrong here please.

    • Hi! Unfortunately, I don't know what custom function you added or what actions you performed. I don't think this is an error message. It is probably a message from a custom function. If you're using Office365 for Web, VBA doesn't work there.

  5. I have created and installed a custom add-in as described (.xlam), and on my computer it all works fine (yey!). However when a workbook using the UDFs is shared to another users commuter, all the UDFs are prefixed with the file location of the add-in on MY computer, and so get a #NAME error. The other user also has a copy of the add-in installed on their C drive (and so manually removing the prefix correctly resolves the function name and it works). As other comments have hinted, it seems Excel creates an external link to the UDFs in the add-in behind the scenes. This can be verified by adding a UDF to a new workbook, going to the Data tab, and in the "Queries & Connections" section looking on "Edit Links". I see a link to my add-in.

    Is there a way to avoid creating this link, and just allowing excel to try and resolve the UFD name it's self, rather than linking to the users add-in location.

  6. Thanks! This was a huge help!

  7. Hello! I am pretty sure that I followed all the steps just as you described them. The UDFs are working properly. However, it seems that Excel recognizes my add-in file (storage for all my UDFs) as a separate file and therefore it ask me each time I open any workbook that relies on the UDFs if I want to refresh the data connections (to the UDF file). That sucks. Is there a way to circumvent that problem? Thank you.

  8. I have been copying and pasting the formula below. Is there a way to write this as a function so I can just hit the = sign, select it, and then entire my parameters? The only thing that changes is the table array in the VLOOKUP part.

    =IF(ISERROR(VLOOKUP($A$2, SheetName!$A$2:$A$200000,1,0)), "No", "Yes")

  9. I've created an add-in file as you've described above. It has multiple Modules used as containers for the UDFs I've defined. When I try to reference those UDFs in other workbooks I have to prefix the UDF name with the add-in filename as you've described in Method 1 above. It seems to me that Excel should automatically search the add-ins file for a UDF if it is not defined in the active workbook.

    Have I done something wrong or omitted some setup step that's created this behavior?

  10. I am having difficulty using the TODAY() function in the Excel date filter (I'd like to include it in a macro). Can I use a UDF or is there an easier way to do that?

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)