In VBA, how to check if two arrays contain the same element?-CodePudding

I suspect I might need a function for that. I would like to return a Boolean. E.g., firstarray = ("haha", "ya", "test") and secondarray = ("haha", "no", "stop"), then return True. Thanks

CodePudding user response：

Please, use the next function. It does what you want without any iteration. It returns True if any of their elements are common to both of them:

Function MatchArrayElement(arr1, arr2) As Boolean
   Dim arrMtch: arrMtch = Application.IfError(Application.match(arr1, arr2, 0), "x") 'place "x" instead of error (not matching elements)
      Debug.Print Join(arrMtch, "|") 'just to visually see what the maching arrays returns...
   If UBound(filter(arrMtch, "x", False)) > -1 Then MatchArrayElement = True
End Function

It can be used in the next way:

Sub testMatchArrEl()
  Dim arr1, arr2
  arr1 = Array("hah", "ya", "test")
  arr2 = Array("haha", "no", "stop")
  Debug.Print MatchArrayElement(arr1, arr2)
End Sub

But if you need checking if a specific element/string exists in an array, you can use the following function:

Function arrElementMatch(El, arr) As Boolean
    Dim mtch: mtch = Application.match(El, arr, 0)
    arrElementMatch = Not IsError(mtch)
End Function

It can be checked in the next way:

  Debug.Print arrElementMatch("haha", arr1), arrElementMatch("hah", arr1)

To check if the element exists in both array, try using:

  Debug.Print arrElementMatch("haha", arr1) And arrElementMatch("haha", arr2)

Please, send some feedback after using it.

If something not clear enough, do not hesitate to ask for clarifications...

CodePudding user response：

Are There Any Matching Elements in Two Arrays? (One-Liner)

A safer yet less efficient way would probably be to loop through each element in one array and, in another (inner) loop, compare it to each element of the other array exiting the loops when a match is found.

Option Explicit

Sub Test()

    Dim FirstArray() As Variant
    FirstArray = Array("A", "ya", CVErr(xlErrNA), "test")
    Dim SecondArray() As Variant
    SecondArray = Array("sa", "aha", CVErr(xlErrNA), "stop")
     
    If FoundMatchingElement(FirstArray, SecondArray) Then
        Debug.Print "True"
    Else
        Debug.Print "False"
    End If
    ' Result:
    ' False ' ... since error values are excluded from the comparison
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns a boolean indicating whether two arrays ('Arr1', 'Arr2')
'               have a same element.
' Remarks:      'Application.Match' is case-insensitive i.e. 'A = a'.
'               Error values will not be considered.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function FoundMatchingElement( _
    Arr1() As Variant, _
    Arr2() As Variant) _
As Boolean
    FoundMatchingElement _
        = Application.Count(Application.Match(Arr1, Arr2, 0)) > 0
End Function