Solved

Excel 2007 VBA to Disable "Recalculate Workbook Before Saving"

Posted on 2011-09-13
6
5,869 Views
Last Modified: 2012-06-21
I have an Excel 2007 .xlsm file I'm working with, and it takes forever to recalculate. (I'm working on implementing the recomendations to reduce calculation time per http://msdn.microsoft.com/en-us/library/ff700515.aspx.)
I use "Application.Calculation = xlCalculationManual" VBA to switch Excel to manual calculation mode, but it still likes to recalculate on save. However, I often hit save as to avoid loosing my work, and then realize I just set myself back 30 minutes (each time) while it calculates.
I'm looking for a way to programmatically (VBA?) deactive Excel 2007 Menu > Excel Options > Formulas > Calculation Options > Workbook Calculation > "Recalculate workbook before saving" while in Manual calculation mode (which, again, I activate via the VBA xlCalculationManual option).

Any ideas how to disable "Recalculate workbook before saving"  via VBA?
0
Comment
Question by:travisjbennett
  • 4
6 Comments
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 500 total points
ID: 36528970
Is this what you are looking for?

    With Application
        .Calculation = xlManual
        .CalculateBeforeSave = False
    End With

Open in new window

0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 36528978
did you do in thisworkbook

Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
End Sub
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36528983
If not, try the following steps.

1 - Turn on "Record a macro". I'm on Excel 2003, so I don't know how you do that in later releases.
2 - Turn off the option to calculate before save.
3 - Stop recording the macro.
4 - Examine the macro in the VBA editor.

For me, the macro is in a Module->Module1

Just changing the manual and calc on save options, recorded the following macro which I edited for you above
Option Explicit

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2011-09-13 by Richard Alan Quadling
'

'
    With Application
        .Calculation = xlManual
        .MaxChange = 0.001
        .CalculateBeforeSave = False
    End With
    ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36529024
OOI,
Debug.Print xlManual
Debug.Print xlCalculationManual
Debug.Print xlAutomatic
Debug.Print xlCalculationAutomatic

Open in new window


outputs
-4135 
-4135 
-4105 
-4105

Open in new window


In different versions of excel, these MAY have different values.

http://msdn.microsoft.com/en-us/library/ff835845.aspx is of interest here also.

0
 
LVL 1

Author Comment

by:travisjbennett
ID: 36529040
Yep RQuadling... shoulda used Macro Recorder...
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36529776
Ha! Don't worry about beating yourself up. This is an easy mistake to make.

Spending two hours on a job and realising you've mixed up the client's databases on the development server ... ho hum.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My experience with Windows 10 over a one year period and suggestions for smooth operation
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question