Wait until Application.Calculate has finished

Question

Can I force my vba script to wait until Application.Calculate has finished to recalculate all the formulas?

Solution

Further to my comments, you can use DoEvents with the Application.CalculationState. See this example

Application.Calculate
If Not Application.CalculationState = xlDone Then
    DoEvents
End If
'~~> Rest of the code.

If you want you can also use a Do While Loop to check for Application.CalculationState

I would also recommend see this link

Topic: Application.CalculationState Property

Link: http://msdn.microsoft.com/en-us/library/bb220901%28v=office.12%29.aspx

Quote From the Above Link

Returns an XlCalculationState constant that indicates the calculation state of the application, for any calculations that are being performed in Microsoft Excel. Read-only.

Comments

[Siddharth Rout]

Add DoEvents after Application.Calculate

[user65]

I relied on this example code as it is commonly provided on the internet but it does not work. DoEvents will not block until calculate completes and is effectively a no-op in your example code.

[AtaSagun]

same here... I still see #REF, #N/A indicating that calculations were not completed although it exited the block...

[user781700]

@user65 Any alternative solution? I am having the same issue.

[stenci]

Siddharth, any idea when that one DoEvents is required? Any idea why only one at most should suffice?

[Siddharth Rout]

@stenci: DoEvents is required when you want to pass the control to the operating system so that it can finish processing the events in its queue. Usually one is enough but you may issue more than one DoEvents in a loop to constantly pass the control back to ensure that all events in the queue are processed. For example between a copy and paste, one DoEvents will suffice. Where as in the above example, you may have to issue the same thing in a Do While loop.