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

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.

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.

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