Search code examples
excelvba

VBA - Application.CalculationState always xlPending

I'm trying to ensure that all calculations are performed in the workbook, I had situations were formulas were not calculated when opening the excel file due to slowness on computer. This is the code I have right now:

Public LOCAL_PARAMETERS_WORKBOOK As Workbook
Public LOCAL_PARAMETERS_WORKSHEET As Worksheet

application.ScreenUpdating = False
application.DisplayAlerts = False
application.EnableEvents = True
application.Calculation = xlCalculationAutomatic

Set LOCAL_PARAMETERS_WORKBOOK = Workbooks.Open(StrPathFile, True, True)
Set LOCAL_PARAMETERS_WORKSHEET = LOCAL_PARAMETERS_WORKBOOK.Sheets("Business Process Data Flow")

LOCAL_PARAMETERS_WORKBOOK.Worksheets("DynamicPath").Calculate
If application.CalculationState <> xlDone Then CalculateFormulas

LOCAL_PARAMETERS_WORKSHEET.Calculate
If application.CalculationState <> xlDone Then CalculateFormulas

This is one part of a function, it's required to calculate first Sheet "Dynamic Path" and only then we can calculate Sheet "Business Process Data Flow" since the second is dependent of the first. From what I checked if I do application.Calculate this will force calculations in all open workbooks to be done or retype application.Calculation = xlCalculationAutomatic then application.CalculationState will be xlDone.

But if I do worksheets("").Calculate the application.CalculationState is going to stay always xlPending, CalculateFormulas is a simple function with a counter and checks if application.CalculationState = xlDone and if not then it's going to perform application.Wait (Now + TimeValue("00:00:01")).

Is there an explanation why if I just do Calculate at sheet level the application.CalculationState doesn't change, and is there another solution that could be implemented here to help fix my code?

Solution

  • There is a bug with the Application.CalculationState when it shows xlPending even after calculation is complete. It manifests particularly when you have volatile functions in the workbook.

    You can go around by using a class I created a few years ago. You can find it here. If you copy code then make sure you don't copy the attribute lines (1 to 9). Otherwise I suggest you download the repo ZIP and import the ExcelAppState.cls file.

    Once you have the class, you can use it like this:

    Option Explicit

Sub Test()
    Dim app As New ExcelAppState
    
    'Do stuff
    '...
    
    app.WaitForCalculations maxMilliSecondsToWait:=10000 '10 sec - or whatever timoeout you need
End Sub