Collection of #NAME errors in Excel cells with Excel-Custom-Functions

[merkelfleet]

My message is neither a bug nor a feature. It is intended to be a collection of possible causes for the #NAME error to help us and other developers track down the error.

Our company created an Excel add-in based on VBA. As Microsoft's development with TypeScript, Windows 365, Office 365, and much more is moving towards online-based applications, we decided a while ago to build a new version of the add-in based on Office-js. So the journey begun.

One of our main issues is the so called #NAME error. The Excel cell just show us #NAME? and a short hint to the reason. But the reason is a general one and not specific.

I want to start this discussion to collect reaons and solution regarding this error based on the usage of office-js and the Excel-Custom-Functions. If I have described something incorrectly, please correct me. I am very grateful for any helpful hints.

If you have any other problems where the error is displayed as a result, please feel free to add them. I am of course grateful for any suggestions for solutions.

1. Cached and outdated taskpane

Context

The user added the add-in to Excel and did not use it for a while. In the meantime, updates were made on the provider's side. However, the add-in is still using an old version. However, the backend of the add-in expects a different request.

Solution

• force a reload of the add-in by righ click on it and click reload

2. More than one installed versions of the add-in

Context

The add-in has been installed several times. Once as a shared resource by the company administrator and once by the user themselves.

Solution

• remove the self installed version

3. Two add-ins are using the same function

Context

The user uses two different add-ins that have the same function. Excel tries to split the functions between the add-ins and adds the name of the add-in as a prefix to the function. Sometimes this does not work well - the cell does not know from which add-in it receives its data.

Solution

Option 1

• split the usage of the add-ins into different files

Option 2

• remove one of the add-ins

4. The add-in has not been fully loaded.

Context

Excel is showing “Error installing functions“ in the bottom status bar. The result in the cell is the “#NAME?” error.

Solution

Option 1 (source from General Electric FAQ)

To load Add-In, follow the steps given below:

1. Select Error installing functions/Error loading Add-ins from the footer.
2. Select One or more Addin Failed to load. See that your Addins”-Office Addin pop-up appears.
3. Select Refresh from Office Add-Ins pop-up and close the pop-up.

Option 2

• force a reload of the add-in by righ click on it and click reload

5. A some files are missing while the add-in is loading

Context

If the taskpane (add-in view) is loading but functions aren’t it could be that excel can’t access the functions.json file.
It's based on this Github issue OfficeDev/Excel-Custom-Functions#136 (comment)

Solution

• check with the inspection console, if the files is loading during a reload

6. Not supported Excel version

Context

The add-in is not loading (blank page in taskpane) and / or the cell shows #NAME?.

Solution

The used Excel version don’t support all requirements used by our add-in.
To interact with the Excel cell, we have to use custom functions.
This is supported with:

• Office on the web
• Office on Mac
• Office on Windows
  • Microsoft 365 subscription
  • Office 2016 and later (retail perpetual)
  • Office 2021 (volume-licensed perpetual)

It’s not supported with:

• Office on Windows
  • Office 2019 (volume-licensed perpetual)
  • Office 2016 (volume-licensed perpetual)
• Office on iPad (Microsoft 365 subscription)

[asegal-singlepane]

Related to #5 above we have been struggling with a similar issue, but ours is random/intermittent.

Our taskpane consistently loads and functions without issue, but we periodically and randomly have issues with functions registering/loading and silently failing. We don't even get the "some of your add-ins functions failed to load" in the status bar - the functions just don't appear and we get the NAME error.

Here is an example flow:

1. Clear office add-in cache (from trust center) to ensure clean open
2. Open new workbook, load our add-in, functions load and all works fine.
3. Close Excel
4. Reopen Excel, open taskpane or attempt to type one of our functions from our namespace and the functions do not appear and taskpane opens fine, but automatically generated sheets that use our functions NAME error.
5. From this broken state, we can either open a new workbook, or close excel and reopen again and then we'll have working add-in and function registration.

It's as if the second load of the add-in is failing to register functions, but subsequent loads then succeed.

In attempts to fix this and chasing various modifications to our Office.onReady calls we now are seeing the behavior above whereas in previous instances once the add-in function registration "broke" it was never able to be recovered without an add-in cache clear.

In searching github and other forums we haven't seen this exact behavior but we all seem to be dancing around the same issues of caching/functions.json failures or other silent reasons for failure of functions to register.