How to make an XLAM UDF call portable?

It seems that when I call a UDF in an XLAM file, the path to the XLAM file is embedded in the spreadsheet. When I open the spreadsheet from another machine (which has the same XLAM add-in installed, just installed to a different path) then Excel complai开发者_StackOverflow中文版ns "This workbook contains links to other data sources..." This doesn't seem to be a problem with UDFs in XLLs. Is there a way around this?

This behaviour is a consequence of the way Addin UDFs are implemented in Excel.

There are 3 approaches to alleviating the problem:

Use an installer that forces a particular path

Add some code to the XLAM that inspects Links for each workbook opened and if the link is to your XLAM but in a different path it does a find and and replace so that the path is corrected.

Convert your XLAM UDFs to XLLs (if VBA convert to VB.Net and use Excel DNA or Addin Express to make a VB.Net XLL)

I know I'm ten years late but if you put this in Workbooks.Open code on your files you are sharing, they will then need to be xlsm files, just bear in mind this will change all external links in formulas to the local addin path:

Sub AddInExternalLinkFix()
    Var = ActiveWorkbook.LinkSources(xlExcelLinks)
    Nwname = Application.AddIns.Item("YourAddInName").FullName
    If Not IsEmpty(Var) Then
        For iCounter = 1 To UBound(Var)
                ActiveWorkbook.ChangeLink Name:=Var(iCounter), newname:=Nwname, Type:=xlLinkTypeExcelLinks
        Next iCounter
    End If
End Sub