title: VBA: To calculate the worksheet in background, while message userform is active
body: <p>I wrote a loop procedure in vba, which pastes a lot of data each time to the new worksheet. Actually it pastes the formulas that get calculated. It takes excel approximately 40 seconds to get all calculations done. Unfortunately the next loop does not wait 40 seconds and fills with the data the next sheet. It takes a lot of resources. To make a loop to wait a little bit, but still to perform calculations I used userform, which appears for 40 seconds, than it will be unloaded and is used in the next loop. The purpose of the userform is to allow the calculations to get done before the calculations in the next sheet begin. This strategy has a drawback, since when the useform is active, the calculations in the background freeze. One can change the options of the useform to showmodal false, but then I cannot upload and unload it each time as new loop begins. Besides I am not sure weather the formulas a still calculated in the inactive sheet. I have to mention that the formulas in the sheet each time activate an excel add-in and I cannot make an add in to wait with calculations, even if the option manual calculations is turned on.       </p>
  
  <p>If anybody knows how to postpone the loop but not to make complete excel application wait (Application.wait or ontime methods do not work) I will appreciate the help a lot. I tried also to active and deactivate events, switch to manual calculation and back. Also this does not help and all data a calculated at once. </p>
  
  <p>The method described in the other post did not worked for me and all formulas are still calculated at the same time: </p>
  
  <pre><code>Application.Calculate
   If Not Application.CalculationState = xlDone Then
   DoEvents
   End If
  </code></pre>
  
  <p>My original code that copies formulas to the new worksheet:</p>
  
  <pre><code>For raw_i = 1 To 3
  
  Set o = GetObject("d:\formulas.xlsx")
  Set Sheet_i = Worksheets.Add(after:=Worksheets(Worksheets.Count))
  Sheet_i.Cells(1, 1).Formula = o.Worksheets("Sheet1").Cells(raw_i, 1).Formula
  Set o = Nothing
  LoadForm    
  next raw_i 
  </code></pre>