Excel calculation just on Active Workbook, looking for a workaround

Question

When you have more than one Excel file open, and your VBA/VSTO code calls the Calculate function, or turns on Automatic Calculation, Excel will painfully recalculate all open Workbooks, not just the active Workbook.

This is a well-known and well-reported problem, which has been around for years, but Microsoft doesn't seem interested in fixing it.

Calculate only the active workbook before saving

Microsoft Excel wishlist: Workbook level calculation

Ridiculously, in both VBA and VSTO, Microsoft gives us the ability to:

• recalculate a particular Worksheeet
• recalculate all open Workbooks

...but there's no option to just recalculate one particular Workbook.

In the financial company I work for, this is a huge issue. Our actuaries have big, bulky Excel files, full of formulae, and when they click on Calculate on one Workbook or we perform a calculate before saving a file, they then have to wait several minutes for all other open Excel files to also get calculated.

There are two ways around this.

You could run some VBA like this:

Application.Calculation = xlManual 
For Each sh In ActiveWorkbook.Worksheets 
    sh.Calculate 
Next sh 

..but this isn't guaranteed to work. Your "Sheet1" might contain formulae pointing to cells in "Sheet2", but other cells in "Sheet2" might have dependencies back on "Sheet1". So, calculating each Worksheet once might not be enough to perform a full calculation on your Workbook.

Alternatively, you could open each Excel file in a separate instance (by holding down ALT as you open the Excel icon). But then, you lose the full Excel cut'n'pasting functionality, as described here:

Can't fully cut'n'paste between Excel instances

So, my question is... has anyone found a workaround for this issue ?

I just want to recalculate the cells in the Active Excel Workbook.

I wondered if I could add some VBA or VSTO which sets all non-Active Workbooks to "read-only" before I kick off a Calculation on the Active Workbook, thus preventing other Workbooks from being able to be recalculated. But this isn't possible. The "Workbook.ReadOnly" can only be read, not programmatically set.

Or perhaps adding a handler to the Worksheet_Calculate event, which checks if that VBA code which is being run belongs to the Active Workbook, and if not, it aborts attempting to calculate...? But this event actually gets kicked off after that Worksheet has been calculated, so it's too late.

Our company can't be the only one suffering from this issue...

Any advice (other than upgrading to Lotus 1-2-3) ?

Answer 1

This method uses another instance of Excel to avoid multiple workbook calculations. A few lines of the code for using a new instance were taken from this SO question, which deals with a similar topic and may be of interest to you.

You will have to test this for speed in your specific case, since the closing/opening time might not out-weigh the avoided calculations!

Macro steps

• Set calculation to manual
• Save and exit the desired workbook
• Open it in a new instance of Excel
• Recalculate
• Save, close and re-open in original instance of Excel.

Key point for running this script:

• The macro cannot live within the workbook to be recalculated, since it gets closed (twice) during the process. It should be placed in some other "utility" workbook.

Code - see comments for details

Sub CalculateWorkbook(WB As Workbook)
    ' Store path of given workbook for opening and closing
    Dim filepath As String
    filepath = WB.FullName
    ' Turn off calculation before saving
    Dim currentCalcBeforeSave As Boolean
    currentCalcBeforeSave = Application.CalculateBeforeSave
    Application.CalculateBeforeSave = False
    ' Store current calculation mode / screen update and then set it to manual
    Dim currentCalcMode As Integer, currentScreenUpdate As Integer
    currentCalcMode = Application.Calculation
    currentScreenUpdate = Application.ScreenUpdating
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    ' Close and save the given workbook
    WB.Close savechanges:=True
    ' Open a new INSTANCE of Excel - meaning seperate calculation calls
    Dim newExcel As Excel.Application
    Set newExcel = CreateObject("Excel.Application")
    ' Could make it visible so that any problems don't leave it hidden in the background
    ' newExcel.Visible = False
    ' Set the calculation mode to manual in the new instance sothat the workbook isn't calculated on opening.
    ' This can't be done without an existing workbook object.
    Dim tempWB As Workbook
    Set tempWB = newExcel.Workbooks.Add
    newExcel.Calculation = xlCalculationManual
    newExcel.CalculateBeforeSave = False
    ' Open the workbook in the new instance of Excel
    Dim newWB As Workbook
    Set newWB = newExcel.Workbooks.Open(filepath)
    ' Calculate workbook once
    newExcel.Calculate
    ' Close and save the workbook, tempworkbook and quit new instance
    newWB.Close savechanges:=True
    tempWB.Close savechanges:=False
    newExcel.Quit
    ' Re-open in the active instance of Excel
    Application.Workbooks.Open filepath
    ' Reset the application parameters
    Application.CalculateBeforeSave = currentCalcBeforeSave
    Application.ScreenUpdating = currentScreenUpdate
    Application.Calculation = currentCalcMode
End Sub

Call the above sub by passing it the workbook object you wish to recalculate (this could be done from a button etc).

This has been tested on a very simple example workbook, and the concept works. However, please test on a copy of your workbook first, since it has not been fully robustness tested, and has no error handling.