Issues on loading add-in affect loading custom functions of another add-in

[SlavkaKnoema]

Our customers are having trouble when using our Knoema Data Finder add-in with another add-in from Excel, f. e. ‘Euro Currency tools’.

Your Environment

PC, Excel, Microsoft 365 Version 2107(Build 14228.20250) 64 bit, Windows 10

Steps to reproduce

1. Create the new file.
2. Install Knoema Data Finder add-in
3. Insert custom function KNOEMA.VERSION()
4. Go to Insert -> My Add-ins -> Manage Other Add-ins -> Add-ins -> Go -> Select Euro Currency tools

After the above steps, we have the error message “One or more add-ins failed to install custom functions” and our Add-in becomes unloaded and custom functions of our add-in in the file have a strange naming: instead of “KNOEMA.VERSION()” we see “_xldudf_KNOEMA_VERSION()”. And when we execute the functions we have “#NAME?” error in a cell.
Note not all Excel add-ins from the list cause error, Also our customers use theirs own same add-ins some of them also cause an error. And after reopen the file all works fine.

Useful logs

OfficeAddins.log.txt

[xiaochunms]

@SlavkaKnoema Sorry for the inconvenience due to the issue. Looks like I can repro the issue at my side too. Will take further investigation and get back to you soon.

[xiaochunms]

Looks like the inserting "Euro Currency tools" triggers the re-registration of Custom Functions. Bug 5349227 was filed to track the issue. @SlavkaKnoema as a workaround please insert "Euro Currency tools" first then insert Custom Functions add-ins. Sorry for the inconvenience.

[ElizabethFerriss]

Thanks for the workaround @xiaochunms! We will look into that. How can we track the status of Bug 5349227?

[ElizabethFerriss]

Is there a time estimate for Bug 5349227?

[grangeryy]

@ElizabethFerriss the bug is still under investigation.

[AlexPhilippov]

Just received new input on this. Every execution of VBA add-in (f.e. macros for styling chart) leads to the same error. We get “One or more add-ins failed to install custom functions” and custom functions are not registered again. It becomes more critical with this information because the execution of VBA add-ins is more frequent than add-in installation. And we can't advise users to install Custom Functions each time they use VBA add-in.
Do we still have no estimate on this issue? Can we raise a priority for this bug due to the latest input?

[xiaochunms]

Just received new input on this. Every execution of VBA add-in (f.e. macros for styling chart) leads to the same error. We get “One or more add-ins failed to install custom functions” and custom functions are not registered again. It becomes more critical with this information because the execution of VBA add-ins is more frequent than add-in installation. And we can't advise users to install Custom Functions each time they use VBA add-in.
Do we still have no estimate on this issue? Can we raise a priority for this bug due to the latest input?

@AlexPhilippov sorry to hear the issue when running VBA add-in. As I know the execution of VBA add-in will not trigger the unregister of custom functions. Based on the description, I am not sure if it shares the same root cause with VBA add-in installation. May you please help to have a repro step which would be much helpful to figure out the root cause. Thanks!

[AlexPhilippov]

@xiaochunms, I've tried to simplify the example, but for some reason, it's reproduced only on the original Add-In from our client:
CE_Macros_SB_Excel_Add-in.zip.
To reproduce you need to

1. install this add-in (Developer -> Excel Add-Ins ... Browse and select attached xlam-file)

2. Add macros Format_Chart from this add-in to Quick access toolbar (name would be something like ...CE_Macros_SB_Excel_Add-in!Format_Chart.Format_Chart)
   

3. Create any simple graph chart

4. Run macros from Quick access toobar and click any of the buttons.
   

After that you'll see message "One or more add-ins failed to install custom functions."

Do I need to submit a separate ticket for that?

[AlexPhilippov]

@xiaochunms, any update on this? This is causing a lot of trouble since users work with both types of add-ins on a daily basis.

[AlexPhilippov]

Updates on my comment regarding reproducing the same issue when calling VBA-addins. Apparently, you can add VBA-addin as a macro on the Quick Access toolbar and then disable its loading in the Excel Add-Ins dialog. Then if you restart Excel when you'll be calling macros from the Quick Access toolbar it will load VBA add-in and cause the same issue - office-js custom functions will fail to register.
This case is actually more frequent since a lot of VBA add-ins are disabled to load on Excel start due to performance reasons.
I reproduced this with empty VBA-macros function and office-js CONTOSO custom functions projects from docs. So it's not specific to our office-js Add-In or VBA-addin.
@xiaochunms, @grangeryy, can you add any information on time estimates? Any workarounds on office-js side? Can shared runtime for custom functions somehow affect the situation?

