Solved

Excel 2007 VBA to Disable "Recalculate Workbook Before Saving"

Posted on 2011-09-13
6
5,710 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

821 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