Application.CalculationState to wait until formulas are done calculating

Did you know you can effectively pause your macro until all the formulas on your worksheet are done recalculating? Just check the Application.CalculationState property to wait until the formulas on your sheet are done calculating before resuming your VBA macro.

The first macro was submitted by wellsrPRO power user, Giancarlo, and the second macro is an alternative you can consider.

Reader’s Note: This article is part of a series featuring macros submitted by my incredible wellsrPRO community members. These articles are similar to my Code Library articles in that they usually won’t contain an accompanying detailed tutorial.

If you’re an existing wellsrPRO member, don’t forget to submit your own macro to the wellsrPRO community using the Share My Macros button.

wellsrPRO users can automatically import this community submission directly into their spreadsheet. Just look for “Community Submissions” in the Auto-Import dropdown menu.

Application.CalculationState DoEvents Example

Created by Giancarlo

Sub WaitUntilFinished()
    'DEVELOPER: Submitted by wellsrPRO community member "Giancarlo"
    'Check to see if formulas are still calculating before resuming
Application.Calculate 'Optional - recalculates all formulas
If Not Application.CalculationState = xlDone Then
    DoEvents
End If
'~~> Rest of your code goes here
End Sub

In this example, Giancarlo forces your instance of Excel to recalculate all the formulas embedded in your worksheets. After that, he checks to see if the calculations are still going by looking at the CalculationState property. If it is, he uses the DoEvents method to yield the macro until the system processes other events.

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

Most people report that this solution works for them. However, a few people say it doesn’t solve their problems. If you’re in the minority of users that don’t have any luck with this solution, try wrapping the CalculationState check in a loop, like this:

Application.CalculationState DoEvents Example with Loop

Sub WaitUntilFinishedLoop()
'Loop until all your calculations are done
Application.Calculate 'Optional - recalculates all formulas
Do Until Application.CalculationState = xlDone
    DoEvents
Loop
'~~> Rest of your code goes here
End Sub

In this example, once the calculation is complete and the CalculationState equals xlDone, the macro continues.

The Application.Calculate line near the top of both of these macros is optional. All that does is force your spreadsheet to recalculate all formulas. If that’s not necessary in your example, and chance are it’s not, you can remove that line.

That’s all for today! Please share this article with your friends on Facebook and Twitter.

If you’re ready to free up your time in the office, subscribe using the form below.