Inflexible Find

Ever since I’ve been using Jan Karel’s Flexfind, I don’t use Excel’s built-in Find. However, about half of the time, I don’t need any fancy options. I just need to find some text in the workbook. So I wrote a utility for myself called DDoE Quick Find. It has the following non-features:

  • It searches the entire workbook – you can’t limit it to a sheet or range
  • It searches only in values, not in formulas
  • It only looks for partial matches
  • It only displays the first 100 results
  • There’s no replace facility
  • It’s really slow on very large workbooks

The add-in puts a Quick Find control on the Edit menu. You start typing in the What box, and when the list is manageable, you select items in the Results box. Selecting an item navigates to that cell.

The above is an example of one of those very large, slow workbooks. If the workbook has more than half a million used cells, the message at the bottom turns red (otherwise it’s black). It’s just a little visual clue that you may be in for a wait. I don’t have any workbooks that big except for the one used in this example. However, the bigger the workbook, the more likely I need to search for something in it. But it’s also more likely that I can narrow it down to a particular sheet and use Flexfind.

It takes about four seconds to search on the above monster workbook. That doesn’t seem too bad, except that it’s four seconds every time I type a letter. The code uses the Change event, not the AfterUpdate event, because I don’t want to have to navigate out of the textbox to get the results. I need to build in a small delay so that the user can get three or four letters typed before it starts searching. I’m not sure how that will work with the events. Will it just queue up the event calls and run them anyway? I don’t know.

And to be fair, the first few letters don’t take long at all. Since it stops searching after it finds 100 results, typing “B” gets 100 results on the first page and is very fast. By the time I get to the “e” or the “i”, it has to look in considerably more cells before it gets to 100 matches, and that is what takes so long.

Monster workbooks aside, it seems to do what I want at a reasonable speed. You can download ddoequickfind.xla.zip.

Posted in Uncategorized

8 thoughts on “Inflexible Find

  1. … and the Keydown to stop it. Does require a boolean test in the search loop, so it won’t speed it up I guess.

  2. Private Sub tbxWhat_AfterUpdate()
       
        If Me.IsLargeSearch Then
            FillResults
        End If
       
    End Sub
     
    Private Sub tbxWhat_Change()
           
        If Not Me.IsLargeSearch Then
            FillResults
        End If
       
    End Sub

    Yeah, that seems to work. Downloadable file updated.

  3. In Excel2003, an error is generated if the text in the found cell is longer than 2047 characters. You can replace the problem line in Private Sub FillResults with Me.lbxFound.AddItem Left$(rFound.Value, 2047).

    This is yet another example of where there are undocumented and different limits to the lengths of text strings that various Excel objects and methods can deal with.

  4. I suspect you could speed up the whole thing not using the additem method
    Instead I’d suggest to use a matrix that can be loaded into the listbox .list=sq
    I rewrote your application so that all the necessary code would be part of the userform. No other modules involved.

    Private Sub tbxWhat_AfterUpdate()
      On Error Resume Next
      If tbxWhat.Text = “” Then Exit Sub
      lblWarning.Caption = “”
      lblMessage.Caption = “”
       
      ReDim sq(100, 2)
      For Each sh In ActiveWorkbook.Sheets
        lblWarning.Caption = Val(lblWarning.Caption) + sh.UsedRange.Cells.Count
        c1 = “”
        c2 = “”
        Do
          If c1 = “” Then
            c1 = sh.UsedRange.Find(tbxWhat.Text, , xlValues, xlPart).Address
          Else
            c1 = sh.UsedRange.Find(tbxWhat.Text, sh.Range(c1), xlValues, xlPart).Address
          End If
          If Err.Number > 0 Then Exit Do
               
          If c1 = c2 Then Exit Do
          If c2 = “” Then c2 = c1
          With lblMessage
            sq(Val(.Caption), 0) = sh.Range(c1)
            sq(Val(.Caption), 1) = c1
            sq(Val(.Caption), 2) = sh.Name
            .Caption = Val(.Caption) + 1
          End With
        Loop Until Val(lblMessage.Caption) > UBound(sq)
        Err.Clear
        If Val(lblMessage.Caption) > UBound(sq) Then Exit For
      Next
      lbxFound.List = sq
       
      With lblMessage
        .ForeColor = IIf(Val(.Caption) > UBound(sq), vbRed, vbBlue)
        .Caption = “Cells Found: “ & IIf(Val(.Caption) > UBound(sq), “>”, “”) & .Caption
      End With
     
      lblWarning.Caption = “Cells to search: “ & Format(lblWarning.Caption, “#,##0”)
    End Sub
  5. You need to exclude hidden sheets so:

    For Each sh In ActiveWorkbook.Sheets
    If sh.Visible = xlSheetVisible Then….

    As Hans Schraven says – technically it’s quicker to use an array. I use a similar system to find parts from a parts database and the array system is over 100 times quicker (the list box can have up to 1,000 entries).

    Ian

Leave a Reply

Your email address will not be published. Required fields are marked *