• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Create an Excel Add-In for User Defined Functions (UDF’s)

You are here: Home / Excel VBA / Create an Excel Add-In for User Defined Functions (UDF’s)
create an excel add-in for user defined functions udfs
November 11, 2016 by Philip Treacy

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!.

using function in 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.

save as xlam

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.

show all file 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:

add file properties

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.

Excel options, add-ins

Check the box beside your add-in and click on OK.

list of available add-ins

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.

using udf from add-in

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.

create an excel add-in for user defined functions udfs

More User Defined Function Posts

writing udfs in excel with python

Writing UDFs in Excel With Python

Write Excel UDFs in Python. Use the power of the vast Python libraries for data science, statistics, web scraping or database connectivity.
Return an array from a udf

Return An Array From A UDF

If you can return an array from a udf you are able to return multiple pieces of data and place them into multiple cells
Return a range from a UDF

Return a Range from a UDF

If you return a range from a UDF this allows you to use that range in other functions and calculations.
creating a reference to personal.xlsb for user defined function udf

Creating a Reference to PERSONAL.XLSB for User Defined Functions (UDF’s)

How to create a reference to PERSONAL.XLSB so that using User Define Functions (UDF's) is easier
Creating Multi-Function UDF's

Creating Multi-Function UDF’s

Combine separate functions into one, multi function UDF (user defined function)

Creating a UDF (User Defined Function) in Excel

Create your own functions in Excel

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Category: Excel VBATag: user defined function
Previous Post:Power Query if Statements
Next Post:Excel COUNT MATCH Array Formula

Reader Interactions

Comments

  1. Prasit

    January 8, 2022 at 12:33 am

    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?

    Reply
    • Catalin Bombea

      January 10, 2022 at 2:41 pm

      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

      Reply
  2. Peter

    October 22, 2020 at 12:44 am

    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.

    Reply
    • Catalin Bombea

      October 23, 2020 at 10:21 pm

      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?

      Reply
  3. Peter

    February 2, 2020 at 1:27 pm

    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.

    Reply
    • Catalin Bombea

      February 3, 2020 at 2:44 am

      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.

      Reply
      • Peter

        February 6, 2020 at 10:21 am

        Catalin, Thank you!! Thank you!! Thank you for solving my issue that has puzzled me for quite a while!! You are the best!!!!

        Reply
  4. Mitchell N

    January 25, 2020 at 8:53 am

    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?

    Reply
    • Catalin Bombea

      January 26, 2020 at 2:00 pm

      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.

      Reply
  5. Keri

    February 7, 2019 at 8:12 am

    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?

    Reply
    • Catalin Bombea

      February 9, 2019 at 2:17 pm

      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.

      Reply
  6. Neil Mitchell

    January 30, 2019 at 3:13 pm

    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

    Reply
    • Catalin Bombea

      January 31, 2019 at 12:39 am

      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.

      Reply
      • Neil Mitchell

        January 31, 2019 at 8:48 am

        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.

        Reply
        • Philip Treacy

          January 31, 2019 at 10:49 am

          Hi Neil,

          Can you please open a forum topic and attach your workbook so we can see your code.

          Thanks

          Phil

          Reply
  7. Lucas Hadin

    July 10, 2018 at 11:04 pm

    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!

    Reply
    • Philip Treacy

      July 16, 2018 at 11:03 am

      Thanks Lucas

      Reply
  8. William F Powers

    June 13, 2018 at 8:03 am

    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?

    Reply
    • Philip Treacy

      June 13, 2018 at 12:39 pm

      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

      Reply
      • William F Powers

        June 14, 2018 at 5:09 am

        Excellent. Or perhaps I should say, “EXCELlent.” Very helpful. Thank you.

        Reply
        • Philip Treacy

          June 14, 2018 at 8:50 am

          No worries.

          Reply
          • Alain

            February 10, 2020 at 11:45 pm

            So impressed and happy, you made my day. Many Thanks

          • Philip Treacy

            February 11, 2020 at 12:06 pm

            🙂 Glad to help

  9. Mathieu

    March 9, 2018 at 6:27 am

    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

    Reply
    • Philip Treacy

      March 11, 2018 at 9:03 pm

      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

      Reply
  10. Chris

    December 9, 2017 at 11:10 am

    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.

    Reply
    • Philip Treacy

      December 11, 2017 at 12:30 pm

      Hi Chris,

      Yes you can do this. Up to you how you want to do it.

      Cheers

      Phil

      Reply
  11. Tony

    April 28, 2017 at 9:27 am

    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

    Reply
    • Catalin Bombea

      April 28, 2017 at 3:51 pm

      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

      Reply
    • Craig

      February 16, 2018 at 4:18 am

      Also, make sure that the function is Public (prepend that word before the word Function).

      Reply
  12. Charlie

    November 12, 2016 at 2:57 am

    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!

    Reply
    • Philip Treacy

      November 13, 2016 at 8:16 pm

      You’re welcome.

      Phil

      Reply
  13. John

    November 11, 2016 at 10:09 pm

    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?

    Reply
    • Philip Treacy

      November 16, 2016 at 9:33 am

      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

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x