Description: a long standing issue are #REF! errors which are not detected and fixed immediately. patch against master attached! It's an enhancement, but could also be qualified as a bug as a 'REF! error obviously destroys the logic without a warning. Steps to Reproduce: 1. delete a row, column, sheet with a referenced cell Actual Results: delete a row, column, sheet with a referenced cell - missing feed back Expected Results: get a warning to undo the malicious operation Reproducible: Always User Profile Reset: No Additional Info: my first contribution - used gemini to get it running - no prior experience in coding c++
Created attachment 205205 [details] adds a red infobar if #REF! error is detected and removes it on undo IMO this enhancement will save many hours necessary to fix broken sheets as inserting #REF! into the formula removes the name of the cell which was deleted. Especially if a team (collabora !!) or a successor works on a sheet the reengineering is cumbersome, often even for the author of the sheet. It's often not possible to find the person who broke the sheet as "I didn't do it" is the usual answer, which I understand as the software didn't give any feedback.
Looks like a reasonable information in particular if the reference is on another sheet. I'd have a few remarks on the code essentially where to store the strings, the Undo button, how to deal with the old count, and there are some unnecessary comments. But better to do so on Gerrit: https://wiki.documentfoundation.org/Development/gerrit And I'd be afraid of performance issues. You iterate through all sheets and all cells on every key stroke. That's surely not a good idea.
Thank you for looking into it. As I said - zero experience in c++ hence the code is not clean (but works) and I do not know where to search to optimise the code - btw I am 74 and it's unlikely that I will do another contribution - so my desire to learn c++ and the internals of libreoffice is low. The code as it is is the result of a 3 hours trial and error based on gemini proposals. And my tool is vi.... I never used gerrit Yes I can remove the comments and beautify the code once the idea to proceed is confirmed. Re Performance: I tested on 500k-1MB xlsx with 10-20 tabs - no noticeable performance issue on a M1 macbook whereas "repair time" account easily for hours usually not available when the #REF! error is detected. If performance is really becomes an issue, this feature could/should be made configurable for very sheet.
Well, first, congratulations on producing a useful patch inside our rather baroque codebase :-) Sadly, I can't think of any simple way of making this perform fast, and I cant see that doing this on every keystroke is going to be acceptable (but I do not have the last word).
You could check only on column/row deletions (wouldn't go as far as deletion of a cell content). To approach this I'd look for the UNO command (Tools > Customize comes in handy), search for the internal SID* variable being linked to the command, and check the exec() code for this variable. You probably find some dedicated function, Calc has a rather nice code base, and could add the function there.
Not sure what it would take, but it would be better to somehow hook into the creation of the error rather than iterating through all sheets and cells to find #REF!
(In reply to Heiko Tietze from comment #5) > You could check only on column/row deletions (wouldn't go as far as deletion > of a cell content). Deletion of the _content_ of a cell does not trigger #REF errors in spreadsheet tools. Deletion of cell(s) themselves does, or the sudden "disappearance" of any kind of address that is already being used/referenced somewhere in an active/open spreadsheet (e.g. delete column, row, worksheet, named range... that is already referenced in some formula...).
(In reply to Buovjaga from comment #6) > Not sure what it would take, but it would be better to somehow hook into the > creation of the error rather than iterating through all sheets and cells to > find #REF! that was my first approach, but not successful. primary target was. source/core/tool/interpr2.cxx: ValidateRef( *pRes); // set #REF! if needed other candidates source/core/tool/compiler.cxx: aRef.Ref1.SetTabDeleted( true ); // #REF! source/core/tool/compiler.cxx: aRef.Ref2.SetTabDeleted( true ); // #REF! source/core/tool/chgtrack.cxx: // Therefore set everything to #REF! I didn't manage neither to catch the error nor to pass it to the gui.
a new trial respecting the performance aspect - hopefully
Created attachment 205293 [details] performance optimisation low level catch of #REF! error
(In reply to Ferdinand from comment #10) > Created attachment 205293 [details] > performance optimisation low level catch of #REF! error just discovered - undo of delete sheets breaks the undo function works for delete row/column
Can not proceed debugging here, because during debug I realised that it's a general bug which might prohibit that this patch works correctly. pls see https://bugs.documentfoundation.org/show_bug.cgi?id=170557
Created attachment 205333 [details] performance issue addressed (hopefully) please review if this is the right way to respect performance issues