30662

Excel Automatic Calculation Setting

I have a complex project in `Microsoft Office Excel 2007' which utilises a large number of UDFs. Through VBA in a <strong>Workbook_Open</strong> Event, I set Excel Automatic calculation to OFF and a strategically placed <strong>Calculate</strong> method to manually calculate the cells whenever I need it so that the UDF doesn't perform recalculation unintentionally.

If the workbook is the <strong>ONLY</strong> one opened (or the <strong>first</strong> to open) in an Excel instance, everything works perfect. Only when it's opened <strong>AFTER</strong> another workbook (within the same instance), my project will inherit the Automatic calculation setting from the <strong>FIRST</strong> workbook and perform endless calculation on my UDFs. The disable code placed in the Workbook_Open event isn't executed until the UDF finishes the calculation (which can take forever). This only happens if my project is <strong>NOT</strong> the one opened first.

Through http://www.decisionmodels.com/calcsecretse.htm, I discover that it is the nature of Excel to perform the calculation process <strong>BEFORE</strong> the Workbook_Open event is executed.

So the question I have obviously relates to the project being opened <strong>AFTER</strong> another workbook is opened with automatic calculation turned ON:

<ol> <li>How do I force my project to <strong>disable</strong> automatic calculation without it performing recalculation first (remember, problem only occurs when the project not the first one to be opened since it will follow settings from previously opened workbook) <strong>OR</strong>...</li> <li>How do I get the project to open in ANOTHER INSTANCE (when double clicked) to avoid inheriting automatic calculation setting from the previous workbook.</li> </ol>

Either way, the answer I'm seeking is for the project to open without performing the calculation first.

Thanks

Answer1:

One way is to use a different workbook (Opener.xls) to initiate opening the UDF workbook (udf.xls)

in Opener.xls the Workbook_Open code - sets calculation to manual - opens udf.xls

Answer2:

In your question I don't recognize the way you use to change and inherit that option to your workbooks, But I answer it as a solution:

Use VBA and running VBA macros to change that option for just your active sheet as soon as you need to calculate; by using it like this:

With ActiveSheet .EnableCalculation = False .EnableCalculation = True .Calculate End With

In another ways that may you need, you can read this part of MSDN article.

Recommend

  • Neo4j create graph that some nodes have more than one relationships
  • How to turn off adjusting large titles by UITableView in iOS 11?
  • Using Split function in Perl but keeping the delimiters [closed]
  • NumPy Matrix operation without copying
  • iOS: How to convert the self-drawn content of an UIView to an image (widespread general solution ret
  • jquery insert html into list before a child ul-tag
  • mod_rewrite replace all slashes (/)
  • Excel Range in C# using interop.Excel. Range both empty and not empty?
  • Copying and renaming unopened workbook in excel
  • Get picture position in Apache POI from Excel xls HSSF
  • Dropdown menu with the dropdown-menu-right class does not align to the right
  • Open view controller when remote notification pressed
  • Looking for datastructure that maintains a size & purges older elements in the process
  • Displaying iOS iAds only to supported countries
  • Why does Redshift need to do a full table scan to find the max value of the DIST/SORT key?
  • ANTLR4: Unrecognized constant value in a lexer command
  • how to insert new rows with values in the same sheet of an excel file in java
  • jQuery, Calling multiple animations in a row on click
  • What does the lower left pane in OllyDbg displays?
  • Weighted round robin dns between 2 Cloudfront distributions
  • Ant: fileset “dir” attribute with a runtime expanded full path
  • Fixed Background Works in Chrome but Not Firefox?
  • Python Paramiko send CTRL+C to an ssh shell
  • Entity Framework unable to delete database, database in use
  • Python adding lots of things to PATH. How do I stop?
  • Vuejs: Lifecycle hooks of child routerview components using keep alive
  • Possible to “watch” both HAML and SASS at the same time?
  • TextToSpeech.setEngineByPackageName() triggers NullPointerException
  • WPF - CanExecute dosn't fire when raising Commands from a UserControl
  • jQuery show() function is not executed in Safari if submit handler returns true
  • How to recover from a Spring Social ExpiredAuthorizationException
  • javascript inside java/jsp code
  • WinForms: two way TextBox problem
  • ILMerge & Keep Assembly Name
  • 'TypeError' while using NSGA2 to solve Multi-objective prob. from pyopt-sparse in OpenMDAO
  • Large data - storage and query
  • WOWZA + RTMP + HTML5 Playback?
  • How to disable jQuery.jplayer autoplay?
  • How get height of the a view with gone visibility and height defined as wrap_content in xml?
  • Linking SubReports Without LinkChild/LinkMaster