Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
"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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
"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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
Help with macro looping and color query function | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) |