CODEWITHSUNDEEP
excelvbaexcel-formula
abpc1
abpc1

Reputation: 63

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

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!

Upvotes: 3

Views: 9819

Answers (3)

Malan Kriel
Malan Kriel

Reputation: 401

I've had similar issues where on some client's machines Application.Calculate just doesn't refresh the results (very randomly) in a loop. I've taken ideas from above and from Rory to come up with the below approach.

Function calculateUntilCalculationStateIsDone() As Boolean
    ' A very paranoid calculation routine to avoid Excel bugs as in
    ' https://stackoverflow.com/questions/44378969/excel-vba-application-calculate-does-not-work-but-ctrlaltf9-does
    ' Calculate until the calculation state is xlDone
    ' First try Application.Calculate 10 times
    ' Then try Application.CalculateFull 10 times
    ' Then try Application.CalculateFullRebuild 10 times
    ' If not done, set calculation options to automatic, and do events until done.
    '   Give up after 1 minute to avoid an infinite loop
    ' Set the calculation option back to what it was before after loop
    '
    ' Returns:
    '   True if calculation was successful
    '   False if calculation was unsuccessful
    
    calculateUntilCalculationStateIsDone = True ' calculation is done unless proven otherwise
    
    Dim i As Long
    For i = 1 To 10
        If Application.CalculationState = xlDone Then Exit Function
        Application.Calculate
    Next i
    
    For i = 1 To 10
        If Application.CalculationState = xlDone Then Exit Function
        Application.calculateFull
    Next i
    
    For i = 1 To 10
        If Application.CalculationState = xlDone Then Exit Function
        Application.CalculateFullRebuild
    Next i
    
    Dim currentCalculationOption As Variant
    currentCalculationOption = Application.Calculation
    ' store current calculation option
    
    Dim Time As Date
    Time = Now()
    
    Application.Calculation = xlCalculationAutomatic
    ' Set to automatic recalculation
    
    Do Until Application.CalculationState = xlDone
        ' Wait for Excel to finish calculating
        DoEvents
        
        If Now() - Time > TimeValue("0:01:00") Then
            ' give up after 1 minute of trying to avoid infinite loop
            Application.Calculation = currentCalculationOption
            calculateUntilCalculationStateIsDone = False
            Exit Function
        End If
    Loop
    
    ' Set the calculation option back to what it was before
    Application.Calculation = currentCalculationOption
    
End Function

I've tested the runtime on a loop that took 1m55s, this increased it to 1m57s, which was not a big loss in performance (the impact would depend on the complexity of the underlying Excel calculations).

I made it a function that returns whether the calculation was successful or not, so the caller sub / function can add a warning / raise an error if not as appropriate for your application.

Upvotes: 0

Titus Buckworth
Titus Buckworth

Reputation: 412

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

Upvotes: 3

antonsachs
antonsachs

Reputation: 55

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.

Upvotes: 3

Related Questions