How to get current value of the "Find" widget/field?

[dan11]

Is it possible in Basic to put into a variable current value of the “Find” field. For example:

I’m writing a macro that appends to each occurrence of a word in a cell a string “<–” to mark it. "word" in the example below is hard-coded to the target variable, but perhaps there is some way to extract it from the “Find” field dynamically?

Sub findAndMark
Const toAppend = " <--"
Const target = "word"
Set oRange = ThisComponent.CurrentSelection
    For i = 0 To oRange.Rows.getCount() - 1             
        For j = 0 To oRange.Columns.getCount() - 1
            Set cell = oRange.getCellByPosition( j, i )
        	tc = cell.CreateTextCursorByRange(cell.Start)
			pointPos = InStr(cell.String, target)
			If pointPos>0 Then
 				tc.goRight(pointPos - 1, False)
 				tc.goRight(Len(target), True)
 				cell.insertString(tc, toAppend, False)
 			EndIf
        Next
    Next
End Sub

Heavily based on this answer in another thread

[Zizi64]

@dan11

there is some way to extract it from the “Find” field dynamically?

Fast workaround tips:

• You can use a helper cell (of the spreadsheet) as your own “Find field”…
• You can use the (the BASIC “command”) Input Box as your own “Find field”… InputBox Function

[dan11]

Thanks, I used the second option (InputBox) - it prompts you for the phrase and searches it.

Fortunately it also seems to work for more complex multiline text cells.

The script seems to work now, but it would be nice if you could just type the search phrase once in the "Find" widget and have the script triggered automatically. Now you need to do it twice:

Sub findAndUnderline	
	Dim target As String
	target = InputBox ("Search phrase")
	Set oRange = ThisComponent.CurrentSelection
	    For i = 0 To oRange.Rows.getCount() - 1             
	        For j = 0 To oRange.Columns.getCount() - 1
	            Set cell = oRange.getCellByPosition( j, i )
				REM https://stackoverflow.com/questions/35239915/find-all-strings-within-a-string
				start = 1
				Do
				  pos = InStr(start, cell.String, target)
				  If pos > 0 Then
				    start = pos + 1
					tc = cell.CreateTextCursorByRange(cell.Start)
					tc.goRight(pos - 1, False)
	 				tc.goRight(Len(target), True)
	 				tc.CharUnderline = 2
				  End If
				Loop While pos > 0
	        Next
	    Next
End Sub

[Zizi64]

@dan11

Now you need to do it twice:

Why you need it?

Just select more than one cells before you use the searching macro routine. Then the macro will mark all of the occurances in every selected cells. (the For loops handles the whole selected cell range.)

[Zizi64]

…or use the another tip: the Helper Cell can store the string what you want to search.

[dan11]

@Zizi64

Just select more than one cells before you use the searching macro routine

I assumed that you will first narrow down the selection to only those cells that contain your keyword using the Calc’s “Find” utility, because it’s probably faster/more robust (for larger sheets with many more complex types of cells and only two or three relevant to your search). I’ve tried running this script once for all selected cells, but it caused an endless loop and froze the UI.

Another approach would be to also reimplement this Cal’s Find functionality in the script… but unless there are some builtin components/services in LO already it is probably not a good idea.

[Zizi64]

@dan11

I’ve tried running this script once for all selected cells, but it caused an endless loop and froze the UI.

Not endless. It is just request very long time. There are more than one million rows on a sheet. Multiply it by the number of the number of columns: that is a huge number of the Cells. The macro must examine all of these cells one by one.

[Zizi64]

@dan11

I assumed that you will first narrow down the selection to only those cells that contain your keyword using the Calc’s “Find” utility,

Why not enough for you to use the built in Find and Replace function manually?
.
.

Find: "dinos" (without the quotes)
Replace: "___&___" (without the quotes)

More options: use the Regular expressions
Replace all
.
The “&” means: “put the same string back”. Use Non-Regular Expression characters to mark the actual word. The underscore character is not a Regular Expression.
https://help.libreoffice.org/6.2/en-US/text/shared/01/02100001.html
.
You can use the Undo to eliminate the “marks”. Or you can use the F&R to eliminate the marks:
.

Find: "___" (without the quotes)
Replace: "" (nothing, without the quotes)

[dan11]

@Zizi64

Why not enough for you to use the built in Find and Replace function manually?

huh, I haven’t thought about it. You are right, you could just use find and replace for this purpose. It also allows you to undo it with a single click when using “Replace All”. That would have saved me quite some time…

@Zizi64

The “&” means: “put the same string back”.

Thank you, very useful!

I don’t suppose that there is some way to replace with formatting? For example to underline or change color of the sought keyword? It’s not a major problem, but the recent script also underlined the keyword making it stand out more (it also preserved the existing formatting).

[Zizi64]

Forget the managing the partial formatting of the cell content. It is not an efficient method.