How can I calculate an entire workbook, but not all open workbooks?

Question

Is there a way to calculate entire workbook but not all open workbooks in VBA?

I know about

worksheet.Calculate

but it will only do 1 sheet. I also know about

Application.CalculateFull

But I think this recalculate all open workbooks at the same time.

Is it possible to do only 1 workbook?

Edit: I get the approach:

For Each wks In ActiveWorkbook.Worksheets
    wks.Calculate
Next

but this is very dangerous if sheets have links between them, then the order of calculation is important. I know I could find the exact sequence and apply it, problem is that on very large workbooks this can get somewhat tricky

Answer 1

Another option is to create a new instance of Excel, open the workbook is that instance and then calculate that. This will leave any workbooks in the original instance uncalculated, potentially saving you time if another workbook is very slow to calculated (100k+ formulas for instance).

Dim xlApp As Excel.Application
Dim wbTest As Workbook

Set xlApp = New Excel.Application
Set wbTest = xlApp.Workbooks.Open("c:\temp\test.xlsx")

' do a bunch of stuff in wbTest

xlApp.Calculate

Whether this is better depends on your circumstances. Opening a second instance of Excel involves a little extra time and probably more memory (although I haven't tested this and it may not be a significant amount).

You may have to change more code to take account of the fact this workbook is in a different instance and there may be issues with some functions (for example if you check if a workbook is already open by spooling through all the workbooks in the original instance of Excel).

Answer 2

After some investigation and testing, selecting all sheets first and calculating afterward works :

Application.Calculation = xlManual
ThisWorkbook.Sheets.Select
ActiveSheet.Calculate 

this calculate all selected sheet at the same time creating the right order of calculation

Answer 3

The answer by Steven G doesn't actually work, it just looks like it does.(I can't reply to that post as I don't have 50+ rep, which is annoying).

It still calculates the sheets in 1 directional order. I tested this by creating 5 sheets and having them all + 1 to another cell, on each sheet in different directions. Using the select sheets then calculate method will give the wrong result.

As it requires that the sheets are visible, elow is function I built to replace the calculate function using that method. (Requires dictionaries reference library). Do not use though.

    EXAMPLE, METHOD DOES NOT WORK
Function Calculate_Workbook(Optional Wb As Workbook)

'Application.calculate will calculate all open books, which can be very slow.
'Looping through all sheets in workbook to calculate can be risky due to formula referencing a cell thats not yet calculated, calculation order may be important.
Dim DicShtVisible As New Dictionary
DicShtVisible.CompareMode = TextCompare
Dim Asht As Worksheet, AWkbk As Workbook    'Previously selected books
Dim Sht As Worksheet

Set Asht = ActiveSheet
Set AWkbk = ActiveWorkbook

If Wb Is Nothing Then Set Wb = ThisWorkbook

Wb.Activate
'Unhide all sheets as can't select very hidden stuff
For Each Sht In Wb.Sheets
    DicShtVisible.Add Sht.Name, Sht.Visible
    Sht.Visible = xlSheetVisible
Next Sht

'Select all sheets and calculate the lot
Wb.Sheets.Select
ActiveSheet.Calculate

'Reapply visibility settings
Dim Key As Variant
For Each Key In DicShtVisible.Keys
    Wb.Sheets(Key).Visible = DicShtVisible.Item(Key)
Next Key

'Reset selections
AWkbk.Activate
Asht.Select

End Function

As far as I can see there is no solution, the only answer is to either know the calculation order of your workbook and manually calculate sheets in that order, or accept using Calculate and that it will calculate all open workbooks.

I would love to be proven wrong however. It's such an annoying and stupid issue.

Answer 4

None that I know about, Steven.

You actually lose time doing Application.Worksheets(1).Calculate

I ran a timing test: 3 workbooks open in the same instance of Excel.

One with 8200 volatile functions, 8000 in one worksheet on 4 non-contiguous ranges, 200 in other worksheets.

Two other workbooks with a combined total of 100 functions. The results:

• Application.Calculate - 4.41 ms
• ThisWorkbook.Worksheets(1).Calculate - 52.05 ms for each worksheet
• ThisWorkbook.Worksheets(1).Range("R1").Calculate (repeated 4 times) - 84.64 ms

It's counter-intuitive, but true.

You also lose time declaring Dim wks as Worksheet. Unless you do For ... Each referring to it as ThisWorkbook.Worksheets(1) - is faster

Also, be careful referring to ActiveWorkbook - when you code runs your primary workbook might not be active one. ThisWorkbook (the one with code) is safer.