PDA

View Full Version : Search for whole words



mdmackillop
11-17-2005, 03:29 PM
Hi All,
In relation to this item http://vbaexpress.com/forum/showthread.php?t=6054 , I would like to add a check box "Whole words" which would check for whole words only. I can add spaces to the start and end of each search term but how can I deal with the first and last words in each cell which would then not be a match? Do I really have to start learning RegExp?
Regards
MD

Ken Puls
11-17-2005, 03:59 PM
Hi Malcolm,

I actually haven't checked your little app out yet, but for the issue at hand...

Could you not use the InStr function? If you word is in the search string, AND the previous/following character is not either a space, comma, period or NULL, then it isn't a whole word on it's own.

:dunno

mdmackillop
11-17-2005, 04:05 PM
Hi Ken,
Thanks. I'll give that method some thought.
Regards
Malcolm

malik641
11-17-2005, 04:35 PM
Hi Malcom
I would say if the new option is selected...then call this macro:

I changed your code just to highlight the whole word....
Sub ColourTextAll(Search)
Dim Cel As Variant, MyWds As Variant, Wd As Variant
Dim ItemRange As Range
Dim strPos As Long

'MyWds = Split(Search, " ")
'For Each Wd In MyWds
Set ItemRange = Range("A3:B" & [A65536].End(xlUp).Row())
For Each Cel In ItemRange
strPos = InStr(1, Cel, Search)
If strPos <> 0 Then
Cel.Characters(Start:=strPos, _
Length:=Len(Search)).Font.ColorIndex = 7
End If
Next Cel
'Next Wd
End Sub
This seemed to work with the workbook you supplied. I hope this is what you were looking for (probably not, I kinda rushed...:nono)

Ken Puls
11-17-2005, 04:43 PM
Hi Malcolm,

Here's what I was thinking:

Sub AmIAWord()
Dim sWord As String
Dim sSearch As String
Dim sNextChar As String
Dim lStart As Long
Dim lEnd As Long

sWord = "Quick"
sSearch = ActiveCell.Value

lStart = InStr(1, sSearch, sWord, vbTextCompare)

If Not lStart > 0 Then GoTo NotAWord

'Check if previous character is a space
If lStart > 1 Then
If Not Mid(sSearch, lStart - 1, 1) = " " Then GoTo NotAWord
End If

'Check if following character is a space or punctuation
sNextChar = Mid(sSearch, lStart + Len(sWord), 1)
If Not InStr(1, " .,!", sNextChar) > 0 Then GoTo NotAWord

MsgBox "This is a word!"
Exit Sub

NotAWord:
MsgBox "This is not a word!"
End Sub

This seems to work if you have a cell with Quick (or quick) anywhere in it. (Beginning, end, whatever. If you put quickly, though, it bongs on it.

malik641
11-17-2005, 05:48 PM
Knew I should've looked into this further....:doh:Sorry Malcom!
My code shows no use to you :mkay ...my mistake. I'll see if I can look into it more...

johnske
11-17-2005, 06:56 PM
Here's a demo that ignores additional spaces and no need to worry about leading or trailing spaces...Sub SplitA1()
Dim Demo As String, i As Long
[A1] = "now is the time for all good men to do something"
'extracts the 1st, 2nd, 3rd (etc) words separately as i = 0, 1, 2 etc
For i = 0 To UBound(Split([A1]))
If Split([A1], " ")(i) = Empty Then '< this takes care of any additional spaces
i = i + 1
Else
Demo = Demo & vbNewLine & Split([A1], " ")(i) '< " " = space deliminited
End If
Next
MsgBox Demo
End Sub:)

Ken Puls
11-17-2005, 10:49 PM
Interesting approach, John. :)

Never used the split function before... will have to keep that in mind.

Unfortunately, it won't split the word out if you have punctuation, though, which still means more wrangling. Only an issue, of course, if you want an exact word match and want to ignore punctuation.

mvidas
11-18-2005, 06:45 AM
RegEx is the way to go!

I made a RegEx word replace function a while ago, that replaces whole words in a string. It can easily be modified to return any data you need (I could have it return an array with the starting character positions if you want?). Let me dig that function out.

