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)
Then, I had to make only minimal changes to the code in the tbxFind_Change routine, replacing
sFindWhat = Me.tbxFind.Text
If Me.FindWholeWord.Value Then sFindWhat = “” & sFindWhat & “”
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.
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