Calculate Now F9 Unavailable after macro run

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

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 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

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

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

> 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
>

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

Ray Hooper wrote:
>
> "Bill Martin -- (Remove NOSPAM from addre" wrote:
>
>
>>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
>>
>
>
> 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

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

> Ray Hooper wrote:
> >
> > "Bill Martin -- (Remove NOSPAM from addre" wrote:
> >
> >
> >>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
> >>
> >
> >
> > 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
>

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