How to search for string in an array

If you want to know if the string is found in the array at all, try this function:

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)End Function

As SeanC points out, this must be a 1-D array.

Example:

Sub Test()  Dim arr As Variant  arr = Split("abc,def,ghi,jkl", ",")  Debug.Print IsInArray("ghi", arr)End Sub

(Below code updated based on comment from HansUp)

If you want the index of the matching element in the array, try this:

Function IsInArray(stringToBeFound As String, arr As Variant) As Long  Dim i As Long  ' default return value if value not found in array  IsInArray = -1  For i = LBound(arr) To UBound(arr)    If StrComp(stringToBeFound, arr(i), vbTextCompare) = 0 Then      IsInArray = i      Exit For    End If  Next iEnd Function

This also assumes a 1-D array. Keep in mind LBound and UBound are zero-based so an index of 2 means the third element, not the second.

Example:

Sub Test()  Dim arr As Variant  arr = Split("abc,def,ghi,jkl", ",")  Debug.Print (IsInArray("ghi", arr) > -1)End Sub

If you have a specific example in mind, please update your question with it, otherwise example code might not apply to your situation.

Another option would be use a dictionary instead of an array:

Dim oNames As ObjectSet oNames = CreateObject("Scripting.Dictionary")'You could if need be create this automatically from an existing Array'The 1 is just a dummy value, we just want the names as keysoNames.Add "JOHN", 1oNames.Add "BOB", 1oNames.Add "JAMES", 1oNames.Add "PHILIP", 1

As this would then get you a one-liner of

oNames.Exists("JOHN")

The advantage a dictionary provides is exact matching over partial matching from Filter. Say if you have the original list of names in an Array, but were looking for "JO" or "PHIL" who were actually two new people in addition to the four we started with. In this case, Filter(oNAMES, "JO") will match "JOHN" which may not be desired. With a dictionary, it won't.

Another option that enforces exact matching (i.e. no partial matching) would be:

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean  IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))End Function

You can read more about the Match method and its arguments athttp://msdn.microsoft.com/en-us/library/office/ff835873(v=office.15).aspx