Inspired by Dick Kusleika’s “Inflexible Find” I decided to add some flexibility to it. In a comment to his original post (http://www.dailydoseofexcel.com/archives/2011/02/10/vbe-find/#comment-58902), I indicated that the userform was now shown modeless. This meant that one could interactively search for a token and examine the associated code.
Another improvement I wanted to make was a ‘find whole word’ capability. And, as always, I wanted to do it with minimal impact on the existing code, which, in turn, meant minimal testing. Luckily DK’s coding approach works well for me.
For obvious reasons, I decided to use a Regular Expression object. It requires a reference to ‘Microsoft VBScript Regular Expressions’ library (in the VBE, Tools | References…)
I added a checkbox named FindWholeWord to the userform.
Next, I added a function in the userform’s code module to check if a string token exists inside another string.
ByVal SearchFor As String, _
Optional IgnoreCase As Boolean = True) As Boolean
Static X As RegExp: If X Is Nothing Then Set X = New RegExp: X.Global = True
X.IgnoreCase = IgnoreCase
X.Pattern = SearchFor
RegExpFind = X.Test(SearchWhat)
End Function
Then, I had to make only minimal changes to the code in the tbxFind_Change routine, replacing
with
sFindWhat = Me.tbxFind.Text
If Me.FindWholeWord.Value Then sFindWhat = “” & sFindWhat & “”
and
with
If RegExpFind(CM.Lines(I, 1), sFindWhat, True) Then
Commenting out the original code leaves an easy fallback to functioning code just in case there’s a problem with the new code.
Finally, I added an event procedure so that a change to FindWholeWord would trigger the search.
tbxFind_Change
End Sub
While I did test the changes, I was fairly confident I didn’t have to.
I also laid the groundwork to add ‘case matching’ through a trivial extension to the userform: add a checkbox named, say, MatchCase, and replace the ‘True’ in above call to RegExpFind with Not me.MatchCase.value
For those who know the VBE object model, there is a codemodule.find method that should have made all of the above a lot simpler. However, I encountered two problems with it.
The first had a workaround. After going through all the lines in a codemodule, the find method kept on returning the last match it found.
The second was of more concern. I would have thought that the built-in method would be much faster than the above VBA-based solution. However, for ‘whole word’ searches, the Find method had a noticeable delay. Don’t ask me why. Like I said, I would have expected it to be faster.
Here is a non-RegEx function that should correctly determine if a whole word is inside another piece of text…
Optional IgnoreCase As Boolean = True) As Boolean
If IgnoreCase Then SearchWhat = UCase(SearchWhat): SearchFor = UCase(SearchFor)
IsWholeWord = ” “ & SearchWhat & ” “ Like “*[!A-Za-z0-9]” & SearchFor & “[!A-Za-z0-9]*”
End Function
It just occurred to me… for those who like one-liners, my IsWholeWord function can be made into a one-liner (albeit a long one)…
Optional IgnoreCase As Boolean = True) As Boolean
IsWholeWord = ” “ & Format(SearchWhat, Mid(“>”, 2 + IgnoreCase)) & ” “ Like “*[!A-Za-z0-9]” & _
Format(SearchFor, Mid(“>”, 2 + IgnoreCase)) & “[!A-Za-z0-9]*”
End Function