OK, modified:Function FindFullWords(ByVal rxFull As String, ByVal rxWhat As String, Optional _
ByVal rxIgnoreCase As Boolean = True) As Long()
Dim RegEx As Object, rxPatt As String, RetArr() As Long, i As Long, RegM As Match
Set RegEx = CreateObject("vbscript.regexp")
rxPatt = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( _
Replace(Replace(Replace(Replace(rxWhat, "\", "\\"), "^", "\^"), "$", "\$"), "*", _
"\*"), "+", "\+"), "?", "\?"), ".", "\."), "(", "\("), ")", "\)"), "|", "\|"), _
"{", "\{"), "}", "\}"), ",", "\,")
With RegEx
.Pattern = "\b" & rxPatt & "\b"
.Global = True
.IgnoreCase = rxIgnoreCase
.MultiLine = True
End With
i = 0
ReDim RetArr(0)
If RegEx.Test(rxFull) Then
For Each RegM In RegEx.Execute(rxFull)
ReDim Preserve RetArr(i)
RetArr(i) = RegM.FirstIndex + 1
i = i + 1
Next
End If
FindFullWords = RetArr
Set RegM = Nothing
Set RegEx = Nothing
End Function
Sub ExampleForMD()
Dim Arr() As Long, i As Long, iPos As Long, wLen As Long
Dim tStr As String, tempStr As String, TheWord As String
tStr = "Hi All," & vbCrLf & _
"In relation to this item http://vbaexpress.com/forum/showthread.php?t=6054 ," & _
" I would like to add a check box ""Whole words"" which would check for whol" & _
"e words only. I can add spaces to the start and end of each search term but" & _
" how can I deal with the first and last words in each cell which would then" & _
" not be a match? Do I really have to start learning RegExp?" & vbCrLf & _
"Regards" & vbCrLf & _
"MD"
TheWord = "words"
wLen = Len(TheWord)
Arr = FindFullWords(tStr, TheWord, True)
If Arr(0) > 0 Then
iPos = 1
For i = 0 To UBound(Arr)
If Arr(i) > 1 Then 'if not first word in string
tempStr = tempStr & Mid$(tStr, iPos, Arr(i) - iPos) & _
UCase$(Mid$(tStr, Arr(i), wLen))
Else
tempStr = UCase(Left(tStr, wLen))
End If
iPos = Arr(i) + wLen
Next
If iPos < Len(tStr) Then
tempStr = tempStr & Mid$(tStr, iPos)
End If
MsgBox tempStr
Else
'not found
End If
End SubMatt

johnske
11-18-2005, 07:26 AM
But isn't that effectively just doing this?Sub SplitA1()
Dim Demo As String, i As Long
[A1] = " ,,/!!.. now, . is. the /time, 'for# all..... $good &men' to''''' do something!"
With [A1]
.Replace ",", ""
.Replace ".", ""
.Replace "!", ""
.Replace "/", ""
.Replace "'", ""
.Replace "&", ""
.Replace "$", ""
.Replace "#", ""
' (etc)
End With
'extracts the 1st, 2nd, 3rd (etc) words separately as i = 0, 1, 2 etc
For i = 0 To UBound(Split([A1]))
If Split([A1], " ")(i) = Empty Then '< this takes care of any additional spaces
i = i + 1
Else
Demo = Demo & vbNewLine & Split([A1], " ")(i) '< " " = space deliminited
End If
Next
MsgBox Demo
End Sub

mvidas
11-18-2005, 07:37 AM
No, the replacements I do are in case the user's search include any regex special characters so they can be searched for without being treated as part of the pattern itself. Yours looks like it is splitting the array into words by spaces, which I'm guessing you would change the function of the loop to say 'if split([a1], " ")(i) = theword then' etc. for Malcolm's function. How would you return the starting position of the original string so he can change the text? What would you do if the user wanted to search for "whole words", or anything else with a space in it?

Also, though unrelated to the question at hand, your loop to generate the demo variable could be replaced with Demo = Join(Split(Application.Trim([A1].Text), " "), vbNewLine)Matt

johnske
11-18-2005, 07:51 AM
Sub SplitA1()
Dim Demo As String, i As Long
[A1] = " ,,/!!.. now, . is. the /time, 'for# all..... $good &men' to''''' do something!"
With [A1]
.Replace ",", ""
.Replace ".", ""
.Replace "!", ""
.Replace "/", ""
.Replace "'", ""
.Replace "&", ""
.Replace "$", ""
.Replace "#", ""
.Replace " ", " "
.Replace " ", " "
' (etc)
End With
Demo = Join(Split(Application.Trim([A1].Text), " "), vbNewLine) ' :o)
MsgBox Demo
'now look at A1 on the s/s
End Sub

mvidas
11-18-2005, 07:57 AM
I don't get it? I see A1 now having " now is the time for all good men to do something" in it.. am I missing something? :)

