Home » Blogs » Vishesh's blog

Filter/Find in Array

Vishesh's picture
Submitted by Vishesh on 7 August, 2011 - 07:51
The following piece of code can be run from any general module. This code extracts all array elements based on the search criteria. You can also search the entire array element or part of it depending on the 3rd parameter of the function if its set to True or False. 
Sub TestRun()
    Dim arr
    Dim varSearch
 
    '-------------Variable parameters----------
    arr = Array(1, 2, 3, 10, 110, 111)
    varSearch = 1
    '==========================================
    MsgBox Join(FilterArray(arr, varSearch, True), vbCr)
End Sub
 
Function FilterArray(arrVariant, varSearch, Optional blnEntireElement As Boolean = True)
    If blnEntireElement Then
        FilterArray = FilterArrayExact(arrVariant, varSearch)
    Else
        FilterArray = Filter(arrVariant, varSearch, True)
    End If
End Function
 
Function FilterArrayExact(arrVariant, varSearch)
    Dim arrSearch       As Variant
    Dim arrFiltered     As Variant
    Const strEncl       As String = ":;"
    Const strDelim      As String = ",,"
 
    'first filter the array for elements containing search variant
    arrFiltered = Filter(arrVariant, varSearch)
 
    If UBound(arrFiltered) > -1 Then
      'mark the beginning and end of each array element found
      'strEncl and strDelim should not be in the array
      arrSearch = Split(strEncl & Join(arrFiltered, strEncl & _
                      strDelim & strEncl) & strEncl, strDelim)
      'again filter array, include Encl in search
      arrFiltered = Filter(arrSearch, _
                        strEncl & varSearch & strEncl)
      'clear Encl from final result
      FilterArrayExact = Split(Replace(Join(arrFiltered, _
                    strDelim), strEncl, ""), strDelim)
    End If
End Function
»