Results 1 to 6 of 6

Disable Calculations on certain worksheets

This is a discussion on Disable Calculations on certain worksheets within the Excel Questions forums, part of the Question Forums category; I have a very large excel model that requires "solving". This is taking a very long amount of time because ...

  1. #1
    Board Regular
    Join Date
    Jul 2003
    Posts
    111

    Default Disable Calculations on certain worksheets

    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.

  2. #2
    Board Regular drsarao's Avatar
    Join Date
    Sep 2009
    Location
    Delhi, India
    Posts
    1,107

    Default Re: Disable Calculations on certain worksheets

    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.
    Excel 2007 Windows 7

  3. #3
    Board Regular
    Join Date
    Jul 2003
    Posts
    111

    Default Re: Disable Calculations on certain worksheets

    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

  4. #4
    Board Regular drsarao's Avatar
    Join Date
    Sep 2009
    Location
    Delhi, India
    Posts
    1,107

    Default Re: Disable Calculations on certain worksheets

    I was wrong. One can disable Calculation on individual worksheets.
    Check this out - http://www.ozgrid.com/forum/showthre...t=30323&page=1
    Excel 2007 Windows 7

  5. #5
    Board Regular
    Join Date
    May 2004
    Posts
    379

    Default Re: Disable Calculations on certain worksheets

    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.

  6. #6
    ZVI
    ZVI is offline
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    2,765

    Default Re: Disable Calculations on certain worksheets

    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 by ZVI; Sep 24th, 2011 at 04:31 PM.
    Vladimir Zakharov
    Microsoft MVP Excel

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com