Disable Calculations on certain worksheets

[Tkeller]

I have a very large excel model that requires "solving". This is taking a very long amount of time because each "loop" requires the model to recalculate every worksheet. I would like a way to only enable "calculation" worksheets, and to turnoff output and input worksheets that do not impact the calculations. Is there a simple way to do this?

I was thinking that I could mark the vital "calculation" worksheets (e.g. with a "1" in cell A1), then I could develop a procedure to first turns calculations to "manual", then goes through each worksheet of the workbook and only enables calculations in those worksheets marked with a "1". Then after performing all the solving, I could turn calculations back to "automatic". I am not sure if my idea is the best way to go about this, nor exactly how I would write this code (I am still a novice, but learning every day!).

Any help would be appreciated.

Thanks.

 

[drsarao]

As far as I know, Calculation is set to Auto or Manual at Workbook level and one can't disable calc in individual worksheets.

Those worksheets "that do not impact the calculations" will in any case be NOT recalculated. So even theoratically turning off calculation in these sheets will not make any difference.

Does this line "each "loop" requires the model to recalculate every worksheet" mean you are using VBA/macro? If yes then try this:

Sub Test()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'your code here

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

This will stop all calculations, alerts and screen changes, speeding up the code considerably.

 

[Tkeller]

Thanks. i am not sure I follow, and perhaps I was not clear. Let me try to give a simple example. I have a worksheet that I need to iterate to solve. In other words, I need to try a value (guess), perform the calculations, then see if the value I guessed was correct - if not, I try another guess that gest me closer. I have an algorithm that trys a better guess, but might take me 20 trys to guess correctly I also have an output sheet that graphs a bunch of things for me. Each time, I try a "guess", the output worksheet changes. I would like to turn the output worksheet off so that it does not slow down the guessing process. Once my guess is correct, then I can turn the output sheet back on.

I am not a VBA expert, but just hunting around google, I thought, perhaps incorrectly, that I could turn off calculations, then only turn on caculations on the caculation worksheets. I thought I could use the two functions below


Application.Calculation=xlCalculationManual


worksheet.enablecalculation=true

Thanks

 

[drsarao]

I was wrong. One can disable Calculation on individual worksheets.
Check this out - http://www.ozgrid.com/forum/showthread.php?t=30323&page=1

 

[FastExcel]

The OzGrid solution is incorrect and does not work: Calculation is at the Application level rather than at Workbook or Worksheet level.

What you are looking for is Worksheet.EnableCalculation. Setting it to False for one or more sheets prevents the sheets being calculated in this Excel session (it gets reset when you close and open the workbook).
the downside of this technique is that when you set it back to True again the next calculation will recalculate ALL the formulas on the worksheet, not just the ones that actually need recalculating.

 

[ZVI]

You can do iterations in the separate cells (separate sheet is good for that) which are not referenced from any cells of main slow model formulas.
After finishing of iterations the result values will be copied via macro to the cells which are used as dependences of the main model with that model recalculation.
Macro can be automated by some criteria.

Without macro the formulas like this will be used as well: =IF(MyFlag=1,A1,0)
where MyFlag is the named range for triggering main model recalculation,
result of iterations is in A1, and some cells of main model are referenced to A1 constantly.

If MyFlag=1 (set by hand) then value A1 goes to the main model with model recalculation.
Else if MyFlag<>1 then zero constant goes to the main model without causing of its recalculation each time on iterations.
MyFlag=1 can be set automatically by formula with some criteria.