VBA Search an array inside an array? (Check if all items of one array exists in another array)-CodePudding

IS it possible to look for an array of strings and/or integers inside an array of strings and/or integers? If so, then how?

To find a string in an array of strings I use code like:

If IsInArray(LowerFilmWidthArray, LowerFilmWidth) then
'Dos tuff
end if

And a function is:

Function IsInArray(arr As Variant, myVal As Variant) As Boolean

IsInArray = Not IsError(Application.Match(myVal, arr, 0))
Debug.Print (IsInArray)

End Function

As a result example, imagine you have an array of integers (1-10) and You are looking if your array (1,5,6) are inside that previous array (all items of it) and then return True. In my case I am getting all my to look for values in columns from 3rd to last column with data, which would make up my array that I try to find ALL items of in another array and return true or false.

An actual example:

Dim LowerFilmWidthArray
LowerFilmWidthArray = Application.Transpose(Evaluate("row(320:420)"))

Dim LowerFilmWidth As Integer
LowerFilmWidth = Array(ThisWorkbook.Worksheets("Machine Specification").Cells(320, 400,400,620)
'I get theese from a range and they might as well be strings and an undefined number of defined by 3 to last column with data

if isinarray(LowerFilmWidthArray,LowerFilmWidth) then
msgbox("Great Success!")
end if

Result in this one would be false because of that last "620" which is not inside the LowerFilmWidthArray.

CodePudding user response：

Is Array In Array

The function will return true if all the elements of an array (IsArr) are found in another array (InArr).

Option Explicit

Sub IsArrayInArrayTEST()
    
    Dim InArr As Variant: InArr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
    
    Dim IsArr As Variant
    
    IsArr = Array(1)
    Debug.Print IsArrayInArray(IsArr, InArr) ' True
    
    IsArr = Array(1, 5, 11)
    Debug.Print IsArrayInArray(IsArr, InArr) ' False

End Sub

Function IsArrayInArray( _
    ByVal IsArr As Variant, _
    ByVal InArr As Variant) _
As Boolean
    
    Dim IsCount As Long: IsCount = UBound(IsArr) - LBound(IsArr)   1
    Dim rArr As Variant: rArr = Application.Match(IsArr, InArr, 0)
    Dim rCount As Long: rCount = Application.Count(rArr)
    'Debug.Print rCount, IsCount
    If rCount = IsCount Then
        IsArrayInArray = True
    End If
    
End Function

CodePudding user response：

Please, look at the next example. Is this what you try accomplishing?

Sub testArrInArr()
  Dim arr(), arr1(), arr2(), arr3(), arr4()
  arr1 = Array(1, 2, 3): arr2 = Array(2, 3, 4)
  arr3 = Array(3, 6, 5, 4): arr4 = Array(4, 5, 6)
  
  arr = Array(arr1, arr2, arr3)
  Debug.Print arrIsInArray(arr, arr2)
End Sub

Function arrIsInArray(arr As Variant, arrX As Variant) As Boolean
   Dim i As Long, jArr As String
    For i = LBound(arr) To UBound(arr)
        If Join(arr(i)) = Join(arrX) Then arrIsInArray = True: Exit Function
    Next i
End Function

Edited:

In order to test each array element if exists in another array, plese try the next way:

Sub tst2CheckArrElements()
  Dim arr, arr1, arr2
  arr = Split("1,2,3", ","): arr1 = Split("Sausage,Dog,Ship", ","): arr2 = Split("1,2,3", ",")
  
  Debug.Print arrElemInArray(arr, arr1)
  Debug.Print arrElemInArray(arr, arr2)
End Sub

Function arrElemInArray(arr As Variant, arrX As Variant) As Boolean
   Dim i As Long, j As Long, boolFound As Boolean
   For i = LBound(arrX) To UBound(arrX)
        For j = LBound(arr) To UBound(arr)
            If arr(j) = arrX(i) Then
                boolFound = True: Exit For
            End If
            If Not boolFound Then arrElemInArray = False: Exit Function
        Next j
   Next i
   arrElemInArray = True
End Function