When to call refreshDynamicArrayFormulas()?

Aspose.Cells Product Family
1

Hi,

A question that seems simple.
When should we call Workbook#refreshDynamicArrayFormulas()?
And why does Workbook#calculateFormula() not invoke Workbook#refreshDynamicArrayFormulas() itself?

Thanks,
Taras

2

@TarasTielkes

You should call Workbook#refreshDynamicArrayFormulas() when you need to ensure that any dynamic array formulas in your workbook are updated with the latest data before performing calculations. This is particularly important if the data that the dynamic array formulas depend on has changed, as it ensures that the results reflect the most current information.

The reason Workbook#calculateFormula() does not automatically invoke Workbook#refreshDynamicArrayFormulas() is likely due to the design choice to separate the concerns of refreshing dynamic array formulas and calculating regular formulas. This allows for more control over when each operation is performed, enabling you to refresh dynamic array formulas only when necessary, rather than every time a formula calculation is triggered.

Here’s a brief example of how you might use these methods in your code:

// Load the workbook
Workbook workbook = new Workbook("path/to/your/file.xlsx");

// Set a dynamic array formula
workbook.Worksheets[0].Cells["A1"].SetDynamicArrayFormula("=SEQUENCE(10)");

// Refresh dynamic array formulas
workbook.RefreshDynamicArrayFormulas();

// Calculate all formulas in the workbook
workbook.CalculateFormula();

// Save the workbook
workbook.Save("path/to/your/output/file.xlsx");

In this example, the dynamic array formula is set, refreshed, and then all formulas are calculated, ensuring that the results are accurate and up-to-date.

3

@TarasTielkes,

Moreover, please note, when calling calculateFormula() method, the dynamic array formulas in the workbook would be calculated too, but Aspose.Cells does not change their applied range according to the change of referenced data. For example, the change of the data it references may cause the formula’s applied range change, so such kind of formulas need to be refreshed(spilled) before calculations. If you are not sure whether they need to be refreshed, it is always better and safe for you to call refreshDynamicArrayFormulas() explicitly to make sure you can get the expected results.

4

@TarasTielkes

And for the question:

It is mainly designed for performance consideration. A common scenario is that users first set formulas and dynamic array formulas, then calculate them or generate the resultant file. When setting the dynamic array formulas, they should have been spilled correctly so there is no need to refresh them again. And even for those formulas loaded from template files, sometimes users are sure that there is no need to refresh those formulas again. So we do not integrate this operation into Workbook.CalculateFormula() automatically. If user is not sure about that, user may just call RefreshDynamicArrayFormulas() before calling CalculateFormula() always.