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.


Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.

I want to join the free wellsrPRO VBA Training program

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, Twitter, and Google+.

If you’re ready to free up your time in the office, visit my Excel Add-ins page for some great automation products.

Reach out to my VBA Consulting page if you have questions and I’ll be happy to help you out!


About The VBA Tutorials Blog

Ryan Wells

The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.