Solved

Excel 2007 VBA to Disable "Recalculate Workbook Before Saving"

Posted on 2011-09-13
6
5,396 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:
RQuadling earned 500 total points
Comment Utility
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
Comment Utility
did you do in thisworkbook

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

Expert Comment

by:RQuadling
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
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
Comment Utility
Yep RQuadling... shoulda used Macro Recorder...
0
 
LVL 40

Expert Comment

by:RQuadling
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Outlook Free & Paid Tools
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now