To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=105986
Issue #|105986
Summary|Provide a way to specify recalculate hints for OOo Bas
|ic functions used in spreadsheet formulas
Component|Spreadsheet
Version|DEV300m55
Platform|Unknown
URL|
OS/Version|Linux
Status|UNCONFIRMED
Status whiteboard|
Keywords|
Resolution|
Issue type|DEFECT
Priority|P3
Subcomponent|programming
Assigned to|spreadsheet
Reported by|smls
------- Additional comments from s...@openoffice.org Sat Oct 17 13:52:26 +0000
2009 -------
I have a multi-sheet OOo Calc file in which the first sheet presents a dynamic
overview of information gathered from string, date and number data in the other
sheets in rather complex ways.
Because of certain limitations in the built-in Calc formula functions (most
notably, SEARCH() not providing a way to check *whether* the specified string
was found (see issue 66590), and LOOKUP() not supporting string keys) I had to
write several custom Basic functions to accomplish my task (all in all, over 150
cells in the overview sheet now call at least one of those Basic functions).
The trouble is: the whole thing is horribly slow now!
Whenever I make *any* change to that overview sheet now - and be it only
changing the contents of one single (unrelated) cell, clicking the "bold"
toolbar button with any cell selected, or even only resizing a column - the
whole interface locks down for about three (!!) seconds while Calc seems to
recalculate all formulas on that sheet (you see a progress bar at the bottom of
the window during those three seconds).
To remedy this situation, I propose allowing certain "recalculate hints" to be
specified for Basic functions in order to limit the number of occasions on which
a function will be re-evaluated when it is used inside a spreadsheet formula.
The easiest solution would probably be to allow the hints to be specified as a
comment in Basic code right before the function definition - maybe something
like this:
Rem FunctionRecalculateTriggers cell_contents_only,
cells_accessed_on_last_evaluation_only
Function MyReallyTimeConsumingCustomLookupFunction(...)
...
End Function
The recalculate hints would cause the results of this Basic function to be
cached for each instance (by that I mean: each "cell from which it is called
from" + "parameters given" combination) and from then on only recalculated under
specific conditions.
The recalculate hints I propose are:
1) "no_recalculate"
--> Never recalculate the function instance (once it has been evaluated
once)
1) "cell_contents_only"
--> Never recalculate the function instance when no actual cell content has
been changed in the spreadsheet (e.g., only formatting or column width changes
have occured)
2) "cells_accessed_on_last_evaluation_only"
--> Never recalculate the function instance when none of the cells that were
read from during the last evaluation of the function instance have changed
There might be other hints that one could add, but for purposes like the complex
overview sheet described above, those three recalculate hints would already be
totally sufficient.
Functions should still be re-evaluated no matter what when the documents is
loaded, when [ctrl]+[shift]+F9 is pressed, when their function code has changed
or when the parameters passed to them have changed.
I'd like to but special emphasis on the proposed
"cells_accessed_on_last_evaluation_only" hint. This is not as arbitrary as it
might sound - in fact, I suppose that almost all functions that don't make use
of the current time, or outside data, etc. might greatly benefit from it.
Think about it... (look at the built in spreadsheet functions to see that this
really makes sense for a lot of functions...):
For example, image a sheet where A2 holds the number 2 and some cell holds the
formula "=OFFSET(A1; 0; A2)":
1) What parts of the spreadsheet are then read (internally) during the
initial evaluation of the formula?
-> Only the contents of the cells A2 and C1.
2) From then on, can the the outcome of the formula change as long as
neither the value of A2 nor the value of C1 changes?
-> No.
Therefore, if OFFSET() were implemented as a (slow) Basic function, the
"cells_accessed_on_last_evaluation_only" recalculate hint would provide vast
performance improvements for spreadsheets that use the function in many cells
(and it would still make sure that the function *is* reevaluated whenever
actually necessary).
The same also holds true for all the custom Basic functions involved in my
complex dynamic spreadsheet mentioned above... In fact, I guess that being able
to set this recalculate hint might actually reduce the "recalculating..." lag
upon changes to that sheet from "always ~3 seconds" to maybe "0 up to ~0.3
seconds" or so (an improvement of factor 10 to infinity ;-) ).
(In order to do implement this feature, however, Calc would need to be able to
track all read access that a Basic function internally makes to the cells of a
spreadsheet... is this feasible?)
---------------------------------------------------------------------
Please do not reply to this automatically generated notification from
Issue Tracker. Please log onto the website and enter your comments.
http://qa.openoffice.org/issue_handling/project_issues.html#notification
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscr...@sc.openoffice.org
For additional commands, e-mail: issues-h...@sc.openoffice.org
---------------------------------------------------------------------
To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org
For additional commands, e-mail: allbugs-h...@openoffice.org
