CODEWITHSUNDEEP
vbaexceluserform
In777
In777

Reputation: 181

VBA: To calculate the worksheet in background, while message userform is active

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

Upvotes: 2

Views: 2928

Answers (1)

SierraOscar
SierraOscar

Reputation: 17647

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

Upvotes: 0

Related Questions