Populating array with items from another array throws TypeMismatch Error-CodePudding

for belows code the line vItemsNotInMaster(k) = vCheckItems(i) throws a type mismatch error once the array vItemsNotInMaster shall be populated. I am not sure why - as the caller sub and function array variables are all declared as Variants and types did not change according to the Locals Window.

I tried different data types but, this does throw other error messages.

Public Sub Testing()
Dim myArray1(1 To 4) As Variant
Dim myArray2(1 To 4) As Variant
Dim myArray3 As Variant

   myArray1(1) = "one1"
   myArray1(2) = "two3"
   myArray1(3) = "three5"
   myArray1(4) = "four7"

   myArray2(1) = "one1"
   myArray2(2) = "two3"
   myArray2(3) = "different"
   myArray2(4) = "four7"
   
   myArray3 = Comparing_TwoArrays(myArray1, myArray2)
   Stop
End Sub

Public Function Comparing_TwoArrays(ByVal vCheckItems As Variant, ByVal vMasterList As Variant) As Variant

   Dim vItemsNotInMaster As Variant
   Dim isMatch As Boolean
   Dim i As Integer
   Dim j As Integer
   Dim k As Integer
   
   ReDim vArray3(1 To UBound(vCheckItems, 1)   UBound(vMasterList, 1))
   k = 1
   
   For i = LBound(vCheckItems, 1) To UBound(vCheckItems, 1)
       isMatch = False
       
       For j = LBound(vMasterList, 1) To UBound(vMasterList, 1)
           If vCheckItems(i) = vMasterList(j) Then
               isMatch = True
               Exit For
           End If
       Next j
       
       If (isMatch = False) Then
           vItemsNotInMaster(k) = vCheckItems(i) '---> Throws type mismatch
           k = k   1
       End If
   Next i
   
   If (k > 1) Then
      ReDim Preserve vArray3(1 To k - 1)
   Else
      vArray3 = Empty
   End If
   
   Comparing_TwoArrays = vArray3

End Function

Does someone has an idea?

Code Example credited to: https://bettersolutions.com/vba/arrays/comparing.htm

CodePudding user response：

As I said in my comment, replacing vItemsNotInMaster(k) = vCheckItems(i) with vArray3(k) = vCheckItems(i) will solve the problem.

But if you need learning arrays manipulation, the next more compact code returns the same in less code lines number:

Public Sub Testing_()
Dim myArray1(1 To 4) As String
Dim myArray2(1 To 4) As String
Dim myArray3 As Variant

   myArray1(1) = "one1"
   myArray1(2) = "two2"
   myArray1(3) = "three5"
   myArray1(4) = "four7"

   myArray2(1) = "one1"
   myArray2(2) = "two3"
   myArray2(3) = "different"
   myArray2(4) = "four7"
   

   myArray3 = Application.IfError(Application.match(myArray1, myArray2, 0), "x") 'it palces "x" when not a match...
   Debug.Print Join(myArray3, "|") 'just to visually see the return...
   'for a single case:
   Debug.Print "(first) missing element: " & myArray1(Application.match("x", myArray3, 0)) 'it returns according to the first occurrence
   
   'For more than one missing occurrence:
   Dim i As Long
   For i = 1 To UBound(myArray3)
        If myArray3(i) = "x" Then
            Debug.Print "Missing: " & myArray1(i)
        End If
   Next i
End Sub

To return occurrences independent of array elements position, it is also simpler to use Application.Match (with a single iteration). If interested, I can also post such a function...

CodePudding user response：

As pointed out by @FunThomas the function does not return anything. Fix for type mismatch error is to Redim the vItemsNotInMaster array for each new item, while preserving the already populated values.

The vArray3 variable does not make sense and function should be rewritten as:

