Support permanent Application.screenUpdating setter instead of suspendScreenUpdatingUntilNextSync

Support permanent Application.screenUpdating setter instead of suspendScreenUpdatingUntilNextSync
8

Upvotes

Upvote

 Oct 31 2022
1 Comments (1 New)
New

This piece of documentation states:

 

Don't call suspendScreenUpdatingUntilNextSync repeatedly (such as in a loop). Repeated calls will cause the Excel window to flicker.

At the same time, Excel Online has a payload size limitation that causes context.sync() to fail if it has accumulated too big a changeset against the current workbook.

 

Same applies to Application.suspendApiCalculationUntilNextSync().

 

This produces a dilemma: to avoid the payload size limit, we'll have to call context.sync() intermittently when processing a large dataset, however doing so will result in unnecessary recalculations and screen updates (flicker).

 

There is a way to avoid flicker and unnecessary recalculations when processing a large dataset.

Can we have something similar to on-off methods like in VBA?

try {
    context.application.screenUpdating = false;
    context.application.calculationSuspended = false;

    ...
} finally {
    context.application.screenUpdating = true;
    context.application.calculationSuspended = true;
}

Or alternatively:

    // Will only revert as soon as Excel.run() finishes.
    // -
    context.application.suspendScreenUpdatingUntilExcelRunFinishes();
    context.application.suspendApiCalculationUntilExcelRunFinishes();

We have submitted this on Office.js forum previously: There is no way to avoid recalculations / updates with Application.suspend...UntilNextSync when trying to avoid payload size limit · Issue #2734 · OfficeDev/office-js (github.com)

Comments
Occasional Visitor

This would be really helpful for us as well, as we open a Blob into a new worksheet and then find the used range, then copy the relevant portion into the active worksheet as sort of an 'insert-in-place' operation.  This requires several context.sync() calls to load properties on the new worksheet we just inserted.  But, once we copy the contents from it, we close that worksheet.  I'd love the user to never even know that we inserted it.

 

I can understand Excel not wanting an add-in to shut down screen updating without some checks on that.  What if it was something where we could disableScreenUpdating until the Excel.run() block finishes?  Then we could sync as much as we needed, but when we were done with all of our updates and we release control to Excel again, it could update once.