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
© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext