Apache OpenOffice Community Forum

Damjan Jovanovic is asking on the dev mailing list for suggests about changing the Find & Replace dialog in Calc. He has fixed a couple of bugs already and wants to collect suggestions while the code is fresh in his mind. His message was sent about four hours ago and he proposes several possibilities.

Some options are preserved during the session, others permanently, some options are not preserved at all.
Regex is saved permanently. Why?
Current selection is not saved at all. LO checks "current selection" when more than a single cell is selected. This makes a lot of sense because when I select one or more ranges before calling the Find&Replace command then I want to search in the current selection.
And I don't like the [More Options] option in this dialog and many others. Showing all the options is not too much overload, particularly when you can not be sure which option is preserved.

Villeroy wrote:LO checks "current selection" when more than a single cell is selected. This makes a lot of sense because when I select one or more ranges before calling the Find&Replace command then I want to search in the current selection.

+1
Initialize Current Selection to on if more than one cell is selected, else off, similar to the way that Writer works with text selection.

Villeroy wrote:And I don't like the [More Options] option in this dialog …

+1
Eliminate More Options in this dialog. I always have to expand the dialog since it is easy to forget which options might have been preserved from a previous Find & Replace. My keyboard shortcut is: ⌘F (Find & Replace) ⌘O (More Options).

FJCC wrote:Damjan Jovanovic is asking on the dev mailing list for suggests about changing the Find & Replace dialog in Calc.

Search In only has three choices: Formulas, Values, Notes. Use radio buttons for the choices instead of a dropdown.

I agree that the "More Option" options should be brought forward to the main F&R screen, but in interest of uniformity, that is a change that ought be applied across all OO applications.

MrProgrammer wrote:Search In only has three choices: Formulas, Values, Notes. Use radio buttons for the choices instead of a dropdown.

And there is the extra option to search Styles which also excludes Formulas, Values, Notes. I'm not 100% sure but I think there could be 4 search modes Formulas, Values, Notes and Styles. I'm convinced that these should remain exclusive to each other.

Hi

I am the developer who was asking. Thank you for your comments so far. "Current selection only" is now checked when the "Find & Replace" dialog is opened with cells already selected. Here were my other questions:

Visibility of options. In a comment on #87032 it is suggested the dialog should always be expanded so all options are visible, but #39453 is all about trying to shrink it as it hides cells under it. #39453 was filed in 2004 when monitors were smaller and had lower resolutions, so dialog size mattered more, but small devices are becoming ubiquitous today too. If the dialog should remain expandable, which options should be visible with it contracted? Comments in #35093 propose including "Current selection only" and "Regular expressions", although I think the "Search in" is far more commonly used than "Regular expressions".

Search in what? #106857 wants the default to be "Values" instead of the current "Formulas", but Excel (2007) uses "Formulas", so it might surprise users even though it is more natural.

Columns or rows? #106857 again wants "Columns" as default even though Excel (2007) has rows.

Use the value of the cell under the cursor as the find text? #73544 asks for this. Calc currently uses the last value searched for instead, as does Excel (2007). Instead of the cell under the cursor, we could use a single selected cell instead: it is rather hard to select only 1 cell by accident (with the cursor on it, you have to Ctrl+Click it or Shift+Right+Left), and you wouldn't need to search just 1 cell, so I think it's safe to treat a single selected cell as the find text. Another AOO innovation?

Selection during the dialog? If cells are selected during the dialog, should we automatically check the "Current selection only" box? We currently uncheck it and gray it out if cells are deselected during the dialog, but only enable it without checking it if cells are selected again. Excel (2007) doesn't even have this option: if cells are selected only they will be searched/replaced, if no cells are selected all will be. If we want to copy Excel's behaviour then we should check it on reselection. But Writer doesn't do that for multi-line text selections either, so it should probably be changed too (and other apps?).

