2

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.

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.

The method described in the other post did not worked for me and all formulas are still calculated at the same time: 

Application.Calculate
 If Not Application.CalculationState = xlDone Then
 DoEvents
 End If

My original code that copies formulas to the new worksheet:

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
Community
  • 1
  • 1
In777
  • 171
  • 1
  • 4
  • 15
  • Possible duplicate of [Wait until Application.Calculate has finished](http://stackoverflow.com/questions/11277034/wait-until-application-calculate-has-finished). You will find your answer in that question. – Sifu Dec 04 '15 at 13:00
  • @Sifu doevents did not worked in my case. – In777 Dec 04 '15 at 13:05

1 Answers1

0

VBA doesn't support multiple process threads - there is one execution route and if you pause it, then you pause the whole execution. It's all or nothing.

Best hope you've got is a While Loop:

While Application.CalculationState <> xlDone
    DoEvents
Wend

Which will loop until calculations have finished.

In response to your most recent edit:

Try changing the main loop to:

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
    Sheet_i.Calculate
    DoEvents
    Set o = Nothing
Next
SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • Did you even read the comments before posting that? He said `DoEvents` did not work for him... The duplicate linked also is almost this exact solution. – Sifu Dec 04 '15 at 13:57
  • That code uses an `If` block which will execute _once_ - it won't do anything to "pause" execution if the calculation state isn't complete. That's why you need a loop... there is a difference. – SierraOscar Dec 04 '15 at 14:00
  • From [microsoft](https://support.microsoft.com/en-us/kb/118468) `Once DoEvents relinquishes control to the operating system, it is not possible to determine when Microsoft Excel will resume the control. After the operating system obtains control of the processor, it will process all pending events that are currently in the message queue (such as mouse clicks and keystrokes).` That means that once `DoEvents` is called, it sends back control once it finished **everything**. So the loop is useless. – Sifu Dec 04 '15 at 14:08
  • Do I have to put this loop inside our outside the main loop? My main for-loop pastes formulas and data that get calculated. – In777 Dec 04 '15 at 14:12
  • I've put it into my main loop and the macro crashed. – In777 Dec 04 '15 at 14:15
  • @Sifu DoEvents takes control _**from**_ the Application _**to**_ the operating system, and in that same article you've linked to it states that it is useful for loop delays - which is exactly what this is. Regardless, my answer clearly states that this is the "Best hope" because VBA can only execute on a single thread. You can't postpone execution while another event process completes in this way. – SierraOscar Dec 04 '15 at 14:15
  • @In777 I don't know what your "main" loop is doing so I can't advise - can you post the code in your original question please? – SierraOscar Dec 04 '15 at 14:18
  • I have added my code, as I've said the loop does not work (starts but never ends) if I put `While Application.CalculationState <> xlDone` instead of the userform. – In777 Dec 04 '15 at 14:29
  • @Macro Man No, it does not wait until the next calculation starts. – In777 Dec 04 '15 at 14:44
  • I honestly don't think you will get this resolved with your existing code - especially as your formulas are calling an another add-in there is far too much going on at once. Honestly, I'd go back and look at the design of your code and find a simpler, effective way of doing what you're doing. Maybe post your code on the [CodeReview](http://codereview.stackexchange.com/) site and see what you get there. – SierraOscar Dec 04 '15 at 14:49