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

SlavkaKnoema commented 3 years ago

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

Create the new file.
Install Knoema Data Finder add-in
Insert custom function KNOEMA.VERSION()
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 commented 3 years ago

@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 commented 3 years ago

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 commented 3 years ago

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

ElizabethFerriss commented 3 years ago

Is there a time estimate for Bug 5349227?

grangeryy commented 3 years ago

@ElizabethFerriss the bug is still under investigation.

AlexPhilippov commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

@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

install this add-in (Developer -> Excel Add-Ins ... Browse and select attached xlam-file)
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)
Create any simple graph chart
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 commented 2 years ago

@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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

@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 commented 2 years ago

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

cidev5 commented 2 years ago

@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 commented 2 years ago

@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 `

donotmatch.xll XLL

    </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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

@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 commented 2 years ago

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.

cidev2 commented 2 years ago

@grangeryy is there an update on fixing the issue? We have clients that are running into the issue.

Also, the workaround you provided in your last post will not work for our use case. We do not have control over the XLL add-ins that our clients install and moreover it will be quite a project to re-implement the custom functions in XLL that we have already built using the JavaScript API.

grangeryy commented 2 years ago

@cidev2 and @cidev5, really sorry to know the work around doesn't help a lot. We notice this issue is more easier to hit when mix-use custom function add-in and VBA add-in. We've got the severity of the impact but it will take time to completely address the root cause behind.

cidev5 commented 2 years ago

@grangeryy Checking in to see what the status of this is. In general these "_xldudf" issues are occurring very frequently - not just in the case described above but in general, without being prefixed with the path to another plugin.

This leads me to ask if we are not doing something correctly in our add-in code. Can anyone on your team give insight into what this means/why it happens, and what we can do to prevent it?

It occurs so frequently yet I see very little in the way of similar issues being reported.

Please let us know what can be done about this. It has become a real pain for our users.

cidev5 commented 2 years ago

@grangeryy, @jiju-MS Is there any update? Our clients continue to experience this issue.

Can an engineer let us know if there are any other workarounds we can employ to prevent this?

grangeryy commented 2 years ago

@cidev5, do you think we can have a call on this? Currently, we don't have an elegant solution yet. And it may need some investigation into your case to see if other work around exists. THX!

Wenjun-Gong commented 1 year ago

Hi @cidev5 , we have been looking into this issue. We will update the thread once we have update on this.

donlvMSFT commented 1 year ago

Hi @SlavkaKnoema , @cidev5 , @omardoma , @AlexPhilippov ,

We have a fix related to this _xldudf_ issue in rolling out process. It should be available for Current Channel in 1 week, and if you have Office version >15831.20122, could you do some validation and let us know if it's resolved?

Thanks, Dongqi

donlvMSFT commented 1 year ago

Hi @SlavkaKnoema , @cidev5 , @omardoma , @AlexPhilippov ,

The change I mentioned above already been released to CC, could you have whether your scenarios been unblocked?

Thanks, Dongqi

cidev5 commented 1 year ago

Hi @donlvMSFT,

We just got off a client call where we confirmed the client has Excel updated to or above the version you specified previously and we were still able to produce the issue.

The steps taken to cause the issue are simply for the sender of the file that contains our custom functions to have these add-ins enabled, and for the receiver of the file to not have those add-ins enabled. Once he enables the add-ins and reopens the file the issue goes away. No other workaround works in this case.

We believe the add-in or add-ins in question are:

analysis-toolpak

Please advise.

Best, Dawid

4tti commented 1 year ago

See https://github.com/OfficeDev/office-js/issues/2568

donlvMSFT commented 1 year ago

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 commented 1 year ago

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

akshay70 commented 1 year ago

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 commented 10 months ago

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 commented 10 months ago

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 commented 10 months ago

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 commented 9 months ago

@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 commented 9 months ago

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 commented 9 months ago

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 commented 9 months ago

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 commented 9 months ago

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

Some questions going forward:

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

sk1136 commented 9 months ago

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 commented 9 months ago

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 commented 8 months ago

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 commented 8 months ago

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

Using Office 16.0.17230.42306:

Opened an existing spreadsheet with the corrupted web addin formula (=BloombergUI.xla!_xldudf_...)
Reloaded the web addin
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 commented 8 months ago

Hi @akshay70 ,

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

akshay70 commented 8 months ago

@donlvMSFT

A few questions:

To clarify, is the fix release just for stopping formulas from being corrupted in the future?
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 commented 8 months ago

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 commented 8 months ago

@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 commented 8 months ago

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

OfficeDev / office-js