Unable to resolve links for XLA Add-in UDFs

%3CLINGO-SUB%20id%3D%22lingo-sub-1859672%22%20slang%3D%22en-US%22%3EUnable%20to%20resolve%20links%20for%20XLA%20Add-in%20UDFs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1859672%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20enabled%20XLA%20Add-in%20as%20follows%3C%2FP%3E%3COL%3E%3CLI%3EGo%20to%20Developer-%26gt%3BExcel%20Add-ins%3C%2FLI%3E%3CLI%3EBrowse%20XLA%20Add-in%2C%20for%20example%20C%3A%5Cdata%5CMyAddin.xla%3C%2FLI%3E%3CLI%3EIt%20will%20show%20a%20check%20mark%20for%20MyAddin%20after%20completion%3C%2FLI%3E%3C%2FOL%3E%3CP%3ENow%2C%20I%20perform%20the%20following%20steps%3C%2FP%3E%3COL%3E%3CLI%3ELaunch%20Excel%20(Office%20365%20or%20MSO%202016)%3C%2FLI%3E%3CLI%3EUse%20UDF%20available%20in%20my%20XLA%20Add-in%2C%20for%20example%20%3DUDFDemo(A1%2CA2)%3C%2FLI%3E%3CLI%3EUDF%20gets%20evaluated%3C%2FLI%3E%3CLI%3ESave%20and%20close%20the%20Excel%20Workbook%3C%2FLI%3E%3CLI%3EReopen%20the%20same%20Excel%20Workbook%3C%2FLI%3E%3CLI%3EUDF%20shows%20%23NAME!%20as%20the%20cell%20value%20with%20formula%20as%20%3D'%5Cstorage1%5Cdata%5CMyAddin.xla'!UDFDemo(A1%2CA2)%3C%2FLI%3E%3C%2FOL%3E%3CP%3ENext%2C%20I%20performed%20the%20following%20steps%3A%3C%2FP%3E%3COL%3E%3CLI%3EClick%20on%20Data-%26gt%3BEdit%20Links%3C%2FLI%3E%3CLI%3EThe%20dialog%20shows%20the%20location%20%5Cstorage1%5Cdata%5CMyAddin.xla%20for%20the%20source%20MyAddin%3C%2FLI%3E%3CLI%3EClick%20on%20'Change%20Source'%20and%20browse%20C%3A%5Cdata%5CMyAddin.xla%3C%2FLI%3E%3CLI%3EThe%20UDF%20gets%20reevaluated%20in%20the%20background%2C%20but%20the%20dialog%20still%20shows%20the%20location%20%5Cstorage1%5Cdata%5CMyAddin.xla%20for%20the%20source%20MyAddin%3C%2FLI%3E%3CLI%3EClick%20on%20'Close'%3C%2FLI%3E%3CLI%3EAs%20the%20UDF%20is%20reevaluated%2C%20save%20and%20close%20the%20Excel%20Workbook%3C%2FLI%3E%3CLI%3EReopen%20the%20same%20Excel%20Workbook%3C%2FLI%3E%3CLI%3EUDF%20shows%20%23NAME!%20as%20the%20cell%20value%20with%20formula%20as%20%3D'%5Cstorage1%5Cdata%5CMyAddin.xla'!UDFDemo(A1%2CA2)%3C%2FLI%3E%3C%2FOL%3E%3CP%3EI%20am%20unable%20to%20understand%20why%20Excel%20is%20looking%20for%20'%5Cstorage1%5Cdata%5CMyAddin.xla'%20when%20I%20had%20enabled%20'C%3A%5Cdata%5CMyAddin.xla'%20from%20Developer-%26gt%3BExcel%20Add-in%3C%2FP%3E%3CP%3EI%20am%20also%20unable%20to%20understand%20why%20Excel%20is%20not%20changing%20the%20location%20of%20the%20source%20when%20I%20use%20%22Change%20Source'%20in%20Data-%26gt%3BEdit%20links%3C%2FP%3E%3CP%3EIf%20I%20perform%20the%20above%20steps%20on%20a%20different%20machine%2C%20the%20UDF%20is%20getting%20evaluated%20without%20any%20%23NAME!%20error.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1859672%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1867452%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20resolve%20links%20for%20XLA%20Add-in%20UDFs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1867452%22%20slang%3D%22en-US%22%3EAs%20this%20is%20a%20problem%20I've%20fought%20with%20a%20lot%2C%20I%20created%20a%20solution%20which%20you%20can%20implement%20in%20the%20add-in%20itself%3A%20%3CA%20href%3D%22https%3A%2F%2Fjkp-ads.com%2FArticles%2Ffixlinks2udf.asp%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fjkp-ads.com%2FArticles%2Ffixlinks2udf.asp%3C%2FA%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I have enabled XLA Add-in as follows

  1. Go to Developer->Excel Add-ins
  2. Browse XLA Add-in, for example C:\data\MyAddin.xla
  3. It will show a check mark for MyAddin after completion

Now, I perform the following steps

  1. Launch Excel (Office 365 or MSO 2016)
  2. Use UDF available in my XLA Add-in, for example =UDFDemo(A1,A2)
  3. UDF gets evaluated
  4. Save and close the Excel Workbook
  5. Reopen the same Excel Workbook
  6. UDF shows #NAME! as the cell value with formula as ='\storage1\data\MyAddin.xla'!UDFDemo(A1,A2)

Next, I performed the following steps:

  1. Click on Data->Edit Links
  2. The dialog shows the location \storage1\data\MyAddin.xla for the source MyAddin
  3. Click on 'Change Source' and browse C:\data\MyAddin.xla
  4. The UDF gets reevaluated in the background, but the dialog still shows the location \storage1\data\MyAddin.xla for the source MyAddin
  5. Click on 'Close'
  6. As the UDF is reevaluated, save and close the Excel Workbook
  7. Reopen the same Excel Workbook
  8. UDF shows #NAME! as the cell value with formula as ='\storage1\data\MyAddin.xla'!UDFDemo(A1,A2)

I am unable to understand why Excel is looking for '\storage1\data\MyAddin.xla' when I had enabled 'C:\data\MyAddin.xla' from Developer->Excel Add-in

I am also unable to understand why Excel is not changing the location of the source when I use "Change Source' in Data->Edit links

If I perform the above steps on a different machine, the UDF is getting evaluated without any #NAME! error.

1 Reply
Highlighted
As this is a problem I've fought with a lot, I created a solution which you can implement in the add-in itself: https://jkp-ads.com/Articles/fixlinks2udf.asp