Excel VBA Application.Calculate does not work but Ctrl+Alt+F9 does

Question

I have an unusual problem with Excel 2013. I have a VBA script to pull data from SQL and generate HTML files then import those HTML files into tabs followed by a recalculate call for formulas that are dependent on the data generated prior.

Here is the basic structure:

Sub RefreshData()

Task1 ' run 1st query and generate HTML file
Task2 ' run 2nd query and generate HTML file
.
.
.
Taskn ' run nth query and generate HTML file
RefreshDataInTabs ' refresh all tabs with generated HTML files
Calculate_AllOpenWorkbooks ' calculate all formulas that depend on data obtained in Tasks above

End Sub

Here is the call to calculate...

Sub Calculate_AllOpenWorkBooks()

Application.Calculate

End Sub.

So everything works as expected until call to Calculate_AllOpenWorkBooks. Cells with formulas don't update. "Automatic" Workbook Calculation is selected in the Calculation options windows in Excel Options. Manual Data -> Refresh All doesn't work BUT Ctrl+Alt+F9 does!

Answer 1

After many attempts to solve the problem I ended up using

SendKeys "{F9}"

There is a 10 ms delay, but it works and you can use it in code.

You can even do SendKeys "^%{F9}" for Ctrl+Alt+F9, if you want.

Answer 2

Sub Calculate_AllOpenWorkBooks()

Application.CalculateFull

End Sub

and if you need the application to wait:

Sub Calculate_AllOpenWorkBooks()

Application.CalculateFull

If Not Application.CalculationState = xlDone Then
DoEvents
Else: End If

End Sub