I am developing an Excel add-in with Office.js and need to manage recalculation while keeping Excel’s calculation mode set to Automatic. Here’s the issue:
Context:
- The add-in uses custom formulas to fetch data from a database.
- Users have around 10,000 custom formulas in a single sheet, which can cause delays when opening the workbook due to automatic recalculation, even if the data hasn’t changed.
Problems:
- Inconsistent Recalculation: The workbook sometimes recalculates on open, causing delays. We need to control this behavior.
- Data Consistency: Data should only change when manually refreshed. Automatic changes need to be prevented.
- Impact on Other Formulas: Setting calculation mode to Manual affects other formulas. We need a solution that keeps the mode on Automatic.
Requirement:
Disable or override native refresh options (e.g., “Calculate Now,” “Refresh All,” F9) so that recalculation only occurs with a custom refresh button in the add-in.
What We Have Tried:
Setting calculationMode to Manual affects other formulas. No Office.js API seems to directly disable native Excel commands.
Questions:
- How can we intercept or disable native refresh functions using Office.js?
- Are there any APIs or workarounds to achieve this without changing the calculation mode to Manual?
- How can we ensure data consistency without affecting other formulas?
Any help or examples would be appreciated!
Setting calculationMode to Manual affects other formulas. No Office.js API seems to directly disable native Excel commands.