Make VB wait for excel to calculate a sheet before carrying on

[ZainE]

Hi

I'm using Excel 2007 and Windows XP Pro...

I'm trying to get VB to wait for excel to calculate a sheet before carrying on with the rest of the macro.

            Application.Calculation = xlCalculationManual
 
            Application.Calculate
            While Application.CalculationState <> xlDone
                'MsgBox Application.CalculationState
                DoEvents
            Wend

The above seems to work but I don't want to calculate the entire workbook (which is what

Application.Calculate

does). I would prefer to use something like

Sheets("Sheet1").Calculate

instead.

But when I do that, the Application.CalculationState doesn't work i.e. Application.CalculationState is always not equal to xldone.

Can you help?
Thanks

 

[drsarao]

I think if only one Sheet is recalculated, Application.CalculationState will not be xlDone.

Please see http://www.decisionmodels.com/calcsecretsf.htm

You can:
ReCalculate the whole workbook in the while loop. Or
Change calculation to auto at the end of the macro. (fastest)

 

[ZainE]

Thanks drsarao.

I think I am calculating the whole workbook (i.e. Application.Calculate) currently...

For your second option, are you suggesting something like the following?

            Application.Calculation = xlCalculationManual
            
            
Begin loop
 
           'Do some stuff

            Application.Calculate
            While Application.CalculationState <> xlDone
                 DoEvents
            Wend
 
            'Do some more stuff
End loop
 
Application.Calculation = xlCalculationAuto