[grangeryy]

Hey @AlexPhilippov, sorry for the late response. This issue is still in the backlog to track with other items. We've figured out the issue happens when user triggers both vba functions and custom functions. The fix will take a while and unfortunately we don't have enough bandwidth to cover this in this quarter. The only work around is just like in the discussion before, which is to insert the vba functions before triggering custom functions.

[cidev5]

Hi, a number of our clients and internal users are experiencing a similar issue, and we (and they) are rather frustrated by this.

Our custom functions get prefixed with _xldudf and often with a path - in addition - to the other add-in (in our case our own older legacy add-in, a COM add-in - but also the Bloomberg add-in which is frequently used by our clients as well).

The workaround you propose doesn't seem applicable to our situation as all that's needed is for someone to have the other add-in installed for our workbooks to become "corrupted" essentially, and anyone else opening them will get this error.

We cannot instruct everyone to not use these other add-ins when working with our add-in so if you could please raise the priority on this issue.

[grangeryy]

@cidev5 and @AlexPhilippov , sorry for the issue. We are re-evaluating the impact and will share the update soon. Also adding the our engineer @jiju-MS to support.

[cidev5]

@grangeryy, do you happen to have an update yet?

[cidev5]

@grangeryy, @jiju-MS

We would like an update on this issue as our clients are continuing to experience it. And more reporting it by the day. It is very disruptive to user experience and people are having a negative impression of the MS Office product.

[AlexPhilippov]

@cidev5, I can share a sort of workaround that worked for us. We noticed that if the add-in has XLL-equivalent, the issue is not reproducible. So we added this code
<EquivalentAddins> <EquivalentAddin> <FileName>donotmatch.xll</FileName> <Type>XLL</Type> </EquivalentAddin> </EquivalentAddins>
The downside of this approach is that the format of custom functions is changed after that. And saved old versions won't be recognized resulting in #NAME error.
More on Equivalent add-ins here. Works only with Type XLL, not COM.

[omardoma]

We are facing the same issue on Excel for Windows (Edge WebView2). When you have a workbook containing our Office.js add-in’s custom functions, then you load a VBA Add-in and use it’s functionality, it breaks our add-in’s existing custom functions in cells and excel no longer recognizes them as custom functions, they show #NAME! and their formulas are changed to start with _xldudf_.

Restarting the workbook solves the issue, so the problem just exists from the moment you use the VBA add-in functionality till you close the workbook (same session). It is really affecting our users' productivity as they have to restart the workbooks everytime they use a VBA functionality.

Version Information:

Version 2102 (Build 13801.21092 Click-to-Run)
Semi-Annual Enterprise Channel
Edge WebView2 installed

[cidev5]

Hi @grangeryy, @jiju-MS.

It was stated that you would share an update soon. That was two months ago.

Can you share an update please?

[grangeryy]

