Option Compare Database
Option Explicit
Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub TestComp()
Dim idsList As New Collection
Dim startTime, diffTime As Long
Dim i As Long
Dim result As String
Dim strList As String
Dim blnComp As Boolean
Dim counter As Long
Dim testArr

    counter = 9999
    
    For i = 0 To counter
        idsList.Add i, CStr(i)
    Next i
    
    startTime = GetTickCount
    For i = 0 To counter
        blnComp = idsList(CStr(i)) = i
    Next i
    diffTime = GetTickCount - startTime
    Debug.Print "Использование коллекции.                       Считываний: " & counter + 1 & " раз. Время: " & diffTime
    
    '=====================================
    strList = ","
    For i = 0 To counter
        strList = strList & i & ","
    Next i
    
    startTime = GetTickCount
    For i = 0 To counter
        blnComp = strList Like "*," & CStr(i) & ",*"
    Next i
    diffTime = GetTickCount - startTime
    Debug.Print "Использование Like.                            Считываний: " & counter + 1 & " раз. Время: " & diffTime
    
    '=====================================
    
    ReDim testArr(counter + 1)
    For i = 0 To counter
        testArr(i) = i
    Next i
    
    startTime = GetTickCount
    For i = 0 To counter
        blnComp = ArrayContainsElement_v1(testArr, CStr(i))
    Next i
    diffTime = GetTickCount - startTime
    Debug.Print "Использование функции ArrayContainsElement_v1. Считываний: " & counter + 1 & " раз. Время: " & diffTime
    
    '=====================================
    
    ReDim testArr(counter + 1)
    For i = 0 To counter
        testArr(i) = i
    Next i
    
    startTime = GetTickCount
    For i = 0 To counter
        blnComp = ArrayContainsElement_v2(testArr, CStr(i))
    Next i
    diffTime = GetTickCount - startTime
    Debug.Print "Использование функции ArrayContainsElement_v2. Считываний: " & counter + 1 & " раз. Время: " & diffTime
    
    '=====================================
    
    ReDim testArr(counter + 1)
    For i = 0 To counter
        testArr(i) = i
    Next i
    
    startTime = GetTickCount
    For i = 0 To counter
        blnComp = InArr(i, testArr)
    Next i
    diffTime = GetTickCount - startTime
    Debug.Print "Использование функции InArr.                   Считываний: " & counter + 1 & " раз. Время: " & diffTime
    
End Sub


Function ArrayContainsElement_v1(searchedArray As Variant, soughtElement As String, Optional delimiter As String = ",", Optional marker As String = "!") As Boolean
    Dim filteredArray As Variant
    Dim joinedElements As String
    'pre-filter the array for elements containing 1
    filteredArray = Filter(searchedArray, soughtElement, True)
    If UBound(filteredArray) > -1 Then
        'mark the beginning and end of each found element
        'marker and delimiter must be characters that
        'don't occur in any element of the array!
        joinedElements = marker & Join(filteredArray, marker & delimiter & marker) & marker
        If InStr(joinedElements, marker & soughtElement & marker) > 0 Then
            ArrayContainsElement_v1 = True
            Exit Function
        End If
    End If
End Function

Function ArrayContainsElement_v2(searchedArray As Variant, soughtElement As String, Optional delimiter As String = ",", Optional marker As String = "!") As Boolean
    Dim filteredArray As Variant
    Dim filteredArray2 As Variant
    Dim joinedElements As String
    'pre-filter the array for elements containing 1
    filteredArray = Filter(searchedArray, soughtElement, True)
    If UBound(filteredArray) > -1 Then
        'mark the beginning and end of each found element
        'marker and delimiter must be characters that
        'don't occur in any element of the array!
        filteredArray2 = Split(marker & Join(filteredArray, marker & delimiter & marker) & marker, delimiter)
        If (UBound(Filter(filteredArray2, marker & soughtElement & marker, True)) <> -1) Then
            ArrayContainsElement_v2 = True
            Exit Function
        End If
    End If
End Function

Function InArr(x As Long, xx As Variant) As Boolean
  
 Dim i As Variant
 
 For Each i In xx
   If i = x Then
     InArr = True
     Exit Function
   End If
 Next
 
 InArr = False

End Function