Excel calculation only in the Active Workbook, looking for a workaround

Question

If you have several Excel files open, and your VBA / VSTO code calls a function Calculateor enables automatic calculation, Excel will painfully recount all open workbooks , not just the active workbook.

This is a well-known and well-known problem that has existed for many years, but Microsoft does not seem to be interested in fixing it.

Calculate only the active book before saving

Microsoft Excel Wish List: Workbook Level Calculation

It's funny, both in VBA and in VSTO, Microsoft gives us the opportunity:

• recount concrete worksheeet
• count all open books

... but there is no way to simply recount one specific Workbook.

The financial company I'm working on is a huge problem. Our actuaries have large, bulky Excel files full of formulas, and when they click Calculate on one workbook or we perform a calculation before saving the file, they have to wait a few minutes so that all other open Excel files can also be calculated.

There are two ways.

You can run some VBA as follows:

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

.. but this is not guaranteed. Your "Sheet1" may contain formulas that point to cells in "Sheet2", but other cells in "Sheet2" may have dependencies on "Sheet1". Thus, calculating each worksheet once may not be sufficient to complete the calculation in your workbook.

Excel ( ALT Excel). Excel cut'n'pasting, :

"" Excel

, : - ?

Active Excel.

, VBA VSTO, " ", , . . "Workbook.ReadOnly" , .

, , Worksheet_Calculate, , VBA , , ...? , , .

, ...

( Lotus 1-2-3)?

Answer 1

Excel, . SO, .

, / !

• Excel
• , Excel.

script:

• , , () . - "" .

- .

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

, , ( ..).

, . , .

Answer 2

, FastExcel, WorkSheet.EnableCalculation false True .

, , : .

, FastExcel Download 15- FastExcel V3 build 231.655.789.380

FastExcel " " ;

: , FastExcel. FastExcel, , - FastExcel V3 Calc