Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 formula recalc problem
Hi everybody,
I have a problem making sure all the formulas in the spreadsheet recalculate, when recalc is called from within a macro. I have a complicated spreadsheet model that takes a second or two to recalculate. I run a macro that: 1. Sets up parameters 2. Forces recalc 3. Grabs the output 4. Cycles back to step 1 with different parameters, etc. My code looks something like this: For j = pageFirst To pageLast Step pageStep <set up parameters of the j-th scenario Application.CalculateFull <save the output, which is just a rectangular range in one of the tabs Next The problem is, sometimes Excel seems to grab the output before a complete recalc is done. So the formulas are only partially updated when the output is saved. This happens pretty randomly, but also frequently enough (once in a 1,000 loops or so). Why is this happening? Is there any way to make ABSOLUTELY sure ALL formulas have been recalc'd before proceeding to the output? Thanks a lot! Tom |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 formula recalc problem
Tom, could it be that you need to turn off the re-cal
(Application.Calculation = xlCalculationManual) until you are ready and then turn it back on (Application.Calculation = xlCalculationAutomatic) just before you re-calculate? -----Original Message----- Hi everybody, I have a problem making sure all the formulas in the spreadsheet recalculate, when recalc is called from within a macro. I have a complicated spreadsheet model that takes a second or two to recalculate. I run a macro that: 1. Sets up parameters 2. Forces recalc 3. Grabs the output 4. Cycles back to step 1 with different parameters, etc. My code looks something like this: For j = pageFirst To pageLast Step pageStep <set up parameters of the j-th scenario Application.CalculateFull <save the output, which is just a rectangular range in one of the tabs Next The problem is, sometimes Excel seems to grab the output before a complete recalc is done. So the formulas are only partially updated when the output is saved. This happens pretty randomly, but also frequently enough (once in a 1,000 loops or so). Why is this happening? Is there any way to make ABSOLUTELY sure ALL formulas have been recalc'd before proceeding to the output? Thanks a lot! Tom . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 formula recalc problem
Ken,
You're right: I do switch Excel to manual calc at the start of the macro. But then calling Application.Calculation = xlCalculationAutomatic would be identical to Application.CalculateFull Actually, from what I understand the latter is supposed to be a more powerful way to force recalc than the former. Again, it's not that my code doesn't work. It does work OK, but with an occasional hiccup, which to me means the problem is probably not in my code, but in the way Excel handles recalc. Thanks for your reply, though Tom "Ken" wrote in message ... Tom, could it be that you need to turn off the re-cal (Application.Calculation = xlCalculationManual) until you are ready and then turn it back on (Application.Calculation = xlCalculationAutomatic) just before you re-calculate? -----Original Message----- Hi everybody, I have a problem making sure all the formulas in the spreadsheet recalculate, when recalc is called from within a macro. I have a complicated spreadsheet model that takes a second or two to recalculate. I run a macro that: 1. Sets up parameters 2. Forces recalc 3. Grabs the output 4. Cycles back to step 1 with different parameters, etc. My code looks something like this: For j = pageFirst To pageLast Step pageStep <set up parameters of the j-th scenario Application.CalculateFull <save the output, which is just a rectangular range in one of the tabs Next The problem is, sometimes Excel seems to grab the output before a complete recalc is done. So the formulas are only partially updated when the output is saved. This happens pretty randomly, but also frequently enough (once in a 1,000 loops or so). Why is this happening? Is there any way to make ABSOLUTELY sure ALL formulas have been recalc'd before proceeding to the output? Thanks a lot! Tom . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2000 Problem | Excel Discussion (Misc queries) | |||
PROBLEM:How to squeeze 2 Page sized Chart in Excel 2000 & embed in Word 2000 and print from Word to fit one page ??? | Excel Discussion (Misc queries) | |||
PROBLEM:How to squeeze 2 Page sized Chart in Excel 2000 & embed in Word 2000 and print from Word to fit one page ??? | New Users to Excel | |||
problem excel 2000 | Excel Programming | |||
UDF recalc problem between sheets | Excel Programming |