Searching

If your array contains less than 100,000 elements then looping through once is very efficient.
Exactly which method you use will depend on the following questions:
1) Is the array 1 dimensional or multi-dimensional.
2) Are you looking to conduct an exact match or just a partial match.
3) Do you need to know the position of the item if it exists.
4) How many searches do you need to do, it might be worth sorting the array.
5) How often is the data changing, you may need to keep re-sorting.


1-Dimensional Array - Exact Match

If you are using Excel VBA you can use the Excel MATCH worksheet function.

Public Function OneDimensional_Exact_One(ByVal arValues As Variant, _ 
                                         ByVal sFindValue As String) _
                                         As Boolean
   On Error GoTo AnError
   OneDimensional_Exact_One = Not IsError(Application.WorksheetFunction.Match(sFindValue, arValues, 0))
   Exit Function

AnError:
   OneDimensional_Exact_One = False
End Function

You can use the FILTER function to tell if an item exists in a one-dimensional array.
This function returns an array of any elements that contain a given text string.
This function takes a string array, text string and returns a one-dimensional array containing all the elements that match the search string.
This method does not distinguish between complete matches and substring matches.
This method does not work with numerical arrays since all the numbers have to be implicitly converted to text first

Public Function OneDimensional_Exact_Two(ByVal arValues As Variant, _ 
                                         ByVal sFindValue As String) _
                                         As Boolean
   
   OneDimensional_Exact_Two = (UBound(VBA.Filter(arValues, sFindValue)) > -1)
End Function

You can use the INSTR function after converting the array to a string concatenation
This method also uses the JOIN function to return the text string containing the elements in an array (String).

Public Function OneDimensional_Exact_Three(ByVal arValues As Variant, _ 
                                           ByVal sFindValue As String) _
                                           As Boolean
Dim stextconcat As String
Dim sdelimiter As String

   sdelimiter = "--"
   stextconcat = sdelimiter & VBA.Join(arValues, sdelimiter) & sdelimiter
   OneDimensional_Exact_Three = VBA.InStr(1, stextconcat, sdelimiter & sFindValue & sdelimiter)
   
'use this line for case insensitive searches
'OneDimensional_Exact_Three = VBA.InStr(1, stextconcat, sdelimiter & sFindValue & sdelimiter, vbTextCompare)
End Function

You can loop through the array using a For-Each loop or a For-Next loop.

Public Function OneDimensional_Exact_Four(ByVal arValues As Variant, _ 
                                          ByVal sFindValue As String) _
                                          As Boolean
Dim sItem As Variant
   
   For Each sItem In arValues
      If sItem = sFindValue Then
          OneDimensional_Exact_Four = True
          Exit Function
      End If
   Next sItem
End Function

1-Dimensional Array - Partial Match



2-Dimensional Array - Partial Match

This function assumes the 1-dimensional array is zero-based.

Public Function TwoDimensional(ByVal arValues As Variant, _ 
                               ByVal sFindValue As String) _
                               As Boolean
   Dim lrow As Long
   Dim lcolumn As Long
    
   For lrow = LBound(arValues, 1) To UBound(arValues, 1)
      For lcolumn = LBound(arValues, 2) To UBound(arValues, 2)
         If (arValues (lrow, lcolumn) = sFindValue) Then
            TwoDimensional = True
            Exit Function
         End If
      Next lcolumn
   Next lrow
End Function

Collection Object

A Collection object can also be used although to populate the collection you need to loop through the array.
This might be a good solution if you need to perform multiple searches in the same data set.

Public Function UsingCollection(ByVal arValues As Variant, _ 
                                ByVal sFindValue As String) _
                                As Boolean
Dim lposition As Long
Dim sreturn As String
Dim MyCollection As VBA.Collection
   
   On Error GoTo AnError
   Set MyCollection = New VBA.Collection
   For lposition = 0 To UBound(arValues)
      MyCollection.Add arValues(lposition), arValues(lposition)
   Next lposition
   sreturn = MyCollection.Item(sFindValue)
   UsingCollection = True
   Exit Function

AnError:
   UsingCollection = False
End Function

Dictionary Object

A Dictionary object can also be used although this would require a reference to the Microsoft Scripting Runtime library.

Public Function UsingDictionary(ByVal arValues As Variant, _ 
                                ByVal sFindValue As String) _
                                As Boolean
Dim MyDictionary As Scripting.Dictionary
Dim lposition As Long
Dim sreturn As String
       
   Set MyDictionary = New Scripting.Dictionary
   For lposition = 0 To UBound(arValues)
      MyDictionary.Add arValues(lposition), arValues(lposition)
   Next lposition
   UsingDictionary = MyDictionary.Exists(sFindValue)
End Function

© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext