Ray Hooper wrote:

"Bill Martin -- (Remove NOSPAM from addre" wrote:


Ray Hooper wrote:

The Calculate Now functions (F9 etc.) cease to operate after I run a specific
macro in a spreadsheet cost model. Other similar macros do not cause this to
happen. This not only stops the recalculation of values in worksheets when
inputs are changed but prevents the updating of charts. The model has to be
closed down and restarted. Work around is to operate a macro that turns the
automatic calc. on and then back to manual and to have one macro per chart to
update charts. This is cumbersome. There are no circular references and no
div0! cells. The macro causing the problem uses long formulae in a named cell
range - the other (working) macros use named cell ranges also. Would welcome
any suggestions.

Ray Hooper



After running your macro, have you tried to revive Excel by going to
Tools/Options/Calculation and making sure the "Automatic" button is still set?
Or change it to manual and then back to automatic again?

It sounds like the macro in question may have a statement near the beginning:

Application.Calculation = xlCalculationManual

to turn off calculation and speed things up. At the end it should have a
corresponding statement:

Application.Calculation = xlCalculationAutomatic

to restore the normal automatic mode.

Good luck...

Bill



Bill,

Thanks for your note. Because there are many linked spreadsheets and many
calculations the calculate mode is set to manual - this is why I need to have
F9 available. Setting the calculation mode to automatic after running the
macro has no effect - no calculation takes place until I close the model and
restart Excel.

Ray


That sounds strange, but I have one more thing for you to try. Walkenbach's VBA
book (pg. 45) says to try Ctrl+Alt+F9 if Excel is not recalculating properly --
it apparently also forces recalculation of things controlled by custom VBA
applications while F9 only forces formulas to recalc. I may be misinterpreting
this, but try the key stroke and see if it helps.

(And also grab the person who wrote the macro in question and ask him/her to
clean up their code!)

Bill