Combining Multiple Arrays in VBA

Question

I am currently trying to combine 46 arrays in to a single array. I have scoured the internet, to no prevail and am hoping someone here can help. I did find the below page, but I need to be able to look through each element of the new array in a nested for loop, so using the method below doesn't quite get me to my end goal.

Excel vba - combine multiple arrays into one

Basically, I need to combine my set of 46 arrays in such a way that I can then loop through each element using a nested for loop. ie.

Set of arrays:

myArray1 = (1, 2, 3, 4)
myArray2 = (5, 6, 7)
myArray3 = (8, 9)
myArray4 = (10, 11, 12, 13, 14)
.
.
.
myArray46 = (101, 102, 103)

Combine them to form new array:

myNewArray = (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14... 101, 102, 103)

Loop through in nested for loop to check each element against my main array:

For i = LBound(mainArray) to UBound(mainArray)
    For j = LBound(myArray) to UBound(myArray)

    If mainArray(i) = myArray(j) Then
    'do something
    End If

    Next j
Next i

Any help and/ or guidance with this is greatly appreciated!

Answer 1

Since you write in your comments that your end goal is to create an array of unique elements, you might be best served using a dictionary, where you can test for uniqueness as you add each element to dictionary. Something like:

Option Explicit
Function uniqueArr(ParamArray myArr() As Variant) As Variant()
    Dim dict As Object
    Dim V As Variant, W As Variant
    Dim I As Long

Set dict = CreateObject("Scripting.Dictionary")
For Each V In myArr 'loop through each myArr
    For Each W In V 'loop through the contents of each myArr
        If Not dict.exists(W) Then dict.Add W, W
    Next W
Next V


uniqueArr = dict.keys

End Function

Sub tester()
    Dim myArray1, myArray2, myArray3, myArray4, myArray5
    myArray1 = Array(1, 2, 3, 4)
    myArray2 = Array(5, 6, 7, 8)
    myArray3 = Array(9, 10, 11, 12, 13, 14)
    myArray4 = Array(15, 16)
    myArray5 = Array(1, 3, 25, 100)

Dim mainArray

mainArray = uniqueArr(myArray1, myArray2, myArray3, myArray4, myArray5)

End Sub

If you run Tester, you will see mainArray contains:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
25
100

Answer 2

Using your data this is how to create one array out of many:

Public Sub TestMe()

    Dim myA, myB, myC, myD, myE
    myA = Array(1, 2, 3, 4)
    myB = Array(5, 6, 7)
    myC = Array(8, 9)
    myD = Array(10, 11, 12, 13, 14)
    myE = Array(101, 102, 103)

    Dim myCombine As Variant
    Dim myNew() As Variant

    Dim myElement As Variant
    Dim myArr As Variant
    Dim cnt As Long

    myCombine = Array(myA, myB, myC, myD, myE)

    For Each myArr In myCombine
        For Each myElement In myArr
            ReDim Preserve myNew(cnt)
            myNew(cnt) = myElement
            cnt = cnt + 1
        Next
    Next

    For cnt = LBound(myNew) To UBound(myNew)
        Debug.Print myNew(cnt)
    Next cnt

End Sub

The "building" of the new array is facilitated through ReDim Preserve, which keeps the old values in the array whenver the dimension of the array changes. And if you want to do something with these arrays, you may use 3 nested loops (a bit slow) and have some check:

Dim cnt2 As Long
For cnt = LBound(myNew) To UBound(myNew)
    For cnt2 = LBound(myCombine) To UBound(myCombine)
        For Each myElement In myCombine(cnt2)
            If myElement = myNew(cnt) Then
                Debug.Print myElement & vbTab & " from " & vbTab & cnt2
            End If
        Next myElement
    Next cnt2
Next cnt

This is what you get on the immediate window:

1    from   0
2    from   0
3    from   0
4    from   0
5    from   1
6    from   1
7    from   1
8    from   2
9    from   2
10   from   3
11   from   3
12   from   3
13   from   3
14   from   3
101  from   4
102  from   4
103  from   4