Only Calculating the Active Workbook

When you have more than one workbook open Excel has always recalculated all of the workbooks at each recalculation. That can be very painful if one or more of the workbooks are slow to calculate.

Now the Excel Dev Team has made a significant change to this process. When in Automatic calculation mode only the active workbook and any workbooks that it links to/from will be recalculated.

This change is currently only available to Insiders in the Beta channel running Windows version 2208 build 15529 or later and Mac version 16.64 build 22081401.

Testing Active Workbook Calc

I ran some tests using Insider Excel build 16224.20000.

Workbook 1 has a volatile formula =NOW() in D1 that shows when the formula was last calculated.

Workbook 2 has a formula in A2 =A1+1 and a volatile formula =NOW() in cell C1.

Both workbooks are visible in their separate windows.

With Workbook 2 active I changed cell A1 several times over the course of a few minutes and checked that the time shown in cell C1 changed – it did. But the time shown in Workbook 1 cell D1 did not change. And when I switched the active window to Workbook 1 then cell D1 did not change: just changing the active workbook does not trigger a recalc.

Next I added a FastExcel UDF CalcSeqCountRef(C1) to book 2 and CalcSeqCountRef(D1) to book 1. CalcSeqCountRef counts the number of times the referenced cell has been recalculated.

Now the time shown in book 1 always remains in sync with book2. This is because VBA UDFs are considered as workbook links to the workbook (in this case an XLAM addin) that contains the VBA code, so Excel considered Book1 and Book2 to be interlinked.
(By the way – UDFs in an XLL addin as opposed to an XLAM addin are not considered as links)

Exceptions to Active Workbook Calc

Active Workbook Only calc will not happen if:

• The workbook uses real-time streaming.
• Excel is in Manual Calc mode.
• You press F9 or Calculate Now.
• Your VBA triggers a recalculation.

Comparison with FastExcel Active Workbook Mode

For my FastExcel product I chose to focus more on Manual Calc mode, because when you have large slow workbooks you will tend to be using Manual.

FastExcel allows you to set Active Workbook Mode so that Excel will only calculate the active workbook.
When you set Active Workbook Mode the default is that each open workbook is in Manual calculation mode, so that you have to press F9 to calculate the Active Workbook but not any other workbooks.
(For more complicated situations FastExcel allows you to set different calculation modes for each open workbook.)

Conclusion

The beauty of the new Excel Active Workbook Calc is that it is automatic – you don’t have to do anything and it works well.
But the downside is that it does not work in Manual mode, which is arguably when you need it most!