Solved

Excel 2007 VBA to Disable "Recalculate Workbook Before Saving"

Posted on 2011-09-13
6
6,186 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

732 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