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 spreadsheet with just one click.
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.
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.
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!