Searching

If your array contains less than 100,000 elements then looping through once is very efficient.
Are you looking to conduct an exact match or just a partial match.
If you need to do a lot of exact match searches then it might be worth sorting the array first and then performing a binary search.
You also need to consider how often the data is changing. Any changes will require sorting the array again.


Looping Through

This function allows you to match exact matches with an optional argument to include substring matches if you want to.

Public Function IsInArray( _ 
   ByVal arr As Variant, _
   ByVal valueToCheck As String, _
   Optional ByVal bExactMatch As Boolean = True) _
   As Boolean

Dim Y As Long
Dim Xupper As Long
Dim Yupper As Long
Dim tempArray As Variant
    Xupper = UBound(myarray, 2)
    Yupper = UBound(myarray, 1)
    ReDim tempArray(Xupper, Yupper)
    For X = 0 To Xupper
        For Y = 0 To Yupper
            tempArray(X, Y) = myarray(Y, X)
        Next Y
    Next X
End Function


Dim arTesting(10,5) As String 
Dim arTesting(1 To 10, 1 To 5) As String
Dim lrow As Long
Dim lcolumn As Long

For irow = Lbound(arTesting,1) To Ubound(arTesting,1)
   For icolumn = Lbound(arTesting,2) To Ubound(arTesting,2)

   Next icolumn
Next lrow


Filter Function

You can use the FILTER function to tell if an item exists in a one-dimensional array.
It won't tell you where but it will tell you if it exists or not.
This function returns an array of any elements that contain a given text string (not equal to the 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.


Public Function IsInArray( _ 
   ByVal stringToBeFound As String, _
   ByVal myarray As Variant) _
   As Boolean

    IsInArray = (VBA.UBound(VBA.Filter(myarray, stringToBeFound)) > -1)
End Function


This method does not distinguish between complete matches and substring matches
This method does not return the index of the elements that match
This method does not work with numerical arrays since all the numbers have to be implicitly converted to text first


Dictionary Object

A Dictionary object is similar to an array and has a number of benefits
You can quickly check whether an item exists


Binary Search




© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext