Using the Filter Function to Search String Arrays

Article
06/14/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

The Filter function makes it easy to search a string array if you simply must 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 does not 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 does not tell you where.

Another potential problem with using the Filter function to search an array is that there is no way to specify whether the search text should match the entire element or whether it only must 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 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 is 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

Using the Filter Function to Search String Arrays

See Also

Additional resources