Excel Web Addin functions are recalculating on every function call

[akshay70]

Summary

Excel Web Addin functions are recalculating constantly without any change to parameters. Any call to a web addin function, or when the sheet is saved, causes all web addin functions across all sheets to trigger. The problem seems to start after opening the attached spreadsheet.

TriggeringWorksheet.xlsx

Why is the attached spreadsheet causing this problem, and how can we prevent functions from being corrupted into triggering constantly?

Environment

• Platform: PC Desktop
• Host: Excel
• Office version number: Version 2311
• Operating System: Windows 10
• Excel Web Addin using Javascript custom functions
• Excel is in Automatic Formula Calculations mode (hard requirement by users)

Expected behavior

The web addin function should be called only when we make some change to its cell/parameters.

Current behavior

The web addin function is spammed 100/sec with the same parameters. Everytime an addin function is called, it triggers every other addin function.

Steps to reproduce

1. Create a basic Excel Web Addin function -> with no parameters, just returns the current time. Example below.

   /**
   * @customfunction GET_TIME
   */
   export async function get_time(): Promise<string> {
   return new Date().toLocaleString();
   }

2. Open the attached workbook, and paste the GET_TIME() function there in two different cells
3. Run one cell and notice how both cells recalculate.

Context

Users are using our Web Addin across 10-15 excel files. Some of the workbooks (like the one attached), are triggering something that causes all addin functions going forward to always trigger, even though no parameters have changed. This causes Excel to freeze or tremendously slow down, and makes the Web Addin a major blocker for users workflow.

This issue persists even after closing the trigger excel workbook. This issue does not affect any native excel functions or any COM Addin functions.

[sk1136]

@penglongzhaochina @rundongmsft This potential bug / issue is also causing business disruption for us. The issue is the entire excel becomes slow and says calculating because of unnecessary custom function refreshes when it should be pulled only once. Think we have one custom function to load an array of data and it gets triggered unnecessarily because other sheets are open and have other COM addins. None of the other COM and VBA addins are affected by this. Only the new web addin.

What can we do to resolve this? Please escalate.

[sk1136]

Hi @Wenjun-Gong @penglongzhaochina @rundongmsft @akshay70 Possible to comment on and acknowledge please. We may want to move to COM or C# addin if this is an issue that can't be resolved or identified. Happy to hop on a call to explain the issue if there is any such medium. Thank you for your help.

[penglongzhaochina]

Hi @rundongmsft , Could you please take a look?

[akshay70]

Thanks @penglongzhaochina, @rundongmsft. Any updates?

This is a huge issue for us - the bug is causing large slow downs and freezing for our users. The addin is unusable when they have multiple excel sheets open.

[rundongmsft]

Hi @akshay70, are the functions volatile function or streaming function in your add-in? I notice that the case "get_time" you provided is a volatile function. If so, "Excel supports the concept of a volatile function, that is, one whose value cannot be assumed to be the same from one moment to the next even if none of its arguments (if it takes any) has changed. Excel reevaluates cells that contain volatile functions, together with all dependents, every time that it recalculates. For this reason, too much reliance on volatile functions can make recalculation times slow. Use them sparingly."

[akshay70]

Hi @rundongmsft, thanks for the response.

The functions in our addin are not volatile and don't show any volatile behaviour to start. Only after opening the triggering worksheet, it seems like all functions get corrupted to have volatile behavior.

Also, for the get_time example. why do you refer to this as volatile? Custom functions require @volatile in their definition to be volatile, right? Nonetheless, you can change it to return "hello" instead of datetime and see the same issue happen.

Could you try this on your side to see the same issue happening?

[rundongmsft]

Hi @akshay70, I noticed that the VBA setting "Workbook.ForceFullCalculation" of the workbook TriggeringWorksheet.xlsx was true. Please set it to false (you can edit it in VBA), save the workbook, reopen excel and see if the issue goes away.

[akshay70]