In the absence of any broad based information about how the dialog is actually used, I would hesitate to change any of the default behavior. The one change of that type that really makes sense to me is setting Current Selection Only when multiple cells are selected. However, I think the setting would have to be visible in the dialog by default. I would be fine with having the More Options section open by default. If the amount of screen space taken up by that is a big concern, what about moving Current Selection Only and Search In into the part of the dialog that is visible by default? I suspect Regular Expressions are not used by most users.

I'd like to mention a small bug which occurs in OOo 3.2 though it may have been fixed in later releases. Create a sheet with X in call A1 and all other cells empty. Make A1 the active cell, for example by clicking. A search for X now fails: Search key not found! Find All will select the cell, but then Find will again report Search key not found. If the bug still exists, this would be a good time to investigate it.

I hardly ever use Search In = Values because it has the side effect, during Replace, of converting the cell's formula to it's value. I have on very rare occasions used it for a Find All operation to select relevant cells. I would prefer Formulas to remain as the default.

I like the idea of using a single selected cell as the default Find value, though I am used to achieving the same thing with Copy/Paste, using F2 to access the formula if that's what I want.

Some might be interested in a new Search In option: Formatted Value. This could, for example, find the "$" in a cell formatted as currency for English (USA) locale or the month abbreviation in a date formatted as YYYY-MMM-DD. Another Open Office innovation? Replacing formatted values would presumably go through the standard data entry routines that are used when values are typed by the user. As a programmer myself, I can imagine that this is the type of idea which may be more difficult to implement than it first seems, so I'll leave this suggestion up to the development team.

MrProgrammer wrote:Some might be interested in a new Search In option: Formatted Value. This could, for example, find the "$" in a cell formatted as currency for English (USA) locale or the month abbreviation in a date formatted as YYYY-MMM-DD. Another Open Office innovation? Replacing formatted values would presumably go through the standard data entry routines that are used when values are typed by the user. As a programmer myself, I can imagine that this is the type of idea which may be more difficult to implement than it first seems, so I'll leave this suggestion up to the development team.

I already fixed that in SVN trunk, it should work in the 4.2.0 release. It was definitely not "difficult to implement": it was a one line patch :

r1712367 | damjan | 2015-11-03 20:44:21 +0200 (Tue, 03 Nov 2015) | 11 lines
Changed paths:
M /openoffice/trunk/main/sc/source/core/data/table6.cxx
A /openoffice/trunk/test/testgui/data/bvt/searchFormulasValues.ods
M /openoffice/trunk/test/testgui/source/bvt/gui/BasicFunctionTest.java
M /openoffice/trunk/test/testgui/source/testlib/gui/UIMap.java

#i60307# search for strings in date formats fails.
When "Search in" is set to "Values", search the formatted values
instead of just the raw input. This fixes a 9 year old
bug that's been duplicated at least 6 times, and matches what Excel
does. Tests show even "Replace" works, and works well: if
"October" is replaced by "November", it converts the original
month from 10 to 11; it only converts the cell type to text if the
resulting text is no longer a valid date. Also added BVT tests
for the expected behaviour.

dacha wrote:... I already fixed that in SVN trunk, ...

Nice! I always wondered why Calc wasn't searching the values as displayed.

Thank you!

Search in what? #106857 wants the default to be "Values" instead of the current "Formulas", but Excel (2007) uses "Formulas", so it might surprise users even though it is more natural.

Just because Excel does it doesn't make it right. If Excel included a vicious virus in it's code would you do the same?

I need to search in values far more often than I do in formulas (I use replace more often than find when editing formulas) and the default from the search box (not the F&R dialogue) should ALWAYS be to search in values not formulas.

How about adding a dialogue that allows the user to set these defaults up in accordance with how they use the software instead of just assuming that you, the developer, know best?

It would be nice if the More options setting was remembered during the session so we don't have to use it more than once.
The Current selection checkbox isn't necessary at all; just assume it if more than one cell is selected.