Automatic Calculation in Excel

Post Reply
Anirudhap
Posts: 2
Joined: Fri Dec 26, 2008 3:59 am

Automatic Calculation in Excel

Post by Anirudhap »

hi!
I am facing this issue whenever I take a snapshot / slice through Perspectives. The "Automatic" calculation option in Excel gets de-activated and the "Manual" option gets activated as shown in the attached screenshot.
Automatic Calculation in Excel
Automatic Calculation in Excel
Automatic Calculation in Excel.GIF (18.72 KiB) Viewed 24177 times
I have to manually activate the "Automatic" option again ; needs to be done everytime a snapshot is taken.

Is there any option to prevent the de-activation of the "Automatic" option.

regards,
Anirudha
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Automatic Calculation in Excel

Post by Steve Rowe »

Hi Aniruda,
Welcome to the forum.
I don't think there is a way to stop this from happening, but it's bad practive to work with TM1 sheets and have calcualtion on automatic. If you do then everytime there is a small change in the excel environment all your TM1 sheets will get recacluated. This just grinds the server and creates network traffic where none is required.

Cheers,
Technical Director
www.infocat.co.uk
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: Automatic Calculation in Excel

Post by Steve Vincent »

Its an Excel issue too, rather than TM1. I'd pay good money to have a way of consistantly managing the on/off status of autocalc, it drives our users nuts :?
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
Anirudhap
Posts: 2
Joined: Fri Dec 26, 2008 3:59 am

Re: Automatic Calculation in Excel

Post by Anirudhap »

hi,Steve!

Thanks for the inputs.

Same here....users are going nuts over this issue. The reason why both excel spreadsheets with calculations and TM1 is being used simultaneously is b'cos a lot of analysis and simulation is done basis budgeting data extracted from the Budget Model deployed through TM1. Hence, it would be an impractical expectation of preventing users from accessing their excel worksheets until and unless we manage to move all their excel templates to TM1.

regards,
Anirudha
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Automatic Calculation in Excel

Post by Steve Rowe »

This can be managed using VBA in the activate / deactivate macros of excel workbooks.

'automatic calcualtion
Application.Calculation=xlAutomatic

'manual calculation
Application.Calculation=xlManual

but this is pretty cumbersome and can be quite a maintenace burden depending on how many spreadsheets you have. Not really a solution more of a workaround.

If you add the automatic one to the workbook activate event then you can at least ensure that the pure excel workbooks behave normally.

HTH
Technical Director
www.infocat.co.uk
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Automatic Calculation in Excel

Post by Martin Ryan »

If we stick it in the *key_err tracer would that a) be viewed as an enhancement by all or only some and b) consistently turn off the calculation, or give that it's an xla rather than and xls, will the auto/manual flag still be set based on the first workbook opened?

Anyone fancy testing question b?

Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
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: Automatic Calculation in Excel

Post by Alan Kirk »

Now, everyone DOES remember that Iboglix explicitly recommends that autocalculation be turned OFF, yes? I can't find the reference at the moment; it's not in the 9.4 manuals, but I know that it's in some of the release notes (which unfortunately I don't have to hand at home).
"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: Automatic Calculation in Excel

Post by Alan Kirk »

Alan Kirk wrote:Now, everyone DOES remember that Iboglix explicitly recommends that autocalculation be turned OFF, yes? I can't find the reference at the moment; it's not in the 9.4 manuals, but I know that it's in some of the release notes (which unfortunately I don't have to hand at home).
Known issues as recently as the 9.1 release notes:
Excel Crashes When Copying/Pasting Slice Cells That Reference Hidden Cells (298323 and others)
When Excel is in Automatic calculation mode, copying and then pasting a slice cell that references any hidden cell may crash Excel. If you encounter this problem, you can set the worksheet containing the TM1 slice to Manual recalculation mode to safely copy/paste slice cells.
Excel Crashes When Copying/Pasting Cells That Contain a DBRW Formula (297136, 297515)
Using TM1 in Excel can sometimes cause Excel to hang or crash when copying data from a cell containing a DBRW formula and then using Paste or Paste Special to paste the data into another cell while Excel is in Automatic calculation mode.
If you encounter this problem, you can set the worksheet containing the TM1 slice to Manual recalculation mode to safely copy/paste slice cells.
These aren't listed as Known Issues in 9.4, but I''d be far more comfortable with that had they been explicitly listed as fixed issues, which they aren't.
"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.
TJMurphy
Posts: 74
Joined: Mon May 12, 2008 12:25 pm
OLAP Product: TM1
Version: PA 2.0.6 Local
Excel Version: Excel 2016

Re: Automatic Calculation in Excel

Post by TJMurphy »

@ Steve, we have a bunch of models with this code to manage the Autocalc. However, we've found that if Excel is in auto-calc then the model calculates once *before* hitting the code to switch it to manual (these are models being launched from the Application folder). And, near as I can figure it, the "autocalculation" method is the slow version (ie like DBRs rather than the two pass DBRW version).

Just wondered if anyone else had any insight on this?

We've gotten it reasonably consistent now - to make Excel start up in manual calculation, we have new default templates (book.xlt and sheet.xlt) which are saved with manual calculation turned on. Doesn't help the original poster wanting to mix and match auto and manual though.

Tony
lotsaram
MVP
Posts: 3648
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Automatic Calculation in Excel

Post by lotsaram »

if Excel is in auto-calc then the model calculates once *before* hitting the code to switch it to manual
Yes unfortunately this is the case, even if the code in in the workbook open event, if calculation mode is automatic then the workbook will still calculate once before the workbook open code runs. And as noted if the workbook is large then not having TM1 control the recalculation can result in very very slow performance. Unfortunately this just seems to be something that users have to be aware of and wary of, basically ensure XL is in manual calculation mode before opening any TM1 workbooks and ensure TM1 workbooks are always saved with manual calculation on.

For users using TM1 every day or every time they use XL this quickly becomes second nature (as does the Shift + F9 combination) but for infrequent users this is a real pain.
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: Automatic Calculation in Excel

Post by Steve Vincent »

Tried that code a long time ago and it doesn't always work, especially when users open CSV files in Excel. Then it just ignores you and turns autocalc back on anyway. Thanks M$ :roll:

Its just something we live with and warn users of at every opportunity...
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
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: Automatic Calculation in Excel

Post by Alan Kirk »

Steve Vincent wrote:Tried that code a long time ago and it doesn't always work, especially when users open CSV files in Excel. Then it just ignores you and turns autocalc back on anyway. Thanks M$ :roll:
No it doesn't. Opening a .csv triggers a global calculation, which I agree is stupid, annoying and pointless (much like the idiots who (for example) decided to inflict Office 2007's GUI on us without a "classic mode", abandoned user level security in Access, designed UAC, came up with the dialog warning you against changing file extensions, decided that icons should look like they were designed for ADD 4 year olds by Fisher Price (I swear, they get bigger and chunkier and brighter with each incarnation of Windows), etc, etc), but it won't actually change your calculation mode. This is why I always save comma delimited data with an extension of .cma which triggers the import wizard instead.

It's impossible that MS doesn't know that this is a common user annoyance. And yet have they fixed it in Excel 2007? Nope, still there. You open a .csv, and it'll recalculate everything.

Still leaves your calculation setting as Manual, though.
"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
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: Automatic Calculation in Excel

Post by Steve Vincent »

It certainly has changed in the past, maybe have just been coincidence that it was when using csv files then. As you said tho, either way its still stupid and annoying and unnecessary and totally M$-esq :x
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