Help - setting up manual recalculation when opeing a file (but before it starts recalculating)

  • Thread starter Thread starter German
  • Start date Start date
G

German

My file is loaded with custom functions and therefore recalculates
very slowly. To make it easier to work with the file I set it to
"manual" calculations.
However, I have a problem when opening the file - it immediately
starts recalculating and it takes forever for the file to open. The
only solution is to set options to manual calculation before opening
the file. I tried to automate this by writing a code under
"ThisWorkbook" object:

Private Sub Workbook_Open()

Application.Calculation = xlCalculationManual

End Sub

Unfortunately, it didn't solve the problem. This code doesn't seem to
be working - the file still recalculates when I open it. Is there a
way to automate this and set options to manual calculation when
opening the file, but before it starts recalculating everything?

Thank you for your help.

German.
 
Perhaps setting the EnableCalculation property of the worksheet or sheets
that bog you down to false and then setting them to true when you're ready
will do it.

Good luck,
Geoff

From the Excel VBA help file:
EnableCalculation Property Example

This example sets Microsoft Excel to not recalculate worksheet one
automatically.

Worksheets(1).EnableCalculation = False
 
Where did you put the code?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Unfortunately Excel does not save the enablecalculation property with the
workbook, so it is always true when a workbook is opened and will not help
you prevent a workbook from recalculating when opened.

Excel sets its initial calculation state from the first workbook it opens.

So either set your workbook to manual and make sure it is the first workbook
opened, or make another workbook with calculation set to manual and save it
in excelstart.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
Thank you very much for your help.

German.

Unfortunately Excel does not save the enablecalculation property with the
workbook, so it is always true when a workbook is opened and will not help
you prevent a workbook from recalculating when opened.

Excel sets its initial calculation state from the first workbook it opens.

So either set your workbook to manual and make sure it is the first workbook
opened, or make another workbook with calculation set to manual and save it
in excelstart.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
Click to expand...
 
That's good to know, Charles--thanks for the info. I don't know how many
times I've tried something, only to find out it doesn't "stick" after
saving, closing, and reopening a workbook. Sorry for the misleading
information, German. I'll be a bit more careful about sticking to what I
know in future posts!

Regards,
Geoff
 
Back
Top