@cidev5 @AlexPhilippov I'm really sorry for the issues you've encountered and my apology for the late response from us. In recent quarter we did more detailed investigation on original bug 5349227, and to fix the root cause behind is more complicated than fixing a bug (We need re-design the part of custom functions registration logic without breaking vba side). We are now under internal discussions and before we can finally address the issue, is the work around by using equivalent XLL (https://docs.microsoft.com/en-us/office/dev/add-ins/excel/make-custom-functions-compatible-with-xll-udf) can short term mitigate the impact?

[cidev5]

Hi @grangeryy, thank you for the update.

Regarding the equivalent XLL workaround, I don't understand how this would work for our case.

We try to make using our add-in as straightforward a process as possible. So we have our add-in that we instruct our clients to download via the Office Add-ins store.

It sounds like, but correct me if I'm wrong, that for this workaround we would need to instruct them to download and install the equivalent XLL add-in.

And the downsides of using this approach, as described above by user AlexPhilippov, make this not actually a viable solution, as it would require our clients to rewrite many templates they use with a new custom function format.

[donlvMSFT]

We have another fix on the deploy road, which should be after build version 16.0.16227.20150, which should fix the issues related.

[donlvMSFT]

Hi @AlexPhilippov , @cidev5 , @omardoma , could you try on the latest version >16.0.16227.20150 and help verify the fix?

[akshay70]

Hi @donlvMSFT - seeing this issue come up a lot more recently with formulas getting warped based on COM addins that are active. Any news on if something changed, or if a new fix is under way?

[sk1136]

Any update on this? We are having similar issues with excel web addin.

Javascript excel addin. when opening up an excel sheet with addin formulas already in it, the formulas get corrupted intermittently. How do we prevent formulas from being corrupted? The issue gets resolved if we manually reload the excel addin. This is via OneDrive and happens every time we reopen the excel model. This is a deal breaker for us to use the new web addin. Any potential solutions?

Proper formula name:

ABC.GET_PB_OPT_ORDER_ANALYSIS()

Corrupted name:

=BloombergUI.xla!_xldudf_ABC_GET_PB_OPT_ORDER_ANALYSIS()

Formulas auto correct to the proper name when the addin is reloaded. This can be either through readding the addin through the 'Insert' Tab and 'My Addins'. Or by clearing the excel cache and letting the addin reload itself on Excel restart.

[donlvMSFT]

Hi @sk1136 ,

I saw you mentioned the scenario is opening up an excel sheet with add-in formulas already in it. I'm thinking about how old the workbooks are. Could you try to create a new workbook on later versions and then re-open to see whether this issue still exist?

Also seems you have Bloomberg related VBA add-in, do you install/uninstall this add-in recently?

[sk1136]

hi @donlvMSFT
We tried with brand new sheet and we use one drive. we tried installing and uninstalling Bloomberg addin. we don't think it has anything to do with Bloomberg addin, it is picking up the first addin that's available.
We think it's opening the excel sheet and formulas in but the web addin takes a while to load in or not at all and the formula crashes / corrupts. once we reload the addin, formula auto corrects. It is adding prefix and adding underscores in place of dots/periods.

[4tti]

@donlvMSFT interesting that you have asked me in the related thread to create new github issue... although this one is open (for years), may I know why? Especially as there is another one....

[sk1136]

Hi @donlvMSFT thank you for reviewing this.
This is affecting every user in the firm. We use few 3rd party COM addins, looks like Web Addin is confliciting with it on opening the spreadsheet the first time until reload the addin happens. We love the web addin but this is a blocker for us if the issue persists. Any advise/suggestions on how to remediate this will be greatly appreciated.

[akshay70]

Hi @donlvMSFT, this is affecting our users badly. Please advise. We really need a fix and plan of action, or will have to switch off this technology.

Some details:

• Users have Our Addin loaded (Web Addin) and Bloomberg addin (COM addin)
• Users are consistently running Bloomberg and Our Addin formulas, and saving/sharing files between each other
• After some use, Our Addin formulas get linked to Bloomberg addin (shown in Excel > Data Tab > Edit Links)
• Once this happens, the linked formulas on Our Addin will corrupt to BloombergUI.xla!_xldudf_ 100% of the time on re-opening the excel file
• Rewriting the linked formulas breaks the link to Bloomberg Addin (which temporarily relieves the issue), but this link is added back after regular user use of the Bloomberg Addin and Our Addin within a few hours, and the formula is corrupted to BloombergUI again
• Users are on a mixture of Desktop-stored Excel and OneDrive-stored Excel - issue is very severe (happening daily) on OneDrive, and happening every 2-5 days on Desktop

Your help is appreciated.

[donlvMSFT]

Hi @4tti , @sk1136 , @akshay70 ,

Thanks for callout, we're continuedly looking into these \_xldudf\_ issue, as we mentioned before, this behavior reflect to several root causes and it's complicated causing much effort. Thanks for your patience.

Currently we identified another root cause and working on the improvement , hopefully the improvement would be on Insider for you validation next month. We'll keep update the progress here.

[akshay70]

Thanks for the update, @donlvMSFT. Good to hear one of the causes is being worked on.

Some questions going forward:

1. What's the expected time to a public release on Excel?
2. Is the team continuing the look for the other root causes as well?
3. What are some interim solutions we can do or provide our users while we wait?

[sk1136]

Hi @donlvMSFT,
Thank you for acknowledging and confirming the bug.
Will this issue be resolved soon? Were you able to identify root cause and have a solution. We will have to revert back to COM Addin if this isn't resolved soon. Please advise so we can plan and communicate with the the users.

[donlvMSFT]

Hi @akshay70 , @sk1136 ,

We're having a fix for this scenario on the way of rolling out. On schedule it should be released on CC PROD on end of Dec or early Jan. We will confirm the release schedule and update the build number here later.

Thanks.

[donlvMSFT]

Hi,

The fix already released to CC(preview) version, please check if you are on Insider and Office version >=16.0.17126.20064.
With this fix, you should install the add-in once again, and after that, reopen the file should not see .xla(m)!xldudf.
If you have the correct version, please help validate the behavior.

Thanks.

[akshay70]

Hi @donlvMSFT - just tried this and looks like it isn't working.

Using Office 16.0.17230.42306:

1. Opened an existing spreadsheet with the corrupted web addin formula (=BloombergUI.xla!_xldudf_...)
2. Reloaded the web addin
3. After reload, the formula stays corrupted. There is still a link on the formula to the COM addin.

Also, seems like a new related issue came up - if the file is accessed from OneDrive or from a place where the COM addin cannot be found, the formula switches to 'C:\blp\API\Office Tools\BloombergUI.xla'!_xldudf_..., and reloading the web addin does nothing to fix it.

[donlvMSFT]

Hi @akshay70 ,

The new version couldn't fix the existing corrupted formula, so you need to add some new functions.

[akshay70]

@donlvMSFT

A few questions:

1. To clarify, is the fix release just for stopping formulas from being corrupted in the future?
2. Could you explain more on the exact issue(s) that was causing the corruption? And what the fix was? So we can better replicate/test it.

[donlvMSFT]

Hi @akshay70 ,

Thanks for sharing thoughts, I could share some details of the root cause:

We identified that sometimes, the custom functions would be registered into the local .xla/.xlam as these file types are also considered as workbooks. In some cases, after save and re-open the document, there would trigger corruption or confliction and then failed.

This fix changed the way of custom function registration aiming to resolve this issue. So, I mentioned that the fix couldn't totally resolve the existing corruptions which are already persisted, but could stop further corruptions as you mentioned.

Let me know if you get further feedback.

Thanks.

[akshay70]

@donlvMSFT - Thank you for explaining! We have the insider version, though it's hard to verify since regular users are using the same files but are on Current Channel. Will look for the Current release. When is it scheduled to release?

[donlvMSFT]

Hi @akshay70 , the new version is already released to Current Channel as version id: 16.0.17126.20126. Please help confirm.

[akshay70]

@donlvMSFT - thank you! Seems like it's working so far! Will keep you posted if we see it come up on our end again.

[4tti]

Hey @donlvMSFT, @Wenjun-Gong, @grangeryy

Our customers are extremely angry about the situation around this. We told them like 3 times it is fixed and always it was incorrect information. This time I receieved requests from big customers to give them link to KBA related to this issue and the resolution, etc. GitHub won't do unfortunately.

Can you please share link to KBA related to this issue, or request new KBA creation to cover that and share it here?

[donlvMSFT]

@4tti, could you help share more information about their scenario? As I mentioned above, the fix couldn't totally resolve the existing corruptions which are already persisted, but could stop further corruptions as you mentioned.

I'm not familiar with the KBA, do you mean if there's any Post or Blog?

[4tti]

Hello @donlvMSFT

They have the scenarios discussed here, i.e. having com/vba add-ins corrupting the formulas.
As you know, there were similar github issues (with similar symptoms) and they were closed as fixed, that was then communicated but the issues reappered. Therefore now the customers asked if we can provide any link to Microsoft page like Knowledge Base Article where the issue is described with the resolution, etc.
Obviously they are extremely annoyed which is something I totally understand because these issues are extremely painful. Imagine you have some monthly report prepared and it works for weeks and then - just because it was opened by some user with COM/VBA Add-in - it got corrupted for you just the day you need to get up-to-date figures and provide it to your management or there is budgeting deadline....

Post/Blog would work too of course.

[donlvMSFT]

Hi @4tti ,

Sorry for the inconvenience, and our fixes needs time to roll out for customers for validation. Currently we don't have posts for this issue...

As explained above, there're descriptions about the root cause, fix solution with version Id, also the limitation. Could you share these with the customer first? Also could you help verify this?

Sometimes, the custom functions would be registered into the local .xla/.xlam as these file types are also considered as workbooks. In some cases, after save and re-open the document, there would trigger corruption or confliction and then failed.
This fix changed the way of custom function registration aiming to resolve this issue. So, I mentioned that the fix couldn't totally resolve the existing corruptions which are already persisted, but could stop further corruptions as you mentioned.

Thank you!