How to search for string in an array
arrays, vba
Solution
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.
Problem
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.