johnske
11-18-2005, 08:08 AM
...What would you do if the user wanted to search for "whole words", or anything else with a space in it?... Maybe I'm the one missing something here...

mvidas
11-18-2005, 08:35 AM
As an example, though I can't say why a user would want to do such a thing, if a user wanted to search for "cell value" but didn't want "cell values" returned (like a 'exact phrase, whole words' search)

To be honest, prior to this post I hadn't clicked Malcolm's link above to see that it was regarding the kbsearch entry. I thought it was his 'findword' entry, that finds words in the spreadsheet and creates a table of contents for it. I could see wanting to match exact phrase, whole words for something like that, but for the kb there really isn't a practical use for it. So yours would be fine (I'll leave the regex vs non-regex speed test for another day) if you could return the position of the matching word so he can change the color of the match.

Actually, Malcolm, you could use a variation of the function for your entire find function, if a 'WholeWords' argument was added:Function FindIt(ByVal rxFull As String, ByVal rxWhat As String, Optional ByVal _
WholeWords As Boolean = False, Optional ByVal rxIgnoreCase As Boolean = True) As Long()
Dim RegEx As Object, rxPatt As String, RetArr() As Long, i As Long, RegM As Match
Set RegEx = CreateObject("vbscript.regexp")
rxPatt = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( _
Replace(Replace(Replace(Replace(rxWhat, "\", "\\"), "^", "\^"), "$", "\$"), "*", _
"\*"), "+", "\+"), "?", "\?"), ".", "\."), "(", "\("), ")", "\)"), "|", "\|"), _
"{", "\{"), "}", "\}"), ",", "\,")
If WholeWords Then rxPatt = "\b" & rxPatt & "\b"
With RegEx
.Pattern = rxPatt
.Global = True
.IgnoreCase = rxIgnoreCase
.MultiLine = True
End With
i = 0
ReDim RetArr(0)
If RegEx.Test(rxFull) Then
For Each RegM In RegEx.Execute(rxFull)
ReDim Preserve RetArr(i)
RetArr(i) = RegM.FirstIndex + 1
i = i + 1
Next
End If
FindIt = RetArr
Set RegM = Nothing
Set RegEx = Nothing
End Function

johnske
11-18-2005, 08:52 AM
Maybe I was trying to be too subtle before, what I was pointing at was your mod (although quite neat) left a leading space whereas my original code didn't. I thought the intent was to remove spurious characters and spaces from a cell (or text box) for a kb search.

No doubt the regex solution would be faster for a large range of cells, but for a reasonably small number of words in one cell, the difference really wouldn't be noticeable.

The only things that can't be handled by the simple solution are the wildcard characters (* and ?)... I was using the msgbox as a simple demo, to clean up the entry in the cell (or text box etc) you would need something along these linesSub SplitA1()
Dim Demo As String, i As Long
[A1] = " ,,/!!.. now, . is. the /time, 'for# all..... $good &men' to''''' do something!"
With [A1]
.Replace ",", ""
.Replace ".", ""
.Replace "!", ""
.Replace "/", ""
.Replace "'", ""
.Replace "&", ""
.Replace "$", ""
.Replace "#", ""
' (etc)
End With
'extracts the 1st, 2nd, 3rd (etc) words separately as i = 0, 1, 2 etc
For i = 0 To UBound(Split([A1]))
If Split([A1], " ")(i) = Empty Then '< this takes care of any additional spaces
i = i + 1
Else
If i = UBound(Split([A1])) Then
Demo = Demo & Split([A1], " ")(i) '< no deliminiter for last word
Else
Demo = Demo & Split([A1], " ")(i) & " " '< " " = space deliminited
End If
End If
Next
[A1] = Demo
End Sub

mvidas
11-18-2005, 09:06 AM
I think we're on two different pages here.. either that or I need more coffee (wait, I need that anyways)

I am not always great at recognizing subtlety, I need bluntness :) To clean up the extra spaces, you could use just application.trim. I thought the intent was to search the cells for a whole word so he could return those and change the color of the matching word?

mdmackillop
11-18-2005, 09:38 AM
Hi All,
The intention is to add checkbox for Whole words only. At the moment, "input" will find the string in "InputBox". With wholewords checked, this would be excluded. I'll have a look at all offerings this weekend. It's the little tidying up things that take all the time!

Matt, Do I need a reference set for RegExp? I'm getting an error on RegM as Match.

mvidas
11-18-2005, 09:44 AM
Sorry Malcolm, change that to Object. I had it as Match to verify I had the .FirstIndex property correct :) You won't need the reference that way

