Bug 60964 - Recalculation on load: Behavior problematic to certain sheets
Summary: Recalculation on load: Behavior problematic to certain sheets
Status: RESOLVED WONTFIX
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.0.3 release
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: regression
Depends on:
Blocks:
 
Reported: 2013-02-16 19:17 UTC by Christian Fries
Modified: 2013-02-18 20:44 UTC (History)
0 users

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Christian Fries 2013-02-16 19:17:57 UTC
LibreOffice 4.0.0.3 (platform independent, but tested with the Mac OS X version) offers a setting at Preferences->LibreOffice Calc->Formula called "Recalculation on file load".

The default setup is "Never recalculate" (tested on Mac OS X). However, LibreOffice 3 and OpenOffice behave by default as "Always recalculate" would do.

To have LibreOffice set to "Never recalculate" will render many sheets useless or in inconsistent state.

The problem is that in this mode, LibreOffice will use cached values of cells and will not recalculate the entire sheet. Assume that cell A1 refers to B1 and C1 and B1 is changed by the user. Then A1 is recalculated using a cached value of C1. This is problem if C1 contains some macro and the spreadsheet relies on the fact the at least C1 is called once to initialize something (e.g. something needed when calculating A1).

While it is OK to rely on cached values during operation of a sheet (is is natural to only recalculate cells depending on changed cells) many professional spreadsheets reply on the fact that the sheet is recalculated completely at least once.

For example: I use the plugin Obba ( http://www.obba.info ) to use external Java libraires in cell functions. Here it is crucial that those plugin functions get called at least once. (Note: this is not a problem of an incorrect cell dependance / dependency tree). Excample: Cell C1 contains the macro to create an object in the external module and returns a reference to is, cell A1 consumes the handle in C1 and a value in B1. In the new LibreOffice the macro in C1 is never executed and hence the external module is not initialized correctly.

There are a couple of workarounds:
- All such macros and add-in functions have to be marked as Volatile: This is not a easy step to do, since UNO XVolatileResult is a complex beast and also requires an additional caching of results.
- The user is advised to change the setting manually to "Always recalculate". This is a problem, since many user will not realize that this is the reason why there sheet remains corrupted.
- All sheets are equipped with an "OnLoad" Macro which performs a recalculation (I have yet to find out how to do it).

I believe it would be best to set the default of LibreOffice to "Always recalculate" - at least for sheet which have "Auto Calculation" enabled. Please consider this! Currently most of my sheets from http://www.finmath.net which I provide for Excel and OpenOffice/LibreOffice will not work with LibreOffice 4 because of this issue (they work fine with OpenOffice 3, LibreOffice 3, Excel, etc).
Comment 1 Jorendc 2013-02-16 20:05:24 UTC
I spoke to a core developer about this; They wouldn't set 'always recalculate' as default. Therefore I mark this bug as RESOLVED WONTFIX.

If you have specific problems regarding this Import Cache, please consider to open a new bug with steps how we can reproduce.

Kind regards,
Joren
Comment 2 Christian Fries 2013-02-16 20:13:59 UTC
Alternatively it should be set to the this available option, which is "Prompt the user".

I believe that this change in behavior is a HUGE problem and the user should be made aware of this. The problem will not occure if you use a spreadsheet without macros or any add-ins, but if you look at how Excel is used in the industry (example in financial institutions) then sheets almost always rely on recalculation of macros upon initialization...
Comment 3 Christian Fries 2013-02-16 20:15:48 UTC
Sorry for the type introduced by autocorrection: Should read:

"Alternatively it should be set to the third available option, which is "Prompt the user".
Comment 4 Christian Fries 2013-02-16 21:21:07 UTC
I just discovered that the option "Prompt user" doesn't work at all. So I filed a bug (60973) related to this and suggested an enhancement related to the problem above (60974).
Comment 5 Christian Fries 2013-02-16 22:10:26 UTC
I just found that there is actually no workaround for this (except to advice the user to disable the feature). UNO add-ins with XVolatileResutls are not recalculated either.
https://bugassistant.libreoffice.org/show_bug.cgi?id=60977