Excel Add-in #NAME error, it appends FUNCRES.XLAM'! in start of formula

Question

I have an excel add-in containing custom functions built using JavaScript. Sometimes when user opens a sheet containing these custom functions, excel does not correctly show formulas. It appends "FUNCRES.XLAM'!xldudf" or "='C:\Program Files (x86)\Microsoft Office\root\Office16\LIBRARY\Analysis\FUNCRES.XLAM'!xldudf" in start of custom function, resulting in #NAME error. If I have a custom function named "Test", then all existing formula are shown as "=FUNCRES.XLAM'!_xldudf_Test" or sometimes as "='C:\Program Files (x86)\Microsoft Office\root\Office16\LIBRARY\Analysis\FUNCRES.XLAM'!_xldudf_Test".

Also if I add custom function to a new cell, it is recognized as correct formula and returns correct result. But if I save this sheet and re-open it then newly added formula is also shown as "#NAME" Error as described above.

Sometimes this error is resolved by restarting excel or computer, but I want to know the reason for this error and how to avoid this.

Answer 1

Edit

The FUNCRES.XLAM add-in at that location looks like a language translator for analysis functions. Excel probably uses it to resolve function names (hence "Func Res") for various languages back to their US English originals.

Is it possible that your UDF has a name conflict with a function listed in that add-in? You can check:

• Open FUNCRES.XLAM (double-click it in Windows explorer)
• Open the VBA editor
• Select its ThisWorkbook page in the Project list
• In the Properties window, set the IsAddin property to False (that makes its workbook visible)

Check the page for whatever language you're using. See if you can find any name conflict. The possibility of a name conflict is a wild guess on my part, but this might provide a clue. Nothing else comes to mind. And my original post seems way off, but I won't delete it until we've resolved this.

Original

This is perfectly normal behavior. Your add-in is named FUNCRES.XLAM and its full path is C:\Program Files (x86)\Microsoft Office\root\Office16\LIBRARY\Analysis\FUNCRES.XLAM. When the add-in is not loaded in Excel, the filename or full path is attached to the function name in a cell, as you noted. If the add-in is loaded, Excel displays just the function name xldudf.

The solution is simple, just load the add-in. You have a couple options:

1. If you want it loaded every time you open Excel, follow these steps:

File / Options / Add-ins / Manage: Excel Add-ins / Go

Then select the add-in if it's in the list, or browse for it.

2. If you want to load it only for a particular session, just go to the folder and double-click the add-in. It will remain loaded until you close Excel. You'll have to repeat that every time you want the add-in loaded.

Note that the #NAME error means that Excel cannot resolve some text in the statement, in this case it's the name of the function.