Check if a value is in an array or not with Excel VBA

Question

I ve got some code below  that is supposed to be checking if a value is in an Array or not   Sub test       vars1   Array  Examples       vars2   Array  Example       If IsInArray Range  A1   Value  vars1  Then         x   1     End If      If IsInArray Range  A1   Value  vars2  Then         x   1     End If End Sub  Function IsInArray stringToBeFound As String  arr As Variant  As Boolean   IsInArray    UBound Filter arr  stringToBeFound    gt  -1  End Function   If the cell A1 contains the word Examples for some reason both of the IsInArray detects it as existing for both Arrays when it should only find it existing in the vars1 array  What do I need to change to make my IsInArray function to make it an exact match

User · Answer

I searched for this very question and when I saw the answers I ended up creating something different (because I favor less code over most other things most of the time) that should work in the vast majority of cases. Basically turn the array into a string with array elements separated by some delimiter character, and then wrap the search value in the delimiter character and pass through instr.

Function is_in_array(value As String, test_array) As Boolean
    If Not (IsArray(test_array)) Then Exit Function
    If InStr(1, "'" & Join(test_array, "'") & "'", "'" & value & "'") > 0 _
        Then is_in_array = True
End Function

And you'd execute the function like this:

test = is_in_array(1, array(1, 2, 3))

User · Answer

I would like to provide another variant that should be both performant and powerful  because    it does not use the sometimes slower Match  supports String  Integer  Boolean etc   not String-only  returns the index of the searched-for item supports nth-occurrence         -1 if not found  https   stackoverflow com a 56327647 1915920 Public Function IsInArray      item As Variant      arr As Variant      Optional nthOccurrence As Long   1       As Long      IsInArray   -1      Dim i As Long   For i   LBound arr  1  To UBound arr  1          If arr i    item Then             If nthOccurrence  gt  1 Then                 nthOccurrence   nthOccurrence - 1                 GoTo continue             End If             IsInArray   i             Exit Function         End If continue      Next i  End Function   use it like this   Sub testInt     Debug Print IsInArray 2  Array 1  2  3       gt  1 End Sub  Sub testString1     Debug Print IsInArray  b   Array  a    b    c    a        gt  1 End Sub  Sub testString2     Debug Print IsInArray  b   Array  a    b    c    b    2      gt  3 End Sub  Sub testBool1     Debug Print IsInArray False  Array True  False  True       gt  1 End Sub  Sub testBool2     Debug Print IsInArray True  Array True  False  True   2      gt  2 End Sub

User · Answer

The below function would return  0  if there is no match and a  positive integer  in case of matching     Function IsInArray stringToBeFound As String  arr As Variant  As Integer  IsInArray   InStr Join arr       stringToBeFound   End Function                                                                                 Note  the function first concatenates the entire array content to a string using  Join   not sure if the join method uses looping internally or not  and then checks for a macth within this string using InStr

User · Answer

You can brute force it like this   Public Function IsInArray stringToBeFound As String  arr As Variant  As Boolean     Dim i     For i   LBound arr  To UBound arr          If arr i    stringToBeFound Then             IsInArray   True             Exit Function         End If     Next i     IsInArray   False  End Function   Use like  IsInArray  example   Array  example    someother text    more things    and another

User · Answer

Use Match   function in excel VBA to check whether the value exists in an array    Sub test       Dim x As Long      vars1   Array  Abc    Xyz    Examples       vars2   Array  Def    IJK    MNO        If IsNumeric Application Match Range  A1   Value  vars1  0   Then         x   1     ElseIf IsNumeric Application Match Range  A1   Value  vars2  0   Then         x   1     End If      MsgBox x End Sub

User · Answer

You want to check whether Examples exists in Range  A1   Value If it fails then to check Example right  I think mycode will work perfect  Please check   Sub test   Dim string1 As String  string2 As String string1    Examples  string2    Example  If InStr 1  Range  A1   Value  string1   gt  0 Then     x   1 ElseIf InStr 1  Range  A1   Value  string2   gt  0 Then     x   2 End If   End Sub

User · Answer

This Question was asked here  VBA Arrays - Check strict  not approximative  match   Sub test       vars1   Array  Examples       vars2   Array  Example       If IsInArray Range  A1   value  vars1  Then         x   1     End If      If IsInArray Range  A1   value  vars2  Then         x   1     End If End Sub  Function IsInArray stringToBeFound As String  arr As Variant  As Boolean     IsInArray   Not IsError Application Match stringToBeFound  arr  0   End Function

User · Answer

While this is essentially just  Brad s answer again  I thought it might be worth including a slightly modified function which will return the index of the item you re searching for if it exists in the array  If the item is not in the array  it returns -1 instead    The output of this can be checked just like the  in string  function  If InStr       gt  0 Then  so I made a little test function below it as an example   Option Explicit  Public Function IsInArrayIndex stringToFind As String  arr As Variant  As Long      IsInArrayIndex   -1      Dim i As Long     For i   LBound arr  1  To UBound arr  1          If arr i    stringToFind Then             IsInArrayIndex   i             Exit Function         End If     Next i  End Function  Sub test        Dim fruitArray As Variant     fruitArray   Array  orange    apple    banana    berry        Dim result As Long     result   IsInArrayIndex  apple   fruitArray       If result  gt   0 Then         Debug Print chr 34   amp  fruitArray result   amp  chr 34   amp    exists in array at index    amp  result     Else         Debug Print  does not exist in array      End If  End Sub   Then I went a little overboard and fleshed out one for two dimensional arrays because when you generate an array based on a range it s generally in this form    It returns a single dimension variant array with just two values  the two indices of the array used as an input  assuming the value is found   If the value is not found  it returns an array of  -1  -1    Option Explicit  Public Function IsInArray2DIndex stringToFind As String  arr As Variant  As Variant      IsInArray2DIndex  Array -1  -1       Dim i As Long     Dim j As Long      For i   LBound arr  1  To UBound arr  1          For j   LBound arr  2  To UBound arr  2              If arr i  j    stringToFind Then                 IsInArray2DIndex  Array i  j                  Exit Function             End If         Next j     Next i  End Function   Here s a picture of the data that I set up for the test  followed by the test     Sub test2        Dim fruitArray2D As Variant     fruitArray2D   sheets  Sheet1   Range  A1 B2   value      Dim result As Variant     result   IsInArray2DIndex  apple   fruitArray2D       If result 0   gt   0 And result 1   gt   0 Then         Debug Print chr 34   amp  fruitArray2D result 0   result 1    amp  chr 34   amp    exists in array at row     amp  result 0   amp     col     amp  result 1      Else         Debug Print  does not exist in array      End If  End Sub

[arrays] Check if a value is in an array or not with Excel VBA

Examples related to arrays

Examples related to vba

Examples related to excel