But what happens when you need to create a new Excel file that also needs to use the same UDF?
For now, you simply have to re-create the UDF manually in every Excel file you create. And that is not really great from couple of reasons: First it is annoying. Additional manual steps to do every time you create a new Excel file. The second reasons is that if you decide to improve the UDF, you now have to go back to each Excel file and manually implement the change in the UDF again. And that is really not fun and a potential bigger issue with multiple revisions of the same functions running around your file system.
Luckily Microsoft provides a way to create Global UDFs. It is global in the sense that it is automatically enabled to every Excel file running in your machine. The trick is to turn the UDF inside an Excel Macro-Enabled-Workbook file (.xlsm) to an Excel Add-In file (.xlam) and save this new file in the right location so it is always enabled.
As a reminder this is our beloved Half function:
Half = value / 2
Instructions below explain how to convert the workbook to Add-In and how to enable it:
1. Edit the Excel Macro-Enabled-Workbook file (.xlsm).
2. In Excel, go to file properties and change the title to a meaningful name. Let’s say “Global-Functions”.
3. And save-as Excel Add-In (.xlam)
4. Go to the Finder and copy the new file Global-Functions.xlam to the Excel’s Add-In folder.
If is a rather “secret” folder and it changes from Windows to Mac and across every Excel version.
In my case for Excel 2011 on Mac it is in this location:
Applications->Microsoft Office 2011->Office->Add-Ins
5. The last thing we have to do is to enable the Add-In. Open Excel. Click on the Tools menu. Select the Add-Ins… option. And a pop-up windows will be opened.
6. Click on the checkbox to enable the Add-In.
And that’s it. Now every time you create a new Excel file the UDF will be automatically available.
As always, more information is available in the Microsoft site here.