question

LB-2714 avatar image
0 Votes"
LB-2714 asked AlexJerabek edited

Does Application.calculate apply on all workbooks?

The current description of Application.calculate indicates this:
"Recalculate all currently opened workbooks in Excel".

But on my tests (through an addin and in ScriptLab) it does only recalculate the active workbook, which seems coherent with the current status of APIs not allowing to "play" with other workbooks (there's no WorkbookCollection class, for instance).

Is this an error on the description?
Or should I expect recalculation on workbooks other than the active one?

PS: on this StackOverflow post from 5 years ago, one of yours specified this which would confirm my thoughts:
"It shouldn't trigger this across workbooks however, only the workbook held by this application instance."


office-js-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AlexJerabek avatar image
0 Votes"
AlexJerabek answered AlexJerabek edited

Hi @LB-2714,

Thanks for raising this issue. I believe Application.calculate actually does recalculate every workbook when using Excel on Windows. I just tested with this Script Lab snippet (ignore the title – I used an existing sample as a base): https://gist.github.com/AlexJerabek/a69fd14c0ca052d42a8aefecb54c0171

Here’s the repro steps:

  1. Open the snippet in two workbooks in Excel for Windows.

  2. Press “Setup” in both.

  3. Press “Toggle calculation mode” in both (ensure they’re both “manual”).

  4. Press “Refresh data” in both. Note the First column with SUM formulas isn’t updating.

  5. Press “Recalc” in one. Note that both workbooks update.


The single workbook behavior does appear to hold true for Excel on the web. I'll update the documentation to reflect this difference soon.

--Alex

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.