Bug 60964

Summary:	Recalculation on load: Behavior problematic to certain sheets
Product:	LibreOffice	Reporter:	Christian Fries <email>
Component:	Spreadsheet	Assignee:	Not Assigned <libreoffice-bugs>
Status:	RESOLVED WONTFIX	QA Contact:
Severity:	normal
Priority:	medium	Keywords:	regression
Version:	4.0.0.3 release
Hardware:	Other
OS:	All
Whiteboard:
i915 platform:		i915 features:

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

Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.