The ability to create a User-Defined Function (UDFs in short) is an awesome feature of Excel. It basically removes the limitation of built-in functions. Aside from coding them, storing and distribution of UDFs are another point. In this guide, we’re going to show you How to store user-defined function in Excel.
UDFs in a Nutshell
A User-Defined Function is a custom function you can create your own by using VBA for Excel. For example, if you find yourself needing to calculate the volume of a sphere frequently, you can create a UDF to find it to use for only one function and enter the parameters you’ve defined before.
Storing User Defined Functions in Excel
Storing in a Specific Workbook
If your intention is to keep your custom functions in a single workbook, all you need to do is to save the file as an XLSM (Macro-enabled Excel Workbook). You can find the option in Save As section.
The downside of this method will occur when you want to use the functions in another workbook. You need to add the file's path in front of the functions.
As a result, this approach becomes less convenient as the file path grows. Also, you may get macro warnings each time the file with UDFs is summoned.
Before heading through more practical ways, you may want to check the LAMBDA function. If your custom functions are for its parent workbook only, and you have Excel 365 subscription, you can create custom functions without coding experience.
The LAMBDA is a special function that can create custom functions in the scope of a named range. Briefly, the function allows you to create named ranges acting like UDFs.
Storing in an Add-in
Alternatively, you can save your custom functions in an add-in file and make it load automatically anytime Excel starts. Neither file paths nor macro warnings.
To save your Excel file with your custom functions as an add-in, choose Excel Add-in, XLAM, option in the Save As dialog.
Note that; add-in files do not keep the UI content. Thus, you may not see the worksheet contents properly. It would be good to move your code into a separate empty file.
Once the file is saved follow these steps to run the add-in at the start:
- Go to File > Options > Add-Ins.
- Select Excel Add-ins from the Manage drop-down.
- Click Go.
- Check your add-in (if it isn't in the list, click Browse and find it).
- Click OK.
You can move or send your add-in file to any other machine you need the formulas.
Storing in Personal Macro Workbook
Excel also offers a special workbook that acts as a default add-in. When it's created, Excel runs the workbook in the background automatically. Thus, you can call any macro or function save in the Personal Macro Workbook.
If it's created, the Personal Macro Workbook is stored in C:\Users\[user name]\AppData\Local\Microsoft\Excel\XLStart folder, under the name Personal.xlsb. Do note to replace [user name] with your own.
Also, you can see it in the VBA window. If not, open the Record Macro dialog and select Personal Macro Workbook as Store macro in option.
Then, click OK to start recording. You can immediately click the Stop Recording button. Once it's done, open your VBA window. You will see the Personal.xlsb file in the Project Explorer window.
You can write and store UDFs in the modules of the Personal Macro Workbook. Just do use the Save button in the VBA window to save the Personal.xlsb.
