VBA Function to Search in Array

[Admin]

Hi All,

There may be better ways to accomplish this task. This UDF returns TRUE if the search key found in array,otherwise obviously a FALSE.

Option Explicit

Function ISEXISTINARRAY(ByVal InputArray, ByVal SearchKey As String, ByVal MultiDimenArray As Boolean, _
                                                        Optional ByVal MatchCase As Boolean = False, _
                                                        Optional SearchWhole As Boolean = False) As Boolean
    
    Dim strJoin     As String
    Dim lngLoop     As Long
    Dim strSource   As String
    Dim Delim       As String
    Dim lngCompare  As Long
    
    'Krishnakumar@ExcelFox.com
    
    Delim = Chr$(1)
    
    If MatchCase Then
        lngCompare = 0
    Else
        lngCompare = 1
    End If
    If SearchWhole Then
        SearchKey = Delim & SearchKey & Delim
    End If
    If TypeOf InputArray Is Range Then InputArray = InputArray.Value2
    If MultiDimenArray Then
        For lngLoop = LBound(InputArray) To UBound(InputArray, 2)
            If SearchWhole Then
                strSource = Delim & Join$(Application.Transpose(Application.Index(InputArray, 0, lngLoop)), Delim) & Delim
            Else
                strSource = Join$(Application.Transpose(Application.Index(InputArray, 0, lngLoop)), Delim)
            End If
            If InStr(1, strSource, SearchKey, lngCompare) Then
                ISEXISTINARRAY = True
                Exit Function
            End If
        Next
    Else
        If SearchWhole Then
            strSource = Delim & Join$(Application.Transpose(InputArray), Delim) & Delim
        Else
            strSource = Join$(Application.Transpose(InputArray), Delim)
        End If
        If InStr(1, strSource, SearchKey, lngCompare) Then
            ISEXISTINARRAY = True
            Exit Function
        End If
    End If
    
End Function

and call the function like ..

Sub kTest()
    
    Dim k
    
    k = Range("a1:c1") '.CurrentRegion
    
    MsgBox ISEXISTINARRAY(k, "SearchWord", 1, 0, 0)
    
End Sub

or in a cell

=ISEXISTINARRAY(A1:F13,"SearchWord",1,1)

Enjoy !!