How to search for string in an array

Question

Is there an easy (one-liner) to search for a string within an array in VBA? Or will I need to loop through each element and compare it with the target string?

EDIT: It is a one-dimensional array. I only need to know IF a string is somewhere in the array.

IE:

names(JOHN, BOB, JAMES, PHLLIP) 

How do I find out if "JOHN" is in the array, it needs to be minimal as it will be repeated around 5000 times and I don't want the function to slow the overall process down.

Answer 1

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