Bug 43003 - Calc EDITING: Cells, containing call to Macro Function as Array doesn't update, even if press F9
Summary: Calc EDITING: Cells, containing call to Macro Function as Array doesn't updat...
Status: REOPENED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: Other All
: medium major
Assignee: Not Assigned
URL:
Whiteboard: BSA target:7.2.0 target:7.1.0.0.beta2
Keywords:
: 129896 (view as bug list)
Depends on:
Blocks: Macro-UI
  Show dependency treegraph
 
Reported: 2011-11-16 14:31 UTC by Abstro
Modified: 2021-01-11 16:37 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
A simple example used to discribe the problem (when changing A1 value). (8.80 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-11-16 14:31 UTC, Abstro
Details
Another test case (change yellow cells; blue cells changed only after save+reopen) (20.73 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-06-15 19:28 UTC, Miguel
Details
New test case: modify A1 and read remarks within (24.47 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-06-21 20:31 UTC, Miguel
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Abstro 2011-11-16 14:31:48 UTC
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.
Comment 1 sasha.libreoffice 2012-04-20 23:13:09 UTC
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.
Comment 2 Abstro 2012-09-04 19:57:08 UTC
Doesn't work with "LibreOffice 3.5.6.2"
Version ID : e0fbe70-5879838-a0745b0-0cd1158-638b327
Comment 3 sasha.libreoffice 2012-09-05 05:19:29 UTC
Thanks for additional testing
"Version" is most old version of LO where bug appeared. Not current version.
Changing back to 3.3.4
Comment 4 Albrecht Müller 2014-05-29 16:08:52 UTC
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".
Comment 5 Miguel 2014-06-15 19:28:44 UTC
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
Comment 6 Miguel 2014-06-15 19:32:28 UTC
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
Comment 7 Miguel 2014-06-21 20:31:29 UTC
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.
Comment 8 Miguel 2014-06-26 19:32:56 UTC
Bug still there in Version: 4.3.0.1
Build ID: 9ed0c4329cf13f882dab0ee8b9ecd7b05e4aafbb
(try to open the last attachment: it is much easier to understand)
Comment 9 Miguel 2015-08-09 17:12:27 UTC
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
Comment 10 Miguel 2016-02-21 20:24:22 UTC
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
Comment 11 QA Administrators 2017-03-06 15:40:26 UTC Comment hidden (obsolete)
Comment 12 Miguel 2017-03-06 16:41:24 UTC
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
Comment 13 QA Administrators 2018-10-22 02:48:52 UTC Comment hidden (obsolete)
Comment 14 Miguel 2018-10-22 20:00:08 UTC
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.
Comment 15 b. 2019-03-17 10:57:58 UTC
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
Comment 16 b. 2019-12-27 11:59:01 UTC
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?
Comment 17 Xisco Faulí 2020-02-17 14:42:00 UTC
*** Bug 129896 has been marked as a duplicate of this bug. ***
Comment 18 b. 2020-07-07 08:14:40 UTC
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 ... :-(
Comment 19 b. 2020-07-25 11:35:10 UTC
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,
Comment 20 Andreas Heinisch 2020-11-30 18:40:00 UTC
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?
Comment 21 Andreas Heinisch 2020-12-03 10:12:08 UTC
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.
Comment 22 Andreas Heinisch 2020-12-04 15:02:14 UTC
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.
Comment 23 Commit Notification 2020-12-07 15:10:53 UTC
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.
Comment 24 Commit Notification 2020-12-07 18:32:44 UTC
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.
Comment 25 Miguel 2020-12-22 13:52:42 UTC
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
Comment 26 Andreas Heinisch 2020-12-31 12:54:23 UTC
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.
Comment 27 Miguel 2020-12-31 13:17:11 UTC
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.