Calculate only the active workbook before saving

问题:

I have tried my level best to find an answer for this question and with no luck I am here posting my first question on stackoverflow.

Question: Can excel calculate only the active workbook before saving while other workbooks are open.

Scenario: I have a large workbook with multiple sheets of reports with formulas. The main control VBA creates multiple workbooks with multiple sheets of reports based on several variables. All report workbooks are converted to values on creation.

Problem: it takes around 20 seconds to calculate the main workbook. Thus when the VBA is trying to save and close the report workbook it calculates both the report and the main workbook which is creating performance issues.

Looking forward to your solutions.

Thanks in advance for your time.

Manoj.

回答1:

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

You can switch off calculation for individual worksheets using Worksheet.Enablecalculation and you can stop Excel from calculating on save by switching off Application.CalculateBeforeSave, but both of these have potentially undesirable side effects.