Saving time on calculate

[Steven]

I have a situation where the calculation takes too long and so I put this in
the ThisWorkbook module "SheetChange" event:

ActiveWorkbook.ActiveSheet.Calculate

This makes the calculation much faster and I am confident everything is ok
becuase all that needed to be calculated was the current sheet but the status
bar now reads...:
"Ready Calculate"
:.... becuase there was not a full calculation done.

Are there any good methods of how to handle this. It is more of a
communications issue to myself of the status of the calculation in the
overall excel system. [Note: I do not want to always be doing a full
calculate becuase of the time it takes but, as an additional note, on the
BeforePrint event I do have a full calculate becuase I cannot take the chance
that something is not fully calculated when printed.]

Thank you for your help,

Steven

 

[Paul C]

Unless you put this message in yourself, I would asssume that you are in
Manual calculation mode. In this mode you will get this message after
changes.

I would guess that either the routine you are runnin does something to the
sheet that is recognizes as a change, or the recalcualtion done via VBA is
not picked up by the workbook.

A couple of suggestions to try,

make sure your calculate statement is at the very end
In addition to the caluclate statement toggle the calculation on/off like this
Application.Calculation = xlCalculationAutomatic
Application.calcualtion = xlCalculationManual

In testing you code you can use the Application.CalcualtionState property to
help determine what the system is showing for calcualtion status.

 

[Charles Williams]

When you do an activesheet.calculate any formulae in other sheets that refer
to the activesheet do not get updated (as well as volatile formulae on the
other sheets), and the cells on the activesheet that were calculated will
also be recalculated at the next calculate.

If an F9 recalculation takes much longer than the ActiveSheet.calculate
(Shift-F9) then either you have a lot of volatile formulae on the other
sheets or the other sheets DO depend on the Activesheet.

If you want to be reminded about the status of the calculation you could put
a message in the status bar after the Sheet.Calculate
Application.StatusBar="Sheet '" & activesheet.name & "' Calculated"

and then Trap F9 using Onkey to do an Application.Calculate and
Application.Statusbar=False to reset the message.


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com