Auto Recalc - STRESS !!!

Post Reply
User avatar
Ajay
Regular Participant
Posts: 183
Joined: Wed May 14, 2008 8:27 am
OLAP Product: TM1
Version: 10.2.0, PA 2.0.9
Excel Version: 2016
Location: London

Auto Recalc - STRESS !!!

Post by Ajay »

Morning All,

Does anyone have a little .xla or something that defaults any spreadsheet to manual calculations when opened, instead of calc'g it ?

Regards
Ajay
Paul Segal
Community Contributor
Posts: 306
Joined: Mon May 12, 2008 8:11 am
OLAP Product: TM1
Version: TM1 11 and up
Excel Version: Too many to count

Re: Auto Recalc - STRESS !!!

Post by Paul Segal »

Hi Ajay,

Here's a interesting (for certain values of interesting) link to a Microsoft KB that illuminates the auto recalc behaviour in Excel: http://support.microsoft.com/default.as ... us;Q214395 which should be of help.

Regards
Paul
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Auto Recalc - STRESS !!!

Post by Alan Kirk »

Does anyone have a little .xla or something that defaults any spreadsheet to manual calculations when opened, instead of calc'g it ?
Yeah, irritating, innit? There IS usually a way around it though which involves no VBA code at all.

As I understand it Excel will adopt the calculation setting of the last opened file at startup, though since calculation is a global setting (rather than a workbook one) it will ignore the settings of any subsequently opened files.

(The calculation mode is apparently buried somewhere in the file, even if it's not an exposed property.)

The secret is therefore to make sure that the last-opened file at startup has a manual calculation mode.

Most people don't open files at start-up (aside from the odd .xla perhaps) but rather start with a blank workbook, the ubiquitous "Book1". What you therefore have to do is make sure that the blank workbook has a manual calculation setting.

Unless you specify otherwise, Excel will use its own internal settings for blank workbooks, including automatic calculation. To substitute your own, you need to create a new global template as follows:
- Close down any workbooks that you have open in Excel;
- Open up a new blank workbook;
- Change its calculation setting to Manual;
- If you feel inclined, take the time to make any other changes that you like. Don't like Arial 10 as the default font? Redefine the Normal style. Want 4 sheets instead of 3? Add one, and so on.
- Find the folder called xlStart. This will typically be on a path like
C:\Documents and Settings\{UserName}\Application Data\Microsoft\Excel\XLSTART
- Save the modified workbook as an Excel template (.xlt) file named Book.xlt (NOT Book1.xlt, just book.xlt)

Thereafter when you open a session of Excel, the blank workbook that opens should have all of your updated styles and settings... including the manual calculation mode.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Auto Recalc - STRESS !!!

Post by Alan Kirk »

Alan Kirk wrote: As I understand it Excel will adopt the calculation setting of the last opened file at startup,
Oops, my memory lapse; as per the article mentioned by Paul, it's actually:
The first document opened uses the calculation mode with which it was last saved. Subsequently opened documents use the same mode.
Regardless, the book.xlt technique should still work.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Auto Recalc - STRESS !!!

Post by jim wood »

I have had real issues with this myself as I have a set of data templates completed by users. I have found that somtimes (Even thogu you have autocalculate siwtched off.) it remebers the setting from when the file was last saved and they always start loading data.

The only solution I ahve found is make sure you are not connected to any TM1 server when opening the file. Even if it set to auto calculate it comes back really quickly,

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Auto Recalc - STRESS !!!

Post by Steve Vincent »

Orrrr you could use the addin i created and demo'd at the "TM1 User Conference of the North" last year :D

This puts a drop down box in to the menu bar with 2 options, calc suppress ON and OFF. ON will ensure any new or opened wb will have calculation turned to manual. OFF will make it auto calc. Note it only makes this change when a wb is OPENED or CREATED. Just selecting the drop down will NOT change anything ;)

I'll add it to the thread of useful stuff later as well...
Attachments
TM1 Tools.zip
ensures auto calc is always on or off, based on users needs
(8.36 KiB) Downloaded 795 times
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Post Reply