Public Function Comparing_TwoArrays(ByVal vCheckItems As Variant, ByVal vMasterList As Variant) As Variant

   Dim vItemsNotInMaster()
   Dim isMatch As Boolean
   Dim i As Integer
   Dim j As Integer
   Dim k As Integer
   
   
   k = 1
   
   For i = LBound(vCheckItems, 1) To UBound(vCheckItems, 1)
       isMatch = False
       
       For j = LBound(vMasterList, 1) To UBound(vMasterList, 1)
           If vCheckItems(i) = vMasterList(j) Then
               isMatch = True
               Exit For
           End If
       Next j
       
       If (isMatch = False) Then
           ReDim Preserve vItemsNotInMaster(1 To k)
           vItemsNotInMaster(k) = vCheckItems(i) '---> Throws type mismatch
           k = k   1
       End If
   Next i
   
   
   Comparing_TwoArrays = vItemsNotInMaster

End Function

CodePudding user response：

Return Matching Array Elements

The function will return an array of the not matching elements from the check array in the master array.
If all elements are matching (are found in master), it will return an array whose upper limit is less than its lower limit.

Option Explicit

Public Sub Testing()
    
    Dim myArray1(1 To 4) As Variant
    Dim myArray2(1 To 4) As Variant
    Dim myArray3 As Variant
    
    myArray1(1) = "one1"
    myArray1(2) = "two3"
    myArray1(3) = "three5"
    myArray1(4) = "four7"
 
    myArray2(1) = "one1"
    myArray2(2) = "two3"
    myArray2(3) = "different"
    myArray2(4) = "four7"
   
    myArray3 = NotInMasterArray(myArray1, myArray2)
   
    If LBound(myArray3) <= UBound(myArray3) Then
        ' Column
        Debug.Print "Column" & vbLf & Join(myArray3, vbLf)
        ' Delimited row:
        Debug.Print "Row" & vbLf & Join(myArray3, ",")
    Else
        Debug.Print "All elements from Check array found in Master array."
    End If
   
   Stop
End Sub

Public Function NotInMasterArray( _
    arrCheck() As Variant, _
    arrMaster() As Variant, _
    Optional ByVal ResultLowerLimit As Variant) _
As Variant()
   
    ' Write the check array's limits to variables.
    Dim cLB As Variant: cLB = LBound(arrCheck)
    Dim cUB As Long: cUB = UBound(arrCheck)
    
    ' Determine the lower limit ('nLB') of the result array.
    Dim nLB As Long
    If IsMissing(ResultLowerLimit) Then ' use the check array's lower limit
        nLB = cLB
    Else ' use the given lower limit
        nLB = ResultLowerLimit
    End If
     
    ' Calculate the result array's upper limit.
    Dim nUB As Long: nUB = cUB - cLB   nLB
    ' Define the initial result array ('arrNot') making it the same size
    ' as the check array (it is possibly too big; it is only of the correct size,
    ' if all check array's elements are not found in the master array).
    Dim arrNot() As Variant: ReDim arrNot(nLB To nUB)
    ' Write the result array's lower limit decreased by 1 to the result
    ' array's limit counter variable (to first count and then write).
    Dim n As Long: n = nLB - 1
    
    Dim c As Long ' Check Array Limit Counter
    
    ' Loop through the elements of the check array.
    For c = cLB To cUB
        ' Check if the current element is not found in the master array.
        If IsError(Application.Match(arrCheck(c), arrMaster, 0)) Then
            n = n   1 ' count
            arrNot(n) = arrCheck(c) ' write
        'Else ' found in master; do nothing
        End If
    Next c
    
    If n < nLB Then ' all found in master
        arrNot = Array() ' i.e. UBound(arrNot) < LBound(arrNot)
    Else ' not all are found in master
        If n < nUB Then ' not all elements are not found...
            ReDim Preserve arrNot(nLB To n) ' ... resize to 'n'
        'Else ' all elements are not found; do nothing
        End If
    End If
    
    ' Assign the result array to the result of the function.
    NotInMasterArray = arrNot
    
End Function