Calculate only the active book before saving

Question

I tried my level to find the answer to this question, and I was not lucky that I am posting my first stackoverflow question here.

Question: Can excel only calculate the active book before saving while other books are open.

Scenario: I have a large book with several report sheets with formulas. The main VBA control creates several books with multiple report sheets based on several variables. All report workbooks are converted to values ​​when created.

Problem: It takes about 20 seconds to calculate the general ledger. Thus, when VBA tries to save and close the report workbook, it calculates both the report and the general ledger, which creates performance problems.

Waiting for your decisions.

Thank you in advance for your time.

Manoj.

Answer 1

There is no built-in method for calculating an active workbook (see https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10601079-workbook-level-calculation at my suggestion to the Excel team about this )

You can turn off calculation for individual sheets with Worksheet.Enablecalculation and you can stop Excel calculation while saving by disabling Application.CalculateBeforeSave, but both of them can potentially cause unwanted side effects.