How to search a string in an array

Question

Is there a simple (single-line) string lookup for an array in VBA? Or do I need to scroll through each element and compare it with the target line?

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

IE:

names(JOHN, BOB, JAMES, PHLLIP) 

How to find out if "JOHN" is in the array, it should be minimal, because it will be repeated about 5000 times, and I do not want the function to slow down the overall process.

Answer 1

If you want to find out if a 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 Sean Cheshire points out, this should be a 1-dimensional 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 HansUp comment)

If you need the index of the corresponding element in the array, try the following:

 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 

It also assumes a 1-dimensional array. Keep in mind that LBound and UBound are zero based, so index 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, please update your question, otherwise the example code may not match your situation.

Answer 2

Another option would be to 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 

Since this will give you a single line

 oNames.Exists("JOHN") 

The advantage provided by the dictionary is the exact match in partial matching from Filter . Let's say if you have an initial list of names in an array, but they were looking for “JO” or “PHIL”, which were actually two new people in addition to the four that we started with. In this case, Filter(oNAMES, "JO") will match "JOHN", which may be undesirable. With a dictionary, he will not.

Answer 3

Another option that provides an exact match (i.e. without a partial match) would be:

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

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

Answer 4

there is a function that will return an array all rows found.

Filter(sourcesrray, match[, include[, compare]])
Sourcearray must be 1-dimensional
The function will return all the rows in the array with the string match in them.

Answer 5

A simpler function that works on Apple OS:

 Function isInArray(ByVal stringToBeFound As String, ByVal arr As Variant) As Boolean For Each element In arr If element = stringToBeFound Then isInArray = True Exit Function End If Next element End Function 

Answer 6

Here is another answer. It works quickly, reliably (see the answer of atomic scientists) and has a compact calling code:

 ' Returns true if item is in the array; false otherwise. Function IsInArray(ar, item$) As Boolean Dim delimiter$, list$ ' Chr(7) is the ASCII 'Bell' Character. ' It was chosen for being unlikely to be found in a normal array. delimiter = Chr(7) ' Create a list string containing all the items in the array separated by the delimiter. list = delimiter & Join(ar, delimiter) & delimiter IsInArray = InStr(list, delimiter & item & delimiter) > 0 End Function 

Using an example:

 Sub test() Debug.Print "Is 'A' in the list?", IsInArray(Split("A,B", ","), "A") End Sub 

Answer 7

If this is a list of constants, you can use Select Case as follows:

 Dim Item$: Item = "A" Select Case Item Case "A", "B", "C" ' If 'Item' is in the list then do something. Case Else ' Otherwise do something else. End Select 

Answer 8

The A Case statement can simplify some applications somewhat:

 select case var case "a string", "another string", sVar 'do something case else 'do something else end select