Option Compare Database
Option Explicit
Public Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub Test()
Const MaxN =  10000   'Число элементов 
Const CountTest =  1000   'Число повторов теста 
Dim FindSTR As String
Dim colExamp As New Collection
Dim M(MaxN) As String
Dim i As Long
Dim t As Long
Dim k As Long
Debug.Print "Число элементов:"; MaxN, "Число повторов теста"; CountTest
t = timeGetTime()
For i =  1  To MaxN
    colExamp.Add Item:=CStr(i), Key:=CStr(i)
Next i
t = timeGetTime() - t
Debug.Print "Коллекция(Заполнение):", t

t = timeGetTime()
For i =  1  To MaxN
    M(i) = CStr(i)
Next i
t = timeGetTime() - t
Debug.Print "Массив(Заполнение):", t

FindSTR = CStr(MaxN *  2  \  3 )

Dim Found, MyObject
 'Поиск по коллекции 
t = timeGetTime()
For k =  1  To CountTest
    Found = False
    For Each MyObject In colExamp
    If MyObject = FindSTR Then
        Found = True
        Exit For
    End If
    Next
Next k
 'Debug.Print MyObject 
t = timeGetTime() - t
Debug.Print "Коллекция:", t

 'Поиск по массиву 
t = timeGetTime()
For k =  1  To CountTest
Found = False
For i =  1  To MaxN
    If M(i) = FindSTR Then
        Found = True
        Exit For
    End If
Next
Next k
 'Debug.Print M(i) 
t = timeGetTime() - t
Debug.Print "Массив:", t

End Sub