Adding functionality – I

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.

    Function RegExpFind(ByVal SearchWhat As 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

    sFindWhat = LCase(Me.tbxFind.Text)

with

    ‘sFindWhat = LCase(Me.tbxFind.Text)
    sFindWhat = Me.tbxFind.Text
    If Me.FindWholeWord.Value Then sFindWhat = “” & sFindWhat & “”

and

                If InStr(1, LCase(CM.Lines(I, 1)), sFindWhat) > 0 Then

with

                ‘If InStr(1, LCase(CM.Lines(I, 1)), sFindWhat) > 0 Then
                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.

Private Sub FindWholeWord_Click()
    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.

Posted in Uncategorized

3 thoughts on “Adding functionality – I

  1. Here is a non-RegEx function that should correctly determine if a whole word is inside another piece of text…

    Function IsWholeWord(ByVal SearchWhat As String, ByVal SearchFor As String, _
                         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
  2. 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)…

    Function IsWholeWord(ByVal SearchWhat As String, ByVal SearchFor As String, _
                           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


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.