Solved: Speed with array vs. dictionary

[mikke3141]

Hello All,

I'm building a file with 30000*16 values from multiple sources.
My question is concerning Dictionary exists vs. array function below

Public Function isinarrayex(ByRef FindValue As Variant, ByRef vArr As Variant) As Boolean
      Dim vArrEach As Variant
     
      For Each vArrEach In vArr
          isinarrayex = (FindValue = vArrEach)
          If isinarrayex Then Exit For
      Next
End Function

Which one would be faster, the dictionary or the shown code? Also, is there a way to get the function above to be any faster as all the elements that I'm checking existence for are in the first element from array(1,1) to array(30000,1). The function above goes also through array(1,2) to array(30000,16), which certainly makes the function slower.

Thank you for your help.

[GTO]

Hi There,

I may truly get myself corrected on this (which is fine as it how one learns), but if I am understanding (I hear that snickering Ted!), my questions/'observations' would be:

1. Dictionary is quite fast, but off the top, I cannot think of how you'd fill it, test for existence, and answer all that quick. I might we;; be wrong there though, so you'd need to provide/attach an example workbook with at least two columns of vals and describe what happens when we find, vs what if not.
2. If the array has 16 columns, why not a 'For x = a To b' instead of a 'For Each...'?

Mark

[mdmackillop]

Copy the first element to a single dimension array and use the Match function
I entered ="Data" & Row() & Column() and copied down to P30000

[VBA]
Sub test()
Dim MyArr
Dim vArr As Variant
MyArr = Range("Data").Value
ReDim vArr(UBound(MyArr))
For i = 1 To UBound(MyArr)
vArr(i) = MyArr(i, 1)
Next
MsgBox IsInArrayEx("data260171", vArr)
End Sub

Public Function IsInArrayEx(ByRef FindValue As Variant, ByRef vArr As Variant) As Long
IsInArrayEx = Application.Match(FindValue, vArr, 0)
End Function

[/VBA]

[Shred Dude]

Since your first solution was focused on returning a Boolean response as whether there was a match or not, and not necessarily where the match was, you may want to try the method below. I've found it to be very fast. It's referenced out there in several places...you'll find it with a quick Google search.

[VBA]Function IsInArray(arr As Variant, valueToFind As Variant) As Boolean
IsInArray = (UBound(Filter(arr, valueToFind)) > -1)
End Function[/VBA]