Bug 40203 - Recalculation Issue: changes in macros not recalculated.
Summary: Recalculation Issue: changes in macros not recalculated.
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Hardware: All All
: low enhancement
Assignee: Not Assigned
: 55774 (view as bug list)
Depends on:
Blocks: Calculate
  Show dependency treegraph
Reported: 2011-08-18 09:45 UTC by Olivier Hallot
Modified: 2020-07-06 08:51 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

test case for automatically updating in Calc (8.72 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-10-08 12:06 UTC, sasha.libreoffice

Note You need to log in before you can comment on or make changes to this bug.
Description Olivier Hallot 2011-08-18 09:45:13 UTC
As far as I know, recalculation is performed 

1) when cells are changed/edited, and other events related
2) on cells that depends on the changed cells.

but recalculation does not occurs when a function is changed in the spreadsheet.

It will be nice to trigger recalculation when a change in macros occurs.

For example:

Function AnyFunction( i as long) as Long

   AnyFunction = i +1

End Function

If AnyFunction is used in the spreadsheet and we chage the line

AnyFunction = I + 1 to AnyFunction = I + 10

The change is not automatically recalculated. Only by Ctrl+Shift+F9.
Comment 1 Björn Michaelsen 2011-12-23 12:35:17 UTC Comment hidden (obsolete)
Comment 2 Florian Reisinger 2012-08-14 13:58:14 UTC
Dear bug submitter!

Due to the fact, that there are a lot of NEEDINFO bugs with no answer within the last six months, we close all of these bugs.

To keep this message short, more infos are available @ https://wiki.documentfoundation.org/QA/NeedinfoClosure#Statement

Thanks for understanding and hopefully updating your bug, so that everything is prepared for developers to fix your problem.


Comment 3 Florian Reisinger 2012-08-14 13:59:31 UTC Comment hidden (obsolete)
Comment 4 Florian Reisinger 2012-08-14 14:04:07 UTC Comment hidden (obsolete)
Comment 5 Florian Reisinger 2012-08-14 14:06:20 UTC Comment hidden (obsolete)
Comment 6 sasha.libreoffice 2012-10-08 12:06:39 UTC
Created attachment 68251 [details]
test case for automatically updating in Calc
Comment 7 sasha.libreoffice 2012-10-08 12:07:15 UTC
reproduced in 3.6.2 on Fedora 64 bit
Comment 8 Regina Henschel 2012-10-09 22:33:17 UTC
*** Bug 55774 has been marked as a duplicate of this bug. ***
Comment 9 Joel Madero 2012-11-21 17:41:49 UTC
Verified, marking as NEW and prioritizing:

Enhancement: This is behaving as it's intended to but your solution does seem to be a good enhancement

Low: Number of users using macros is very low, easy work around (manually updating using the keys you said), and only happens under specific conditions (changing a macro after it's already being used in a sheet)

Thanks for your patience and your help in reporting. 

Comment 10 b. 2019-12-16 09:22:22 UTC
imho it's not a good idea to apply changes automatically. 

while creating macros and functions a little more complex than the sample by 'trial and error' usually plenty errors evolve before success. this would lead to plenty ruined sheets where it's difficult for the user to go back and find a healthy version. 

if he's informed that a 'hard' recalc is needed he can apply that quite simple when editing is finished. 

may be it's good to apply a hint 'function in use in sheet, recalc by ctrl-shift-F9 required' into the editor as it is for subs with 'you'll have to restart the program after this edit'.
Comment 11 b. 2019-12-29 14:11:00 UTC
as of today and with ver. recalculation for a cell respecting the changed function can also be triggered by hitting F9 while the cell is selected (while it is the 'focus'), 

other cells using the same function are not! recalculated, they still need ctrl-shift-F9
Comment 12 b. 2020-07-06 08:51:44 UTC
after some re-think ... 

forget comment #10: 
1. sheets have to be responsive, 
2. sheets have to be reactive, 
3. sheets have to be consistent, 
4. sheets may not carry traps which produce 'later errors', thus each change must be immediately reflected in the workbook while autocalc is on, 
5. against the risk of messing up things while experimenting with macros users have to use backups, 

regarding 'Low: Number of users using macros is very low ...' from c#9: perhaps it would be more users if macros were a bit less complicated and a bit more reliable? 

thus it would be a good idea to include used functions! into the 'dependency chain'? and trigger recalculation on changes, (macros in opposite have their trigger and are intentionally only calculated when triggered?), 

there is some (not good working, it omits some pasting actions?) functionality in the macro editor which interrupts edits with dialogs 'you have to restart after that change' or similar, perhaps that can be used to set a recalc trigger for the sheet / cells / functions? 

complications: functions are not bound to the file, thus it may happen that a sheet is opened on a different system with different functions called by the same name? that ... is too complex for me at the moment ... forced recalc on open with user dialog 'functions results have changed, perhaps reg. different system, pls. check results in cells [list of cells with functions where values changed on recalc]'?