Disable Calculations on certain worksheets

Tkeller

Board Regular
Joined
Jul 23, 2003
Messages
143
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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:
Code:
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top