@rundongmsft, thank you for discovering this!! ForceFullRecalculation is the culprit. It causes large slowdowns when combined with web addin formulas, especially if the formulas return large data matrices. We will work to disable it where we can.

A few questions going forward:

1. Looks like COM addins have protection against this volatility. They do not retrigger when ForceFullRecalculation is on. Any idea how?
2. Is there a way for us or your team to adjust the code to protect web addin functions from rapid recalculation, especially if no parameters have changed?

Thanks again for the help on this! Discovering this issue is huge for us.

[sk1136]

hi @rundongmsft @penglongzhaochina Any update on this? Our vendor COM addins do not seem to have this issue (ex: Bloomberg and Visible Alpha COM addins). The issue is only with excel web addins. How do we remediate? Please advise.

[sk1136]

Separately users aren't aware of manually setting ForceFullRecalculation = True. How would this come to be? @rundongmsft @penglongzhaochina

[rundongmsft]

Hi @sk1136, do you mean you met this issue without setting "ForceFullRecalculation = True"? If so, that would be another issue, how to repro it?

[rundongmsft]

Hi @akshay70, for the question 2, js addin cannot control ForceFullRecalculation, but it is false by default. Only VBA code can control this setting. In js addin, one recommended method to avoid rapid recalc is to set the calculation mode to manually. You can set it from Excel Ribbon or js API, and use js API Range\worksheet\workbook.calculate() to control what you want to recalculate. For the question 1, you can try VBA method Application.CalculateFull to forces a full calculation of the data in all open workbooks.

[sk1136]

Hi @rundongmsft yes we didn't manually set the ForceFullRecalculation = True. Not sure how this setting was set.

[rundongmsft]

Hi @sk1136, in addition to actively setting it from VBA, this setting would also involved by a workbook persisted with it. You can check it in VBA editor (this doc tells how to enable it in Excel). If it's false, that means you met another issue with similar behavior, please open a new Github issue item.

[akshay70]

Thanks @rundongmsft.

When there is volatile behavior (IE: if ForceFullCalculation is True, using =TODAY() formula, etc.) I noticed that COM Addin functions don't face any performance issue. Looks like these functions have underlying logic to prevent recalculation and re-rendering if the function parameters didn't actually change.

Are we able to implement similar logic to prevent unnecessary recalculation in Web Addins?

This would be a huge boon to performance issues in the Web Addin. Also, note that automatic calculation is a necessity in our users' as their use cases involve cell inputs changing quite frequently.

[rundongmsft]

Hi @akshay70, in fact the COM Addin functions are also triggered recalculation in this condition. They are calculated too fast to be noticed. However, for JS Addins, since Excel need to wait for the result from js side, it needs more waiting time, and a large number of CF recalculations will cause perf issue. We understand the predicament your users are going through. It's hard for them to realize if the opened workbook persisted with ForceFullRecalculation. We think "open a workbook persisted with ForceFullRecalculation = True will affect application-level recalculations" would be bug. We have created an internal bug item 8650511 to track it. Please let your users to manually set ForceFullRecalculation=false as a workaround when they encounter this issue.

[akshay70]

@rundongmsft, thanks for creating the ticket!

I'm wondering, is there something on the Web Addin code that can be enhanced to help these types of performance issues in general?

For example:

1. Web addins trigger a "Busy" text before rendering - the rendering of "Busy" and then the data seems to cause slowness, especially for tables of data. Can this process be sped up?
2. Can there be logic built into the addin to check if parameters have changed since the last call within x seconds, and not re-render if so?
3. Can there be an extra attribute when defining a custom function or a return value passed from the custom function, such that with that attribute or return value, a re-render is not triggered.

Basically, are there enhancements the web addin team can make to have a more efficient/smarter recalculation or re-rendering of cells?

[akshay70]

Hi @rundongmsft, thanks for your help - following up:

1. Was there any update on the internal ticket 8650511?
2. Any updates on my previous message regarding how we can mitigate the web addin performance issues?