Muokkaa

Jaa

Manage calculation mode in Excel

  • Artikkeli

This sample shows how to use the calculation mode and calculate methods in Excel using Office Scripts. You can try the script on any Excel file.

Scenario

Workbooks with large numbers of formulas can take a while to recalculate. Rather than letting Excel control when calculations happen, you can manage them as part of your script. This will help with performance in certain scenarios.

The sample script sets the calculation mode to manual. This means that the workbook will only recalculate formulas when the script tells it to (or you manually calculate through the UI). The script then displays the current calculation mode and fully recalculates the entire workbook.

Sample code: Control calculation mode

function main(workbook: ExcelScript.Workbook) {
    // Set the calculation mode to manual.
    workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.manual);
    // Get and log the calculation mode.
    const calcMode = workbook.getApplication().getCalculationMode();    
    console.log(calcMode);
    // Manually calculate the file.
    workbook.getApplication().calculate(ExcelScript.CalculationType.full);
}

Training video: Manage calculation mode

Watch Sudhi Ramamurthy walk through this sample on YouTube.