Microsoft Office 2000/Visual Basic Programmer's Guide

Using the Filter Function to Search String Arrays

The Filter function, described earlier in this chapter in "Working with Strings as Arrays," makes it easy to search a string array if you simply need to know whether an item exists in the array. The Filter function takes a string array and a string containing the search text. It returns a one-dimensional array containing all the elements that match the search text.

One potential disadvantage of using the Filter function to search an array is that it doesn't return the index of the elements of the array that match the search text. In other words, the Filter function tells you whether an element exists in an array, but it doesn't tell you where.

Another potential problem with using the Filter function to search an array is that there's no way to specify whether the search text should match the entire element, or whether it need only match a part of it. For example, if you use the Filter function to search for an element matching the letter "e," the Filter function returns not only those elements containing only "e," but also any elements containing larger words that include "e."

The following procedure augments the capabilities of the Filter function in order to search an array and returns only elements that match exactly. The FilterExactMatch procedure takes two arguments: a string array to search and a string to find. It uses the Filter function to return an array containing all elements that match the search string, either partially or entirely. It then checks each element in the filtered array to verify that it matches the search string exactly. If the element does match exactly, it's copied to a third string array. The function returns this third array, which contains only exact matches.

Function FilterExactMatch(astrItems() As String, _
                          strSearch As String) As String()
                    
   ' This function searches a string array for elements
   ' that exactly match the search string.

   Dim astrFilter()   As String
   Dim astrTemp()       As String
   Dim lngUpper         As Long
   Dim lngLower         As Long
   Dim lngIndex         As Long
   Dim lngCount         As Long
   
   ' Filter array for search string.
   astrFilter = Filter(astrItems, strSearch)
   
   ' Store upper and lower bounds of resulting array.
   lngUpper = UBound(astrFilter)
   lngLower = LBound(astrFilter)
   
   ' Resize temporary array to be same size.
   ReDim astrTemp(lngLower To lngUpper)
   
   ' Loop through each element in filtered array.
   For lngIndex = lngLower To lngUpper
      ' Check that element matches search string exactly.
      If astrFilter(lngIndex) = strSearch Then
         ' Store elements that match exactly in another array.
         astrTemp(lngCount) = strSearch
         lngCount = lngCount + 1
      End If
   Next lngIndex
   
   ' Resize array containing exact matches.
   ReDim Preserve astrTemp(lngLower To lngCount - 1)
   
   ' Return array containing exact matches.
   FilterExactMatch = astrTemp
End Function

This procedure is available in the modArrays module in VBA.mdb in the ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.