Testing Calculation State

[Jabba1963]

Hi,

So... I am trying to run some code to effect changes to the spreadsheet with Calculation State set to Manual, then switch it back to Automatic whilst retaining control still within the Macro - allow all the updates to take place and then putout a Msgbox "Done".

The code is something like this...

Sub Domystuff()

   Application.Calculation = xlManual
'
'     lots of code here that effects lots of changes to the spreadsheet...
'
   Application.Calculation = xlAutomatic

   Application.Wait(Now +#0:00:03#)     ' Pause briefly to enable Calculation to get started

   WaitforCalculationToFinish

End Sub

Private Sub WaitForCalculationToFinish()

    Const delayInSeconds = 15
   
    Dim startTime As Double
   
    startTime = Timer

    Do While Timer < startTime + delayInSeconds And Application.CalculationState <> xlDone
        DoEvents
    End If

    MsgBox "Done"

End Sub

My query is regarding the WaitForCalculationToFinish routine and the Do While Loop...

As it currently stands - will this work or am I missing something such that I could loop endlessly ?

Am I achieving my objective of retaining control of the macro until all updates are complete ?

Any assistance gratefully received
Jabba

 

[NARAYANK991]

Hi Jabba ,

Just a small error : replace an End If by Loop :

Do While Timer < startTime + delayInSeconds And Application.CalculationState <> xlDone
DoEvents
Loop

Narayan

 

[Jabba1963]

Hi Narayan...

Sadly a complete typ-O on my part - and thankfully you spotted it... so I am now assuming the code is good to test - it was the concept of DoEvents / waiting for Calculation to finish / to then msgbox "Done" I was really getting at - so thankfully you spotted the typ-O anyway - phew

Thanks