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 |
#7
|
|||
|
|||
"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 |
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) |