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.
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.
This question is tagged with
~ Asked on 2012-06-08 15:24:50
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.
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.
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.
~ Answered on 2012-06-08 16:31:36
Another option would be use a dictionary instead of an array:
Dim oNames As Object Set 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 keys oNames.Add "JOHN", 1 oNames.Add "BOB", 1 oNames.Add "JAMES", 1 oNames.Add "PHILIP", 1
As this would then get you a one-liner of
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.
~ Answered on 2014-04-15 12:00:21