Stop calculating on open

[Guest]

I have a large file which I open frequently, and it drives me crazy if I
forget to turn off automatic calculation, as it can take 10 minutes to
calculate - I usually crash out of Excel to stop it.

I added code to switch calculation to manual as the workbook opens, but
unfortunately, that workbook_open procedure seems to fire after the workbook
has been through the calculation process. Is there any code that runs
immediately when a file is opened?

Thanks in advance
Daniel

 

[Guest]

Hi Daniel,

there's an easy way - without any programming...

Simply load the file and (unfortunately you'll have to wait this time till'
it has calculated - have you every tried to hit "Esc" during calculation?)

Now, you select "Tools" - "Macros" - "Visual Basic-Editor".
In the Project Explorer you'll see the sheets of your workbook.
Select the one which has these tremendous calculations.
Now you should see in the properties of the sheet the property
"EnableCalculation".
Set this to False and you're done.

Now the sheet only calculates if you hit "F9"...

Hope this helps and best wishes,
Eric

 

[Guest]

Wow - a fabulous solution! Thanks!

Hi Daniel,

there's an easy way - without any programming...

Simply load the file and (unfortunately you'll have to wait this time till'
it has calculated - have you every tried to hit "Esc" during calculation?)

Now, you select "Tools" - "Macros" - "Visual Basic-Editor".
In the Project Explorer you'll see the sheets of your workbook.
Select the one which has these tremendous calculations.
Now you should see in the properties of the sheet the property
"EnableCalculation".
Set this to False and you're done.

Now the sheet only calculates if you hit "F9"...

Hope this helps and best wishes,
Eric

 

[Dave Peterson]

You sure that it calculates when you hit F9??

From VBA's help for the .enablecalculation property:

When the value of this property is False, you cannot request a recalculation.
When you change the value from False to True, Excel recalculates the worksheet.

 

[Dave Peterson]

I think I'd use a helper workbook that turns off calculation, then loads your
real file.

 

[Guest]

Hi Dave,

I tried it - it does....at least in my MS Excel...

Best wishes,
Eric

 

[Guest]

Hi Dave,

to make sure I'm not wrong I re-tried it and this is very strange:
Some calculations are done - some not.... e.g. "SUM" is not calculated while
"Now" is calculated when hitting F9.

OK...to ensure calculation is possible, I'd add either a button on the
worksheet or assign a macro to a shortcut to make it calculate (by setting
worksheet.enablecalculation=True and after calculation again to "False".
This would enable me to calculate the sheet whenever I (and not Excel )
want to but I'd be sure to use False as a standard.

Best wishes,
Eric

 

[RSantos]

Hi Daniel,

there's an easy way - without any programming...

Simply load the file and (unfortunately you'll have to wait this time
till'
it has calculated - have you every tried to hit "Esc" during
calculation?)

Now, you select "Tools" - "Macros" - "Visual Basic-Editor".
In the Project Explorer you'll see the sheets of your workbook.
Select the one which has these tremendous calculations.
Now you should see in the properties of the sheet the property
"EnableCalculation".
Set this to False and you're done.

Now the sheet only calculates if you hit "F9"...

Hope this helps and best wishes,
Eric

HI I´m trying to use option of setting enablecalculation to false, but
when I reopen the document this property is set to true again. I´m
saving the document after setting enable calculation to false, but it
doesn´t work.