How to use and store custom functions 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:

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.

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.

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.

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.