CODEWITHSUNDEEP
exceloffice-js
Evan Nicoles
Evan Nicoles

Reputation: 51

Toggle Calculation Mode in Excel with Add-In

I'm working with the Office JS API to create an Add-In for Excel.

I'm applying a block of data to a table (8784 x 5) with several referencing formulas. Applying the data via range.values = values is pretty slow (~15 seconds) while automatic calculations are turned on. Applying data with auto calcs turned off is significantly faster (<3 seconds). Is there a way to manipulate the calculation mode via the Office JS API? I know that I can see the read-only value of the mode, but I don't see any way to adjust it.

I'd like to turn off auto calcs, apply data, turn back on auto calcs.

Upvotes: 2

Views: 171

Answers (2)

HAMID REZA KOOSHESH
HAMID REZA KOOSHESH

Reputation: 1

First, you'd turn off automatic calculations, then you'd apply your data. After that, you'd turn automatic calculations back on. This way, you're essentially controlling when Excel recalculates your formulas, which can significantly speed up the process when dealing with large data sets.

So, while you can't directly switch the calculation mode using the Office JS API, you can use this workaround to accomplish what you need.

Upvotes: 0

Sudhi Ramamurthy
Sudhi Ramamurthy

Reputation: 2478

Setting calculation mode is in our backlog and is an important feature that we need to add (no timeline yet); however as you noted for now it is read-only. In addition we are currently working on range, worksheet level calculate functions, which may help with workbooks that are already in manual mode.

For larger tables with formulas, there are few pitfalls you can avoid as described here.

Upvotes: 3

Related Questions