In a previous post I discussed storing your UDF's in PERSONAL.XLSB so that you could use them in any workbook.
You do run into a problem with this though in that unless you create a reference to PERSONAL.XLSB, every time you want to use your UDF, you need to precede the function name with PERSONAL.XLSB!.
But there is an alternative way to use your UDF's and avoid this clumsiness. You can create your own add-in and store your UDF's in that.
Add-ins can be very complex and allow you to write large, complicated applications, or you can use them to create customized Ribbons and provide extra functionality via the Ribbon icons you create.
But in this example, we're just going to look at creating a UDF and saving that into our own add-in.
Once you know these basic steps (and it's not hard) you can experiment on your own.
Creating an Add-In
An add-in is just a workbook you save as an add-in (.xlam) from the Save As dialog box. NOTE: I'm using Excel 2013. In Excel 97-2003 the file extension is .xla.
Once you've saved it and loaded it into Excel, the functions in it become available to all workbooks open in Excel, and you can edit the add-in's VBA code from the VBA editor as you would any other macro enabled workbook.
Where the Add-In is Saved
When you choose to save the workbook as an add-in, Excel automatically changes the save location to the default folder for add-ins.
On my Windows 10/Excel 2013 machine, this default is C:\Users\pgt\AppData\Roaming\Microsoft\AddIns, pgt being my username.
You can save the add-in to any folder though, but if you do, when you want to load the add-in into Excel, you'll have to locate it on your computer by browsing to that folder.
If you save it to the default location you don't have to go hunting around for it on your computer, Excel will automatically list it.
What You Need To Do - Step by Step
Create a new workbook and write a UDF in a code module. I'm going to use the CUBE function I wrote in my last blog post about referencing PERSONAL.XLSB.
Function cube(number As Long) As Long cube = number ^ 3 End Function
Click on the File tab. On the right hand side you should see some of the file's properties listed. At the bottom of this list click on Show All Properties.
When you come to loading the add-in into Excel, Excel uses the title and comments to provide you with information about what the add-in does.
So click on Title and Comments, and fill in something appropriate like so:
Now click on Save As and save the workbook as an add-in (.xlam).
You'll notice that Excel hasn't kept the add-in open. The Title Bar won't show the name of your add-in file. Neither is the add-in visible in the VBA editor.
Loading the Add-In into Excel
To load your new add-in, go to the File tab again. Click on Options, at the bottom left.
In the Excel Options window, click on Add-Ins. Make sure Excel Add-ins is selected in the Manage box, and click Go.
Check the box beside your add-in and click on OK.
The function(s) in your add-in are now available for use, and if you go into the VBA editor (ALT+F11), you'll see the add-in is there and available for editing.
That's it. You've now created your own add-in, loaded it into Excel and you can use the UDF's in it. If you want to write more UDF's, just write the code into the add-in in the VBA editor and save it.
Please Share
If you liked this or know someone who could use it please click the buttons below to share it.
Mathieu says
Hi,
For some reason I don’t get that cube function available as @cube but instead VBAProject.Module1@cube….
Not a big deal I guess. But I would still prefer the “simplified” format.
Cheers
Philip Treacy says
Hi Matthieu,
Can you please open a Helpdesk ticket and send me the .xlam file you are using as your add-in.
The function should just be available as =cube()
Regards
Phil
Chris says
Hi.
My question is :
Say I have a work book with UDF loaded into it.
Can we not transfer/copy all these udf from the original workbook into a new workbook we are creating ? Rather than saving an Xlam file.
By saving / transferring the UDF directly into the new workbook, we can eliminate the need to share the add-in file.
Philip Treacy says
Hi Chris,
Yes you can do this. Up to you how you want to do it.
Cheers
Phil
Tony says
Hi Phil,
This looks great, but I have a problem.
I’ve followed what you have here, but I get the error #NAME? although I am selecting the function as I can see it when I start typing it.
I’ve tested the function within a module on the spreadsheet and it works OK, but fails when it’s in the add-in. So close but…
My example is:
Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum
End Function
Am I missing something?
Thanks,
Tony
Catalin Bombea says
Hi Tony,
Make sure you have that function only once, in the Add-in only, remove it from other open files modules, there can be no duplicate functions. Also, check the name of the function, maybe it’s a small typo in the function name, or you are using a named range and the name is spelled incorrectly.
Catalin
Craig says
Also, make sure that the function is Public (prepend that word before the word Function).
Charlie says
Great tips Phil! I create add-in’s for many of my own UDF’s, but I never knew about using the Title and Comments fields. Thanks!
Philip Treacy says
You’re welcome.
Phil
John says
Hi Philip and thanks for this but what happens if you share a workbook that uses an add in, will it load automagically or will I have to handhold every user through installing?
Philip Treacy says
Hi John,
If you share a workbook that relies on the add-in, and the user doesn’t have that add-in loaded, then they will get a warning on opening the workbook that links in the document (to the add-in) can’t be updated.
They will have the option to update those links, but you probably don’t want to go down that route with users and the problems that can cause.
What you can do is store the add-in on a shared network folder accessible by all, and load the add-in from that shared location. It’s important this is done before creating the workbooks that will use the add-in.
Any user who requires the add-in also loads it from the shared network drive. This way all links in workbooks requiring the add-in are pointing to the same place.
The only hiccup is that when you load the add-in into Excel from the shared network drive, Excel asks if you want to copy the add-in to your local Add-Ins folder on your computer. You want to say no here. Not sure if this prompt can be circumvented by deploying the add-in via Group Policy or some other centralised means.
Another benefit to loading the add-in from a shared network location is that you can maintain read/write access to it, but prevent others from changing it. So you can update the add-in as you need and the updates are loaded the next time a user starts Excel.
Regards
Phil