[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel 2007 VBA to Disable "Recalculate Workbook Before Saving"

Posted on 2011-09-13
6
Medium Priority
?
6,909 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 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

649 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