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(Inpu tArray, 0, lngLoop)), Delim) & Delim
Else
strSource = Join$(Application.Transpose(Application.Index(Inpu tArray, 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 !!