I have a (large) worksheet with many interdependencies. Due to the heavy processing demand I've had to take a few measures:
WorkSheet.Calculate is non blocking.
I have taken the usual approach of a wait function after doing a sheet.calculate :
Unfortunately this never leaves the loop: it seems that as long as any cell is still considered "dirty" the CalculationState remains 2 (pending) so this doesn't work for sheet by sheet (and I understand for large books the management of clean/dirty is suboptimal). I have tried the alternative:
but this doesn't seem to be waiting.
Is there a way for a large workbook to do sheet based calculation in a blocking call / with a wait phase?
- automatic calculation -> manual
- do not recalculate on save
- a control panel of buttons to selectively recalculate different sheets to allow minimum recalculation depending on which part of the workbooks we want to update
WorkSheet.Calculate is non blocking.
I have taken the usual approach of a wait function after doing a sheet.calculate :
VBA Code:
Private Sub waitforcalc()
Do Until Application.CalculationState = xlDone
DoEvents 'let events happen, like calculation
Loop
End Sub
Unfortunately this never leaves the loop: it seems that as long as any cell is still considered "dirty" the CalculationState remains 2 (pending) so this doesn't work for sheet by sheet (and I understand for large books the management of clean/dirty is suboptimal). I have tried the alternative:
VBA Code:
Private Sub waitforcalc()
Do While Application.CalculationState = xlCalculating
DoEvents 'let events happen, like calculation
Loop
End Sub
but this doesn't seem to be waiting.
Is there a way for a large workbook to do sheet based calculation in a blocking call / with a wait phase?