Created attachment 198583 [details] Case1: KO I initially had a performance issue in a sheet document. After some analysis, I found that it was due to macros being re-evaluated when applying a filter. I created a much simpler reproduction case. In the reproduction files, the macro only opens an information dialog and returns a static value. Steps to reproduce: - Open one if the attached files - Enable the macro - During the opening, there are several information dialogs open, click on OK (there are useful to understand the problem in following steps). - On column C, filter to keep only "false" value - Expected: No information dialog is displayed. The macro has already been applied, so the result is here and there is no reason to compute again. - Observed: On "*_KO.ods" files, some information dialogs are displayed. This clearly shows that the macros are being executed again. The behavior is wrong, ie macros are called again, when there are at least the same macro twice one below the other.
Created attachment 198584 [details] Case2: OK
Created attachment 198585 [details] Case3: KO
Created attachment 198586 [details] Case4: OK
Created attachment 198587 [details] Case5: OK
Created attachment 198588 [details] Case6: KO
For your information, I use a workaround for my own case. I created a second macro that is just an indirection of the first one. And I called macro1 and marco2 alternately: - Line1 : macro1 - Line2 : macro2 - Line3 : macro1 - Line4 : macro2 - Line5 : macro1 - ... With this workaround, macro1 and macro2 are no longer called when I change the filter status.
This is about optimizing evaluation (maybe double check the meaning of "deterministic"), so not a bug, but an enhancement request ? your point is to avoid "hard recalculate" ? macros and filters are just for the demo ? For the context : https://bugs.documentfoundation.org/show_bug.cgi?id=100475#c14 ;)
We can consider it as an enhancement request as it is a performance problem. But since I couldn't figure out the logic between my different example cases, I consider it a bug instead. > your point is to avoid "hard recalculate" ? Yes, in my example there is no reason to "hard recalculate" on the filter application. > macros and filters are just for the demo ? Yes and no: This is my initial problem. I reduce it to have simpler cases.
maybe double check what volatile functions are; for example https://wiki.documentfoundation.org/Documentation/Calc_Functions/RAND and https://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1017870_715980110 from there, try to formally characterize what the actual enhancement could be; otherwise, would rather be RESOLVED - NOTABUG
(In reply to Laurent Redor from comment #8) > We can consider it as an enhancement request as it is a performance problem. > But since I couldn't figure out the logic between my different example > cases, I consider it a bug instead. I reproduced it on version 25.2.3.2. I also did more extensive testing, editing the attached files, so now I can add new informations. I found out that your condition "when there are at least the same macro twice one below the other" is not exact. More precisely, the recalculation occurs when two cells, one below the other, contains the same *expression* calling your macros. The expression =MYMACRO1($B$2:$B$4)+0 is not same as =MYMACRO1($B$2:$B$4), you can use it as another workaround. My tests demonstrate that recalculation *has* a deterministic reason: the argument that you used calling your macros. Indeed, the range $B$2:$B$4 belongs to the table you are filtering. When filtering, if the filter hides/reveals some cells belonging to $B$2:$B$4, the expression is recalculated, else is not recalculated. The fpy's link, about OpenFormula standard, brings me to suppose that Calc normally checks, after each filtering, if a recalculation is needed: when a formula contains references to cells that are changing their visibility status, the expression is recalculated. This "filter dependency" is not expressly mentioned in the "3.5 When recalculation occurs" section of the standard. But it is explainable, because the cell's visibility can change the result of a function, as happens with SUBTOTAL(). Therefore IMHO filter recalculation is normal. On the contrary, I expected it happens more frequently, not only when an expression is written twice on consecutive cells of the same column. But that would be another story.