30

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

WendiKidd
  • 4,333
  • 4
  • 33
  • 50
Sebastian Müller
  • 5,471
  • 13
  • 51
  • 79

3 Answers3

38

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.

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • 9
    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. – user65 Nov 11 '15 at 22:05
  • same here... I still see #REF, #N/A indicating that calculations were not completed although it exited the block... – AtaSagun Dec 09 '20 at 14:24
  • @user65 Any alternative solution? I am having the same issue. – user781700 Jan 28 '21 at 17:24
  • Siddharth, any idea when that one DoEvents is required? Any idea why only one at most should suffice? – stenci Aug 09 '21 at 22:13
  • @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. – Siddharth Rout Aug 10 '21 at 03:08
  • @SiddharthRout Issuing one in a loop is a key here. Excel calculates asynchronously anyway even when you don't "pass the control". The DoEvents here is for you, not for Excel, so that you can wait without blocking the Excel UI. – GSerg Jun 06 '23 at 21:18
1

Accepted answer did not worked for me because "DoEvents will not block until calculate completes and is effectively a no-op in your example code" I had the same issue. Finally this solution worked for me.

Do
    DoEvents
    Application.Calculate
Loop While Not Application.CalculationState = xlDone

In Manual calculation mode sometimes while loop goes into long running process. That is why added below line.

Application.Calculate
user781700
  • 844
  • 3
  • 15
  • 27
  • 1
    How would starting calculation over again on each iteration help speed up the loop's completion? – GSerg Jun 06 '23 at 21:19
0

Can't believe no-one has posted this solution, which would appear to be pretty much the definitive one ...

Do While Not Application.CalculationState = xlDone
    If Application.CalculationState = xlPending Then Application.Calculate
    Application.Wait (Now + TimeValue("00:00:02"))
Loop

The Microsoft docu says that application.wait allows re-calculation to proceed in the background.

Anthony Youngman
  • 1