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.
I wrote my udf and stored it as xlam file.
Then, I shared that exact .xlam file with my colleagues and they installed it as per steps on this page.
However, when my colleague created an Excel sheet using the formula in xlam file and shared it back to me, it had xlam file directory attach to it.
How can I avoid this annoying problem? Is there any other way to solve this problem rather than sharing xlam file on a shared network folder?
Hi Prasit,
If you go to ribbon-Data tab, Links and press the Change link button, can you change the link pointing to the addin to your addin location?
Or, try this solution with macros:
https://jkp-ads.com/Articles/FixLinks2UDF02.asp
Hi, I created a user defined function and saved as an add-in that everyone accessed/shares on a network drive.. Everything was working fine until some folks upgraded there Excel version to Excel 365. Those users now have error messages “#Name?” for the cells for the file that was created using the prior excel2016 or earlier. When the excel 365 user opens the file, they see a @ symbol after the = sign followed by a long file path and then user defined function. If they go into the cell and delete the @ and file path, leaving just the = and function, it works fine. The users on the network that have not migrated to excel 365 version do not encounter this issue and when they open the file created/saved in the non excel 365 version, they have no issues (they do not see @ followed by a long path to where the add in is. Additionally, if the excel 365 version user creates a file with the user defined function, 365 users don’t encounter any issues but now the non-365 version user sees the long file path and encounters issues. Perhaps there is a version compatibility issue for user defined functions? Do you know if there is a way to fix this so that the user defined functions work properly regardless of which excel version a user is on? Thanks so much for any suggestions.
Please make sure there is no conflict between add-in functions and macro’s from the files they open.
Can you upload on our forum a sample addin?
I created an add-in for my user defined function and put it on a shared drive for all to use. Everyone loaded this add-in on the shared drive to their Excel. I used the function and saved it. When someone else subsequently goes into this same file, they see a long path for the function and If they click in that function cell, they then get an error message. Looks like the add-in creates a local copy and so someone else cannot use or access it unless they delete the long file path containing something like user and roaming/…. is there a way if someone else opens my file they would be able to see my function without the long path to the function and manipulate the parameters without getting an error message because they don’t have access to a local copy of my add-in that may be created? Thank you.
Hi Peter,
You can find useful information here.
Look after “Remove badly installed add-in” section, when they install again the add-in, users should click NO when they receive the message asking them if they want to store a local copy.
Catalin, Thank you!! Thank you!! Thank you for solving my issue that has puzzled me for quite a while!! You are the best!!!!
I have an add-in created like this and it still inserts the add-in name in front of the function. That is, for example, if I type in one cell
=MyFunction()
where MyFunction is one of the functions in the add-in, it will be changed automatically into
=MyAddIn.xlam!MyFunction()
where MyAddIn.xlam is the name of the add-in that I have created as described here.
This not only is bothering because of the clumsiness of the cell formula, but it creates issues with files that have been created before the use of the add-in (some old solution where the VBA was stored in the Excel file itself rather than in an add-in) and which has cells with the simple call
=MyFunction()
So now the old cells have the function not qualified with the name of the add-in while the new cells have the function with the name of the add-in. The new cells resolve properly but the old cell show #NAME? error. It would be inconvenient to just re-write the old files and also would not like to have the functions names qualified with the add-in name.
Is there any solution to this?
Hi Mitchell,
If you have 2 functions with the same name, it’s a name conflict, if you press the debug command in vb editor the compiler will raise an error saying “ambiguous name detected: MyFunction”, I guess this is the reason why you get the prefix before function name.
If you know that there may be files that will have the same function name in their vb modules, it’s better to rename your new addin function and replace the cell old function names with the new name.
If I have the user load an add-in that’s saved to a shared network drive, will that add-in be available for every workbook the user subsequently opens or creates?
Hi Keri,
Yes, that’s the purpose of an add-in, will be loaded each time excel opens, no matter if you open new or old file, the add-in is loaded at application level, not at a file level.
Of course, if the user is working on a computer outside the network, the add-in will not be accessible, therefore will not load when excel starts.
I have the following code, which has worked in the past:
Public 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
I did not include comments so I would remember how to use this UDF – can anyone help with an example?
Thank you
Hi Neil,
To use it in a worksheet, type in a cell:
=SumByColor(A1, B2:B10)
A1 interior color will be the color reference to sum, and B2:B10 is the range to be summed based on A1 color.
Thanks, Catalin, however, it is still not working. The workbook is macroenabled and I’m using Excel 2013.
I found another source code for counting cells by colour, but that code won’t work, either.
Hi Neil,
Can you please open a forum topic and attach your workbook so we can see your code.
Thanks
Phil
Great guide! Kicked it off by adding a date parsing function – turning YYYYMMDD into excel date:
Function PDATE(rng As Range) As Date
PDATE = DateSerial(Left(rng, 4), Mid(rng, 5, 2), Right(rng, 2))
End Function
Thanks again!
Thanks Lucas
Mr. Treacy, thank you for a very helpful article. I am using Excel 2003 on a Windows 7 machine. I was able to create my XLA file and was able to attach it to Excel so that it loads every time I open any XLS file. My problem is that code in the XLS file does not recognize the code in the XLA file. The Subs and Functions are not recognized as I type the code, and running the VBA code creates a “Sub or Function not defined” compile error. I even did a Cut&Paste on the routine name to make sure I was not misspelling it, but to no avail. Any suggestions?
Thanks William.
If you want to call subs or functions from VBA, that reside in your add-in, you can set a reference to that add-in from Tools -> References in the VBA editor.
But an easier way is to use Application.Run. Let’s say you want to call a function named CUBE which is in your add-in. This function takes one numeric argument.
To call it in your workbook’s VBA module you’d write
ans = Application.Run(“CUBE”,2)
where ans is a variable you’ve declared to receive the result.
Cheers
Phil
Excellent. Or perhaps I should say, “EXCELlent.” Very helpful. Thank you.
No worries.
So impressed and happy, you made my day. Many Thanks
🙂 Glad to help
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
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
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.
Hi Chris,
Yes you can do this. Up to you how you want to do it.
Cheers
Phil
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
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
Also, make sure that the function is Public (prepend that word before the word Function).
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!
You’re welcome.
Phil
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?
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