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

Completing remark to Jimmy Pena's accepted answer

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

The following example call demonstrates that the IsInArray() function cannot be called only for 1-dim arrays, but also for "flat" 2-dim arrays:

Sub TestIsInArray()
    Const SearchItem As String = "ghi"
    Debug.Print "SearchItem = '" & SearchItem & "'"
    '----
    'a) Test 1-dim array
    Dim Arr As Variant
    Arr = Split("abc,def,ghi,jkl", ",")
    Debug.Print "a) 1-dim array " & vbNewLine & "   " & Join(Arr, "|") & " ~~> " & IsInArray(SearchItem, Arr)
    '----
    
        '//quick tool to create a 2-dim 1-based array
        Dim v As Variant, vals As Variant                                       
        v = Array(Array("abc", "def", "dummy", "jkl", 5), _
                  Array("mno", "pqr", "stu", "ghi", "vwx"))
        v = Application.Index(v, 0, 0)  ' create 2-dim array (2 rows, 5 cols)
    
    'b) Test "flat" 2-dim arrays
    Debug.Print "b) ""flat"" 2-dim arrays "
    Dim i As Long
    For i = LBound(v) To UBound(v)
        'slice "flat" 2-dim arrays of one row each
        vals = Application.Index(v, i, 0)
        'check for findings
        Debug.Print Format(i, "   0"), Join(vals, "|") & " ~~> " & IsInArray(SearchItem, vals)
    Next i
End Sub

Function IsInArray(stringToBeFound As String, Arr As Variant) As Boolean
'Site: https://stackoverflow.com/questions/10951687/how-to-search-for-string-in-an-array/10952705
'Note: needs a "flat" array, not necessarily a 1-dimensioned array
  IsInArray = (UBound(Filter(Arr, stringToBeFound)) > -1)
End Function

Results in VB Editor's immediate window

SearchItem = 'ghi'
a) 1-dim array 
   abc|def|ghi|jkl ~~> True
b) "flat" 2-dim arrays 
   1          abc|def|dummy|jkl|5         False
   2          mno|pqr|stu|ghi|vwx         True

Answer 2

This is my code, inspired by @atomicules

Public Function IsName(name As String) As Boolean
  Dim names As Object
  Set names = CreateObject("System.Collections.ArrayList")

  names.Add "JOHN"
  names.Add "BOB"
  names.Add "JAMES"
  names.Add "PHLLIP"
  
  IsName = names.Contains(name)
    
End Function

And this is usage:

If IsName("JOHN") Then ...

Answer 3

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.

Answer 4

more simple Function whichs works on Apple OS too:

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

Answer 5

You could use the following without the wrapper function, but it provides a nicer API:

Function IsInArray(ByVal findString as String, ByVal arrayToSearch as Variant) as Boolean
  IsInArray = UBound(Filter(arrayToSearch,findString)) >= 0
End Function

The Filter function has the following signature:

Filter(sourceArray, stringToMatch, [Include As Boolean = True], [Compare as VbCompareMethod = vbBinaryCompare])

Answer 6

there is a function that will return an array of all the strings found.

Filter(sourcearray, match[, include[, compare]])
The sourcearray has to be 1 dimensional
The function will return all strings in the array that have the match string in them

Answer 7

A Case statement might suit some applications more simply:

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

Answer 8

If it's a list of constants then 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 9

Here's another answer. It works fast, reliably (see atomicules' answer) and has 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

Sample usage:

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

Answer 10

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 at http://msdn.microsoft.com/en-us/library/office/ff835873(v=office.15).aspx

Answer 11

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

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.