VBA Function to Search in Array

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.

Code:

---

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 ..

Code:

---

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 !!