mdmackillop
11-18-2005, 09:48 AM
Thanks Matt

Amanda1
11-18-2005, 10:53 AM
:boohoo Hi Gentlemen,

I have just been feeling very pleased with myself after having worked all day and managed to change Matts script that he wrote for me the day before yesterday, to get it to now work on other spreadsheets that I have got. For somebody who is just about clueless on this VB stuff, I thought it was a great acheivement.

Then of course I log in to see what is going on as I think this site is brilliant - only to look at your scripts and conversations. Believe me, I have just gone from being proud of myself, down to depths of misery after realising what I've managed to do today, you can all do in less than two minutes.

I'm now going to have a bath, sulk and wallow in self-pity.

Good night

Amanda

mvidas
11-18-2005, 10:58 AM
One step at a time, Amanda! The fact that you're even attempting vba is the important thing, succeeding like you did is just icing on the cake :)

Amanda1
11-18-2005, 11:30 AM
:( Um.

I've just written a reply, tried to add a smilie to it & somehow managed to end up closing all the windows hence I lost the message. Now I've got another reason to sulk! :(

Seriously, I've managed to work out how to modify your script to only check in one col, but still return multiple cols and move them between workbooks etc etc. Taken me basically the whole of today, but I've made it. What I do have problems with though is understanding what VB terms mean & then of course why and where they would be used - I'm not looking for Rocket Science, just getting the car out of the garage - do you know of a resource that I might benefit from - even if it just a list of the terms to start with.

OK, I'm feeling happier now - thanks!!!!! :dance:

Amanda PS - I'm not adding a smilie in case I lose the message again - but imagine a nice laughing one

mvidas
11-18-2005, 11:35 AM
do you know of a resource that I might benefit from - even if it just a list of the terms to start with.

Theres a great site, http://www.vbaexpress.com that can help you with your coding :)

Seriously though, the VBA help is a huge resouce to figure out what something does. If you see something you don't know what it does in a code, put the cursor in that method/property (the word after the last period "."), and press F1 to see the help for that.
If you're just wondering what goes with what, etc, press F2 from VBA to open the object browser. You click on a keyword, it tells you what can be put after that, and what it does.

mdmackillop
11-18-2005, 12:04 PM
Hi Amanda,
Have a look at this KB item and download the sample file. Johnske has generously provided a lot of his code items there. You'll need to allow access to your VB Project.
Tools/Macros/Security/Trusted Publishers/Trust Access to VB Project
Regards
MD

VBA Code Library (Attachment Includes 75 Working Code Examples) (http://www.vbaexpress.com/kb/getarticle.php?kb_id=403)

Amanda1
11-18-2005, 12:44 PM
Hi

I've downloaded the file, thank you - again. Just straight unzip etc, it opens but then gives me an error on this section of code

Application.VBE.Windows(N).WindowState = vbext_ws_Minimize

But it is not a train smash - I can get in on the VB and see everything - it looks really good for me - so no prizes for guessing what I'm going to be doing tomorrow.

Matt, thanks again - I've looked at the object browser before - shook my head and then promptly closed it - I didn't know that if you clicked it gave you an explanation - now I do, so I will be looking.

Before my dogs end up biting me because they no longer recognise me, I suppose I had better spend some time with the family - (been on my PC since 5.30 this morning).

Take care & thanks!!!!!:dialog: PS Thank you for adding the smilies to my previous note

johnske
11-18-2005, 04:30 PM
Hi Amanda,

Here's another link that's a lot of help, it's the Office 97 Visual Basic Programmers Guide http://msdn.microsoft.com/archive/en-us/office97/html/web/PCHintroH101 (most of the things there still apply to later versions). Click the links you find there and you'll find there's about 15 chapters discussing all aspects of VBA.

Another good help is to just browse through posts here, copy/paste some of the code and try to work out what's going on.

Of course, if you want qualification, VBAX offers a course in VBA...

Regards,
John :)

EDIT: There's something wrong with the link above. If you have trouble accessing it, PM me your email address and I'll send you a zipped offline copy

Amanda1
11-18-2005, 06:55 PM
Thanks. Its 3.30 am local time but there has just been such a massive storm that has woken everyone up for miles - now its over I've figured that trying to get back to sleep is a waste of time and being so taken with this site I decided to log in.

Now, I've got more pointers - there is definitely no chance of me going back to bed - So, no prizes for guessing what I'm now doing.

Thanks for your guidance - I really appreciate it!!

Cheers

Amanda
:hi: