title: Application.Calculation depending on workbook
body: <p>I'm managing a workbook with more than 200 000 formulas (some really complicated array formulas) which means that I can't let Excel automatically calculate all the cells every time I click somewhere (it takes around 8 hours to calculate everything).</p>
  
  <p>Instead of that, the calculation is set to manual and I have the following VBA code executed when <em>Calculation.xlsm</em> is opened:</p>
  
  <pre><code>With Application
      .CalculateBeforeSave = False
      .Calculation = xlCalculationManual
  End With
  </code></pre>
  
  <p>I use custom buttons to calculate only some parts of the 200k cells when needed.</p>
  
  <p>I noticed that Excel does keep track of that setting in each workbooks, which means that if I open my Calculation.xlsm, Excel remembers that the calculation is set to manual. If I open my Values.xlsx, Excel does remember that the calculation is set to automatic. This was before I tried to copy values from <em>Calculation.xlsm</em> to <em>Values.xlsx</em>.</p>
  
  <p>Now, because I'm using VBA in Calculation.xlsm to copy values to Values.xlsx, Excel does apply the <code>Application.Calculation</code> setting to that workbook too, which means that if I open it with a new instance of Excel, the calculation will still be set to manually.</p>
  
  <p>If I add a <code>Application.Calculation = xlCalculationAutomatic</code> before closing the <em>Values.xlsx</em> with VBA in my <em>Calculation.xlsm</em> workbook, it will work, but Excel will also start to compute the 200k cells in my <em>Calculation.xlsm</em> workbook, which I obviously don't want.</p>
  
  <p>So my question is about how to actually set the calculation of Excel based on a specific workbook instead of with the <code>Application</code> object. This is based on the fact that Excel does keep track of that setting depending on which workbook is opened (you can just do the test and create 2 different <em>.xlsx</em> files, one with the calculation enabled and the other with the calculation disabled and Excel will remember these settings).</p>
  
  <p>I know I could use the <code>Worksheets.Range.Calculate</code> method to calculate my Values.xlsx workbook before closing it, but the calculation will still be set to manual if I open it in a new instance of Excel after that.</p>
  
  <hr>
  
  <p><strong>EDIT 3:20pm</strong>: Not sure if I was clear enough, English isn't my native language. In short, I have <em>Calculation.xlsm</em> with VBA and <code>Calculation</code> set to manual. I have <em>Values.xlsx</em> with no VBA and Calculation set to automatic. If I open <em>Values.xlsx</em> with the following VBA code in <em>Calculation.xlsm</em>, Excel will automatically convert my <em>Values.xlsx</em> workbook to manual calculations.</p>
  
  <p><em>Calculation.xlsm</em> code:</p>
  
  <pre><code>Private Sub Workbook_Open()
      With Application
          .CalculateBeforeSave = False
          .Calculation = xlCalculationManual
      End With
  End Sub
  
  Sub someFunction()
      Set WB = Application.Workbooks.Open("Values.xlsx")
      Set WBws = WB.Sheets("mySheet")
      DoEvents
      wb.Save
      WB.Close
  End Sub
  </code></pre>
  
  <p>After the execution of someFunction(), <em>Values.xlsx</em> calculation is set to manual. That's the problem. I would like it to stay on automatic (and I can't add VBA to that file, it must be all done from <em>Calculation.xlsm</em> like above).</p>
  
  <hr>
  
  <p><strong>EDIT 3:40pm</strong>: Could I just have my big workbook with Application.Calculation set to manual, put all the data I need in the clipboard (I only need the values, not the formulas), close it (will the VBA still continue to execute even if I close the workbook from which it is executed?), set Application.Calculation to Auto (since there is no open workbook), then open the destination workbook to paste the values (will Excel still keep the data in the clipboard since the other workbook is closed?), then save and close that workbook, set back the calculation to manual (no workbook opened) and reopen the original workbook from which the code was executed?</p>