Excel VBA two array

[help_for_excel]

Hi,

I have two arrays:

Dim arr_1 (1000)
Dim arr_2 (1000)

arr_1 = {abc, xyz, mnc}
arr_2 = {abc, xyz, pqr}


I want VBA code that compares both arrays and string found in arr_1 but not found in arr_2 should pop up in message box. so here mesg box should pop-up "mnc"


please help.....thanks in advance

 

[tigeravatar]

help_for_excel,

One way...

Sub tgr()
    
    Dim arr_1
    Dim arr_2
    Dim i As Integer
    Dim MatchFound As Integer
    Dim strNotFound As String
    
    arr_1 = Array("abc", "xyz", "mnc")
    arr_2 = Array("abc", "xyz", "pqr")
    
    On Error Resume Next
    For i = LBound(arr_1) To UBound(arr_1)
        MatchFound = 0
        MatchFound = WorksheetFunction.Match(arr_1(i), arr_2, 0)
        If MatchFound = 0 Then strNotFound = strNotFound & ", " & arr_1(i)
    Next i
    
    If Len(strNotFound) > 0 Then
        strNotFound = Right(strNotFound, Len(strNotFound) - 2)
        MsgBox strNotFound
    End If
    
End Sub

 

[help_for_excel]

thanks it works great.

Just one quick question. it will work even i increase array size to 1000 or 2000 etc

 

[tigeravatar]

help_for_excel,

It should be able to handle that, yes. Let me know if it doesn't though.

 

[chirp]

will but to be safe and avoid the ~30,000 integer type overflow you should use longs. there are not to many situations in which integers are superior.

 

[Rick Rothstein]

Here is a slightly shorter macro that you can consider using...

Sub Array1ElementsNotInArray2()
  Dim X As Long, Arr1 As Variant, Arr2 As Variant, Combined As String, Result As String
  Arr1 = Array("abc", "xyz")
  Arr2 = Array("abc", "xyz", "pqr")
  Combined = Chr(1) & Join(Arr2, Chr(1)) & Chr(1)
  For X = LBound(Arr1) To UBound(Arr1)
    If InStr(Combined, Chr(1) & Arr1(X) & Chr(1)) = 0 Then Result = Result & ", " & Arr1(X)
  Next
  Result = Mid(Result, 3)
  If Len(Result) Then MsgBox Result
End Sub

 

[chirp]

here is a slightly longer function you could consider using

uses dictionaries so should be slower for smaller (~1-100) lists and better for larger lists (>~100)

'so:
'(0): in arr1 not arr2
'(1): in arr2 not arr1
'(2): in both
'note: could just use array(udic1.keys, etc) to assign return val,
'but like to keep 0 base consistent
Function getDifferencesA(arr1, arr2, _
                                    Optional skipBlanks As Boolean = True, _
                                    Optional matchCase As Boolean = True, _
                                    Optional tst As Boolean) As Variant

Dim uDic1 As Object, uDic2 As Object, uDic3 As Object
Dim Var As Variant
Dim tmpArr(0 To 2) As Variant
Dim funcTest As Boolean
Dim tStr As String

tst = False
On Error GoTo exitFunc

'if either range is nothing then exit (this is a custom function just to check if arrays are one dim, initialized, simple)
'If Not validArray(arr1) Then GoTo exitFunc
'If Not validArray(arr2) Then GoTo exitFunc

'creates 3 dics using late binding...increase speed set reference
Set uDic1 = CreateObject("scripting.dictionary")
Set uDic2 = CreateObject("scripting.dictionary")
Set uDic3 = CreateObject("scripting.dictionary")

If Not matchCase Then uDic1.compareMode = vbTextCompare: uDic2.compareMode = vbTextCompare: uDic3.compareMode = vbTextCompare

For Each Var In arr1
    tStr = CStr(Var)
    If skipBlanks Then If tStr = vbNullString Then GoTo skipAdd
    uDic1(tStr) = Empty
skipAdd:
Next

'not sure if this is the best way, but it is certainly A way
With uDic1
    For Each Var In arr2
        tStr = CStr(Var)
        If skipBlanks Then If tStr = vbNullString Then GoTo skipAdd1
        If Not .Exists(tStr) Then
            If Not uDic3.Exists(tStr) Then uDic2(tStr) = Empty
        Else: uDic3(tStr) = Empty: .Remove tStr
        End If
skipAdd1:
    Next
    'the return of this could be changed..ie specified, so as not to return unwanted
    'params...however making it consistent is ok too
    'add this so as always to return 0 based
    tmpArr(0) = .Keys
    tmpArr(1) = uDic2.Keys
    tmpArr(2) = uDic3.Keys
End With

'assign return val
getDifferencesA = tmpArr
tst = True
exitFunc:
End Function

to get a result as a string you could use this like:

sub test()
dim v1 as variant
v1 = Join(getDifferencesA(array(1,2,3,4), array(1,3,4,5,6))(0), ", ")
end sub