open excel in calculation manual

[janeninca]

how does excel determine if it opens a file in manual or automatic?
how can i choose that excel opens every file in calculation manual?

regardsn
jan

 

[Richard Schollar]

The calculation setting is determined by the first opened file within Excel (with Automatic being the default). The easiest way to ensure calculation is set to manual is to create or find your Personal.xls macro workbook, unhide it (Window>Unhide) and use Tools>Options to amend to Manual Calculation and then rehide Personal.xls and close down Excel replying Yes when asked if you want to save changes to Personal.xls.

Now when you reopen Excel, your calculation settings will be on Manual.

 

[janeninca]

thanks,

i don't have a file personal.xls yet.
in what directory should i save the file? (i'm using excel2003)

jan

 

[Richard Schollar]

Jan

Go Tools>Macro>record New Macro and in the dialog that opens choose the "Store macro in: Personal Macro Workbook" option. This will automatically create one for you. Click Finish (so you haven't actually recorded any actions - this doesn't matter as all you want to do is create the workbook). Now go to Window>Unhide and select Personal.xls from this list. Then go Tools>Options and make sure calculation is on Manual, then rehide the Personal.xls, then shut down Excel (and say Yes to save changes to Personal.xls).

It should then work as described.

 

[janeninca]

hi, i did this and it works,
but when i open a second excel, i now get the message" personal.xls is locked for editing by 'myself'."

how can i avoid this?

jan

 

[Richard Schollar]

You won't be able to avoid it - you can just click on thru and accept access as read-Only. It happens because Excel will automatically open any files in your XLSTART folder. The Personal.xls is already open in your first instance of Excel, hence you get that message.

Do you need to have a separate instance of Excel open? Most of the time I only have a one instance open with multiple workbooks opened in that one instance.

 

[RoryA]

You can set the workbook's IsAddin property to True and then you won't get the message. You also won't get any of the macros it may contain appearing in the list of macros, though, which may or may not be a problem.

 

[janeninca]

hi,

yes i need more than one instance.
when one instance is calculating (some macros take a lot of calculation time), i can continue working in another instance.
would there be another solution then?

jan

 

[Richard Schollar]

Rory - does that mean you could just use a purpose built add-in to change the calculation mode when you open up Excel? I don't suppose there's a registry hack to do this (switch calc to manual) is there?

 

[RoryA]

Just realised that if you set the IsAddin property to True, it won't change the calculation mode. You would need to add a workbook_Open event to change it.