How to search for string in an array

42

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.

This question is tagged with arrays vba

~ Asked on 2012-06-08 15:24:50

The Best Answer is


73

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.

~ Answered on 2012-06-08 16:31:36


26

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.

~ Answered on 2014-04-15 12:00:21


Most Viewed Questions: