Automatic Calculation in Excel

[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.GIF (18.72 KiB) Viewed 30860 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

[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,

[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

[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

[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

[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

[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).

[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).

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.

[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]

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.

[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$

Its just something we live with and warn users of at every opportunity...

[Alan Kirk]

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$

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.

[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