Created attachment 53611 [details] A simple example used to discribe the problem (when changing A1 value). Problem description: Using a macro function returning an array in one of the spreadsheet works (using Ctrl+Maj+Enter as usual for such an input). But when the spreadsheet updates the value of the resulting cells, the function is called (you can use the debugger) BUT the resulting cells don't change. Steps to reproduce: 1. Open "TestMacroFunctionAsArray.ods" attached. 2. with authorized macro for this file 3. Change A1 with "1" Current behavior: A1, F2 and D2 update their value to 1, 2, 1 B1, C1 don't update (and so D3 and D4) Expected behavior: B1 might update to 2 (and so D3) C1 might update to 3 (and so D4) Platform (if different from the browser): Browser: Mozilla/5.0 (Windows NT 6.0; rv:8.0) Gecko/20100101 Firefox/8.0 Remark : F2 updates its value (so value resulting from a user Function works fine). D2 updates its value (so value resulting from array computation works fine). If you save the document nothing change. But, then, if you reload it B1 and C1 (and so D3 and D4) update.
Reproducible in 3.3.4 and 3.5.2 on Fedora 64 bit Cell F1 contains call to Basic function that returns ordinary (scalar) value. It updates after press F9. Cells B1,C1 contain call to Basic function that returns array value. It not updates after press F9.
Doesn't work with "LibreOffice 3.5.6.2" Version ID : e0fbe70-5879838-a0745b0-0cd1158-638b327
Thanks for additional testing "Version" is most old version of LO where bug appeared. Not current version. Changing back to 3.3.4
I could reproduce this problem using LibreOffice Calc Version: 4.2.4.2 Build-ID: 63150712c6d317d27ce2db16eb94c2f3d7b699f8. More to the story see in comment 3 of bug 79027. Note: The example file (attachment 53611 [details]) has automatic recalculation turned off and therefore shows no automatic updates unless this is turned on. The description refers to a cell "F2", I think this should be "F1".
Created attachment 101117 [details] Another test case (change yellow cells; blue cells changed only after save+reopen) I make a heavy use of Basic macros and I also have big problems with recalculation: it recalculated what is not needed, and updated values are not always displayed. (I also reported a possibly related bug here: https://bugs.freedesktop.org/show_bug.cgi?id=79770 ) I am using: LibreOffice Version: 4.2.4.2 Build ID: 63150712c6d317d27ce2db16eb94c2f3d7b699f8 On a PC running Linux Ubuntu 32 bits: Linux ... 3.13.0-30-generic #54-Ubuntu SMP Mon Jun 9 22:47:59 UTC 2014 i686 i686 i686 GNU/Linux In the test case: * the yellow cells may contain something or be empty * "Reference" contains a formula that uses the yellow range * "Formula" contains the text of the formula * "Address" contains the address of the yellow range * "reference coordinates" are numbers for getting the location of the yellow range * the blue cells are the matrix result of the macro the uses the coordinates. They indicate the location of the non-empty cells I have displayed the cells on which depends each calculated cell. What should happen: - when I write or delete a cell in the yellow range, the blue cells should change - the macro should be run when I modify a cell in the yellow range, not when I modify an unused cell What happens in the results: - the result is GOOD when I open the file - if I modify the yellow range, the blue cells are not updated - if I save and reopen the file, the blue cells are GOOD What happens with the macro (setting a breakpoint in the Basic code): - whenever I modify a cell (even an used one), the macro is run - at the end of the code, the result is good (updated value) - but the new matrix is not displayed in the spreadsheet
Remark with the test case just attached: - when I do "Recalculate" (F9) or "Unconditional recalculate" (Ctrl-Shift-F9), the blue cells are not updated. - when I do Ctrl-Shift-F9, the macro is run (but cells not updated) - Save + reopen is the only way to update the values
Created attachment 101507 [details] New test case: modify A1 and read remarks within Sorry, previous test case was incorrect, I just marked it obsolete. Initial bug is still there, in 4.2.4.2 and in 4.2.5.2. Tested on Linux Ubuntu i386 32 bits. I added some comments on the spreadsheet in order to make the test case easy to understand. It is an extremely simple example.
Bug still there in Version: 4.3.0.1 Build ID: 9ed0c4329cf13f882dab0ee8b9ecd7b05e4aafbb (try to open the last attachment: it is much easier to understand)
Still there in LibreOffice 5.0.0 It makes all the "macros" system basically unusable (and notably my accounting software). Please check with this very simple and very conclusive test case (from Comment #7): https://bugs.documentfoundation.org/attachment.cgi?id=101507
Still there in LibreOffice 5.1.0 (.3) Very easy to test: open this file, with macros enabled, change the value in cell A1, and observe the comments in the spreadsheet: https://bugs.documentfoundation.org/attachment.cgi?id=101507
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.2.5 or 5.3.0 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20170306
This bug is still there in LibreOffice 5.3. Tested with LibreOffice 5.3.0.3 on Linux Ubuntu 16.04.2 LTS (Xenial). CPU = Intel(R) Pentium(R) Dual CPU T2370 @ 1.73GHz
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
The bug is still there in latest release: Version: 6.1.2.1 Build ID: 65905a128db06ba48db947242809d14d3f9a93fe Threads CPU : 2; OS : Linux 4.15; UI Render : par défaut (default); VCL: gtk2; Locale : en-IE (fr_FR.UTF-8); Calc: group threaded Also tested in LibreOffice 3.3.0 as suggested, and found that the bug was already there => I am setting the version to "inherited from OOo", as requested.
looks still buggy in 6.3.0.0.alpha0(x64)-2019-03-17, irritating - deviating - behaviour on forced recalc and save - open, imho critical, that's not how a reliable spreadsheet should work, stepping through the macro with F8 stops with 'BASIC runtime error. Argument is not optional'? tested with file from comment #10
looks still buggy in 6.4.0.1, behaviour repro, the case is somewhat simple, the functionality does! work - see file load - but is either not triggered, neither by changes in A1 (it should), nor by recalc (F9) (unsure if it should), nor by hard-recalc (ctrl-shift-F9) (it should), or works buggy?, regardless which settings you try for autocalculate, recalculate on load, threaded, CL and so on ... not even adding a new instance of this formula to another cell will work, it will be calculated with the value of A1 on load of the sheet, changes of A1 will not be obeyed until save and reload of the file, then it'll be evaluated, - an assumption ... the value to calculate the array is taken from the file, or from any cached storage, not from the actual active working copy in memory? it's an exotic use case, but a very well researched and presented bug, and old, why didn't anybody care for it?
*** Bug 129896 has been marked as a duplicate of this bug. ***
still buggy in 7.1.0.0.a0+, https://bugs.documentfoundation.org/show_bug.cgi?id=129896 shows another flavour of this bug, a call to the function used somewhere else on the sheet doesn't take it's own parameters but calculates with that taken for the first instance of the function on load of file, even if you delete everything from the sheet and put a value in a cell and try to calculate INCDINC besides it, it doesn't take the value of the referenced cell, but that what was loaded as parameter for the first used INCDINC on opening the file, resolutely persistent behaviour ... regarding "stepping through the macro with F8 stops with 'BASIC runtime error. Argument is not optional'?" from c#15: a function takes parameters, called by a formula in a cell those are passed, is there a way to get them in if you want to 'debug' the function in the 'IDE' (Basic Editor)? otherwise debugging is blocked ... :-(
after coming over "stepping through the macro with F8 stops with 'BASIC runtime error. Argument is not optional'?" thanks to a hint by @Mike Kaganski i can say that the function is getting the right parameters, and does the right calculation, e.g. INCDINC(15) calculates the result {16; 17} as it's expected, just on the last two steps handing 'rslt' over to INCDINC and presenting it in the sheet something goes wrong ... i can't see where as INCDINC can't be set as a watch variable,
Another observation about the problem: If you change the Macro IncDInc without saving the file a hard recalc works. Does LO save the file, when a macro has been changed?
After some hours of debugging, I finally found the issue of this problem. At https://opengrok.libreoffice.org/xref/core/basic/source/runtime/runtime.cxx?r=8332d6d8&mo=60334&fi=1933#1940 something goes wrong with the assignment of the final result. Imho this bug is also related to tdf#136755. I will try to come up with a patch.
After digging into the overloaded assigment operator, it seems that the problem, finally, lies in https://opengrok.libreoffice.org/xref/core/basic/source/runtime/runtime.cxx?r=8332d6d8#4178. This part of the code changes the reference to the method, resulting in changes on a different variable, which has no effect to the final result.
Andreas Heinisch committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/2538188ee85513cb80eb8aa8f925082d86ff711d tdf#43003 - convert parameter to the requested type It will be available in 7.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Andreas Heinisch committed a patch related to this issue. It has been pushed to "libreoffice-7-1": https://git.libreoffice.org/core/commit/4db748f8ce2241e500bb900c94abc74ebf8690d7 tdf#43003 - convert parameter to the requested type It will be available in 7.1.0.0.beta2. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Test redone on test file from my attachment 101507 [details] (2014-06-21 20:31:29 UTC). The test still failed for macro "Inc", which returns a scalar: if you modify cell A1, you must press F9 (Recalculate) or Ctrl-Shift-F9 (unconditional recalculate) to update its result in cell F1. The test however is fixed for macro "IncDInc", which returns a 2×1 array into cells B1:C1. I am then changing its state to "reopened". Tested with: https://dev-builds.libreoffice.org/daily/libreoffice-7-1/Linux-rpm_deb-x86_64@86-TDF/2020-12-21_17.35.36/libreoffice-7-1~2020-12-21_17.35.36_LibreOfficeDev_7.1.0.1.0_Linux_x86-64_deb.tar.gz
Very strange behaviour: If I create a new file including this macro, sometimes it instantly works, sometimes it doesn't. However, the test macro in the test file never does. All I can say that this bug is not a macro problem, because then a recalc or a hard recalc wouldn't work too. Maybe LO doesn't get the right cell type or if the cell is dirty itself.
When this problem started to appear (in 2014, in LibreOffice 4.1), like in bug 79770 or bug 81757, I had the feeling that it came from the algorithm which tells which cell must be recalculated and in which order, at least when cells or cell arrays are sent as parameters to the macro.