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

User · Answer

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

User · Answer

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    gt   0 End Function   The Filter function has the following signature   Filter sourceArray  stringToMatch   Include As Boolean   True    Compare as VbCompareMethod   vbBinaryCompare

User · Answer

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  amp  Join ar  delimiter   amp  delimiter      IsInArray   InStr list  delimiter  amp  item  amp  delimiter   gt  0 End Function   Sample usage   Sub test       Debug Print  Is  A  in the list    IsInArray Split  A B          A   End Sub

User · Answer

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

User · Answer

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

User · Answer

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  quot flat quot  2-dim arrays  Sub TestIsInArray       Const SearchItem As String    quot ghi quot      Debug Print  quot SearchItem     quot   amp  SearchItem  amp   quot   quot       ----      a  Test 1-dim array     Dim Arr As Variant     Arr   Split  quot abc def ghi jkl quot    quot   quot       Debug Print  quot a  1-dim array  quot   amp  vbNewLine  amp   quot     quot   amp  Join Arr   quot   quot    amp   quot     gt   quot   amp  IsInArray SearchItem  Arr       ----                 quick tool to create a 2-dim 1-based array         Dim v As Variant  vals As Variant                                                v   Array Array  quot abc quot    quot def quot    quot dummy quot    quot jkl quot   5                       Array  quot mno quot    quot pqr quot    quot stu quot    quot ghi quot    quot vwx quot            v   Application Index v  0  0     create 2-dim array  2 rows  5 cols            b  Test  quot flat quot  2-dim arrays     Debug Print  quot b   quot  quot flat quot  quot  2-dim arrays  quot      Dim i As Long     For i   LBound v  To UBound v           slice  quot flat quot  2-dim arrays of one row each         vals   Application Index v  i  0           check for findings         Debug Print Format i   quot    0 quot    Join vals   quot   quot    amp   quot     gt   quot   amp  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  quot flat quot  array  not necessarily a 1-dimensioned array   IsInArray    UBound Filter Arr  stringToBeFound    gt  -1  End Function  Results in VB Editor s immediate window SearchItem    ghi  a  1-dim array     abc def ghi jkl    gt  Wahr b   quot flat quot  2-dim arrays     1          abc def dummy jkl 5         False    2          mno pqr stu ghi vwx         True

User · Answer

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

User · Answer

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

User · Answer

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    gt  -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   gt  -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

User · Answer

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

[arrays] How to search for string in an array

Examples related to arrays

Examples related to vba