UDFs in Excel add-ins only work when I open the add-in first. How do I get Excel to link the add-in to the spread sheet naturally?

Question

I'm using Excel in Microsoft 365 and I have several UDFs written in MS VBA and saved in a *.xla file located in C:\Users\username\AppData\Roaming\Microsoft\AddIns. Additionally, this file has been successfully loaded as an add-in because I can see it in the Add-ins window made available on the Developer tab's "Excel Add-ins" icon. I can also view the code in the module from the Visual Basic window.

The problem is all instances of a UDF have the following error: #NAME?.

However, if I open the *.xla file first (which results in a completely blank spreadsheet i.e, no cells) and then open a saved spreadsheet containing my UDFs, all of the UDFs work fine. While this is a workaround, it is a bit clumsy and I would like a cleaner solution.

My expectation was because my *.xla file was loaded as an Excel Add-in it would naturally be available to use in any Excel spreadsheet.

I also tried unchecking the Add-in I created when the spreadsheet was open but when I re-opened the spreadsheet, each instance of a UDF is altered with the path to the *.xla file mentioned above. New instances of a UDF will not work unless I enter the full path. For example:
='C:\Users\username\AppData\Roaming\Microsoft\AddIns\myAddIn.xla'!UDFname(E9,E10).

Answer 1

I was curious about this issue and went back to the website of the late but great Chip Pearson. Following the instructions from his page, I was able to accomplish what you are stuck on. Looking at your question (well done btw), it does look like you've saved it to the proper location, but i would triple check as that seems most probable for an error.

For what it's worth, here's what I was able to do to get a UDF to appear as a recognized formula from an initial load blank file:

• Downloaded Chip's test file and extracted the zip
• Within Excel → developer tab → Excel Add-ons (NOT COM!) → Click on browse
• Whatever folder opens is where the xla should be, so I copied Chip's file there
• I closed and reopened Excel, then reviewed the code in the module, which does not have any UDF's an it, so I added a formula thanksPearson and hit save. The entire code from the module is saved below from the xla.
• I saved, closed Excel, then reopened and the formula =thanksPerason("boom") performed as expected.

Option Explicit

Sub MacroToRunOne()
    Dim S As String
    S = "Hello World From One:" & vbCrLf & _
        "This Add-In File Name: " & ThisWorkbook.FullName
    MsgBox S
End Sub

Function thanksPerason(anINput As Variant) As String
    thanksPerason = " what the function is this" & anINput
End Function


Sub MacroToRunTwo()
    Dim S As String
    S = "Hello World From Two:" & vbCrLf & _
        "This Add-In File Name: " & ThisWorkbook.FullName
    MsgBox S
End Sub

Remember Pearson

In case it's not obvious, I'm a fan of the work of Chip Pearson who died way too early in 2018 due to a car accident. If you read through his website, it is absolutely a masterpiece of well-written comprehensible explanations with examples that are hailed as immeasurably more useful than anything Microsoft ever produced. Anyway. Just a guy worth giving a shout-out to every now and then considering the amount of value he delivered to the world.