VBA - Application.CalculationState always xlPending

[Joao Inacio]

Hello Everyone,

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?

Thank you very much!

 

[Joao Inacio]

Just for future people having the same issue, the solution I found was to use:

LOCAL_PARAMETERS_WORKSHEET.cells.SpecialCells(xlCellTypeFormulas).calculate

This forces the formulas to be calculated only on cells that have formulas, after this application.calculationstate is xlDone