To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=94721 Issue #|94721 Summary|Macros, slow recalculation and enableAutomaticCalculat |ion Component|Spreadsheet Version|1.0.0 Platform|PC URL| OS/Version|Linux Status|UNCONFIRMED Status whiteboard| Keywords| Resolution| Issue type|DEFECT Priority|P3 Subcomponent|programming Assigned to|spreadsheet Reported by|tyranscooter
------- Additional comments from [EMAIL PROTECTED] Tue Oct 7 03:45:12 +0000 2008 ------- I have a fairly complex spreadsheet with thousands of cells that get recalculated for each cell that is modified. Some cells include conditioal formatting (some cell backgrounds changes to red is there is a problem). Recalculation time for each change is approx 2 seconds. A macro (in basic) writes to a dozen or so cells and takes about 30 seconds to run. Most of the time goes into sheet recalculations. Each write triggers recalculation of (mostly) the same set of cells In an effort to optimize the macro I encapsulate the writes to cells in the following ThisComponent.enableAutomaticCalculation(FALSE) ... writes... ThisComponent.calculate() ThisComponent.enableAutomaticCalculation(TRUE) observations: 1) cell values are not changing while macro runs, seems as though disabling autocalculation is working 2) Some cells are blinking red during macro execution, but not the ones written by the macro... seems as though conditional formatting expressions are being recalculated (they shouldn't be) and that they are evaluating differently (they shouldn't, at least not yet) 3) macro execution time is virtually unchanged. Expected result is that it run in 2-4 seconds. In summary: enableAutomaticCalculation(FALSE) doesn't seem to be working as expected. Theories: 1) Perhaps the spreadsheet is evaluating the transitive list of all dirty cells for each write while leaving the cell values unchanged. I'd suggest that computation of the transitive list of dirtied cells be suppressed when enableAutomaticCalculation(FALSE) is in effect 2) Perhaps conditional formatting expression recalculation is not suppressed by enableAutomaticCalculation(FALSE). Further the conditional formatting recalculation somehow serving as a back door to triggering at least a partial set if cell recalculations. I'd suggest recalc of conditional formatting also be suppressed by enableAutomaticCalculation(FALSE) Questions: Is there a global on/off switch for conditional formatting? Can it be toggled from a macro? How? When are cells marked dirty? When a cell it depends on is marked dirty, or when a cell it depends on changes value? Marking a cell dirty and triggering recalculation based on actual value changes in an input would seem to be more efficient. My impression is that Excel does this, Open Office doesn't. e.g. many cells are using IF() and the inputs that are changing are in the THEN or ELSE clause not being used, so no change in resultant value, yet downstream cells seem to being recalculated anyway. --------------------------------------------------------------------- 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
