Calculate Now F9 Unavailable after macro run

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

:




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

 

[Guest]

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

Bill,

Thanks once again! None of the F9 variations works. I wish I could grab the
person who wrote the macro - it wasn't me! But I can't see anything wrong
with it. I have tried writing it differently as well, but to no avail. The
cell equations the macro uses are a bit long - maybe that's the problem.

Ray

 

[Guest]

:




Bill,

Thanks once again! None of the F9 variations works. I wish I could grab the
person who wrote the macro - it wasn't me! But I can't see anything wrong
with it. I have tried writing it differently as well, but to no avail. The
cell equations the macro uses are a bit long - maybe that's the problem.

Ray

--------------------

What you plainly need is Chip Pearson! If he doesn't jump in here today, you
might try addressing a message to him. I can't think of anything else for you
to try short of someone sitting down to look over your shoulder while things go
awry.

Good luck...

Bill

 

[Guest]

--------------------

What you plainly need is Chip Pearson! If he doesn't jump in here today, you
might try addressing a message to him. I can't think of anything else for you
to try short of someone sitting down to look over your shoulder while things go
awry.

Good luck...

Bill

Bill,

I've found the answer! It's a little obscure but it was the length of the
cell formuale. Formula cells are limited to 1024 characters. The formula that
the macro uses from the spreadsheet had 1008 characters in row 12 but this
had gone up by 40 to 1048 when it reached row 1000. This happened because the
formula refers to cells from the same row numbers (so 2 more digits per
reference). Thus some of my formulae had exceeded the character limit without
me realising. Solved by simplifying the formulae. Not sure why F9
disappeared. The warnings on this problem are not clear. Perhaps someone
from MS might like to take a look at this problem. Anyway I got there
eventually. Thanks for your help.

Ray Hooper