Searching for a Keyword in a Range and Highlighting It

[dipique]

I have a Word VBA macro which has pulled data from a word document and populated an excel spreadsheet. So at this point I have:

XLSheet, the Excel Worksheet I've been using to manipulate Excel
sRange, the string containing the range I'd like to now work with ("D:D", as it happens)
sKeyword, the keyword I would like to search for and highlight within the range


The function I have so far:

'Find keywords in a range and set them to red and bold
Sub HighlightKeyword(XLSheet As Object, sRange As String, sKeyword As String)
'As long as the word isn't blank
If sKeyword <> "" Then

'Loop through the range for each cell
Dim r As Range
For Each r In XLSheet.Range(sRange).Cells

'And, using the font property of the selected keyword within the cell,
With r.Characters(Start:=InStr(1, r.text, sKeyword), Length:=Len(word)).Font

'Set to bold and red
.Bold = True
.ColorIndex = wdRed
End With
Next r
End If
End Sub


And the error I'm getting:


Compile Error: Named Argument Not Found
(Note: the "Start" parameter is highlighted when the error occurs.)

The goal is to loop through each cell in the range and, if the keyword exists, make it red and bold.

Help?

Dan

[Frosty]

You probably need to dim your r as Object rather than Range. Otherwise you're trying to loop through the collection of Word.Range objects contained in the Worksheet.Range.Cells collection.

I'm not as familiar with Excel objects, but since both Excel and Word have "Range" objects, I'm guessing the problem is contained in your use of an unexplicit range object.

[dipique]

You were right, of course. And I almost crashed Excel because it was trying to loop through every row in "D:D". :p

Now I'm having an issue with it highlighting the correct number of letters, but in the incorrect location (the first five characters rather than the actual word). I'll poke around and post again tomorrow if I can't figure it out.

[Frosty]

I would record the macro in Excel, figure out how to have it execute correctly, and then do it inside a With oExcelApp block within the Word VBA. That's generally how I approach object models I'm not as familiar with when dealing with them from another app.

Alternatively, you could look at Early Binding instead of Late Binding. I'm assuming you're familiar with the concept, so I'll just describe the process.

In the VBA IDE, with your macro project selected, go to Tools > References and add in the Micorosoft Excel 14.0 Object Library.

You'll then be able to reference r as Excel.Range instead of Object. That could shorten the development cycle... and then if you want to switch to Late Binding later (referencing as generic objects and removing the reference to the Excel object library), you can do that to "slim down" your project.

[dipique]

I used early binding and it worked without a hitch. I didn't even change the code logic.

Argh.

Thanks again. The final code that worked:

'Find keywords in a range and set them to red and bold
Sub HighlightKeyWord(XLSheet As Object, SelRange As String, Keyword As String)
'As long as the word isn't blank
If Keyword <> "" Then

'Loop through the range for each cell
Dim r As Excel.Range
For Each r In XLSheet.Range(SelRange).Cells

'And, using the font property of the selected keyword within the cell,
With r.Characters(InStr(1, r.text, Keyword, vbTextCompare), Len(Keyword)).Font

'Set to bold and red
.Bold = True
.Color = vbRed
End With
Next r
End If
End Sub

Dan

[dipique]

Oh! Never mind, I found the problem. When I re-wrote the code with early binding, I remembered to add "vbTextCompare" to the InStr function rather than using the default, vbBinaryCompare. Worked like a charm.

I also (by happenstance) used the color property because, hey, I had a context menu. This works great, unlike the "ColorIndex" property I was using previously. I applied this learnin' to my late-bound macro and it's perfect. Here's the code I'm using:

'Find keywords in a range and set them to red and bold
Sub HighlightKeyword(XLSheet As Object, sRange As String, sKeyword As String)
'As long as the word isn't blank
If sKeyword <> "" Then

'Loop through the range for each cell
Dim r As Object
For Each r In XLSheet.Range(sRange).Cells

'And, using the font property of the selected keyword within the cell,
With r.Characters(Start:=InStr(1, r.text, sKeyword, vbTextCompare), Length:=Len(sKeyword)).Font

'Set to bold and red
.Bold = True
.Color = vbRed
End With
Next r
End If
End Sub


Dan

[dipique]

Now I'm just muddying the waters, but I made one more change as the above code doesn't handle well if the keyword doesn't exist in the cell being searched. One more revision:

'Find keywords in a range and set them to red and bold
Sub HighlightKeyword(XLSheet As Object, sRange As String, sKeyword As String)
'As long as the word isn't blank
If sKeyword <> "" Then

'Loop through the range for each cell
Dim r As Object
Dim iKWLocation As Integer
For Each r In XLSheet.Range(sRange).Cells

'Check if the keyword exists within the cell
iKWLocation = InStr(1, r.text, sKeyword, vbTextCompare)
If iKWLocation <> 0 Then

'And, using the font property of the selected keyword within the cell,
With r.Characters(Start:=iKWLocation, Length:=Len(sKeyword)).Font

'Set to bold and red
.Bold = True
.Color = vbRed
End With
End If
Next r
End If
End Sub

Dan

[Frosty]

I'm not 100% sure, but I suspect this may be speedier if you switch to using the excel version of the find object rather than doing string comparisons.

You are doing the analog of what you were doing in the other thread: checking each "thing" in a range for some text, and then doing something to anything you find. If, down the road, you find yourself having performance issues... I would try recording a macro in Excel and then performing a find operation, and seeing if that wouldn't be the better way to do it.

Much of the same stuff from the other thread would apply here, the only difference would be how you execute the find operation.

[dipique]

Thanks Frosty--this section completes in less than half a second even for thousands of items, so I'm not overly concerned about optimizing. :)