pkabirrvelixo's avatar
pkabirrvelixo
Iron Contributor
Nov 01, 2022
Status:
New

Support permanent Application.screenUpdating setter instead of suspendScreenUpdatingUntilNextSync

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)

No CommentsBe the first to comment