Combining 2D (2-dimensional) arrays

Question

I am using VBA in Excel to consume an XML file and dump specific information into individual tabs. I want to be able to combine 2-dimensional arrays. The arrays have a "known" number of columns but an "unknown" number of rows. Consider the following two arrays:

array1:

a    b    c
d    e    f

array2:

1    2    3
4    5    6

How do I combine these to arrays if I want the following result:

array3:

a    b    c
d    e    f
1    2    3
4    5    6

And just out of curiosity, how would I code if instead I wanted to add to the right instead of the bottom, like this:

array4:

a    b    c    1    2    3
d    e    f    4    5    6

I can't seem to find the answer to this anywhere.

Please keep in mind my example above is rather small, but in reality, I'm trying to do this with approx 100,000 rows of data at once. There are only six columns of data, if that matters.

The goal here is to assemble a large array and then write it to an Excel sheet all in one step because when I do it in pieces the performance is really poor.

If possible, I'd prefer a solution that does not require iteration.

The reason I ask about both ways is that in reality I want to add kind of sequentially. For instance, assume I have four arrays, A, B, C, D.

First, add array A:

A

Then, add array B:

A    B

Then, add array C:

A    B
C

Then, add array D:

A    B
C    D

and so forth...

Keep in mind that each of the above arrays would be sized such that they "fit" correctly meaning A and B have the same number of rows, but different number of columns. A and C on the other hand have the same number of columns but a different number of rows. And so on...

I wanted to add a demonstration using Macro Man's code from below. Here is what he provided (I added a bit so readers can just copy/paste):

Option Explicit

Sub Testing()

    Dim Array1(0 To 1, 0 To 2) As String
    Array1(0, 0) = "a"
    Array1(0, 1) = "b"
    Array1(0, 2) = "c"
    Array1(1, 0) = "d"
    Array1(1, 1) = "e"
    Array1(1, 2) = "f"

    Dim Array2(0 To 1, 0 To 2) As String
    Array2(0, 0) = "1"
    Array2(0, 1) = "2"
    Array2(0, 2) = "3"
    Array2(1, 0) = "4"
    Array2(1, 1) = "5"
    Array2(1, 2) = "6"

    Dim i As Long
    For i = 1 To 25000

        With Range("A" & Rows.Count).End(xlUp).Offset(IIf(IsEmpty([A1]), 0, 1), 0)
            .Resize(UBound(Array1, 1) - LBound(Array1, 1) + 1, _
                    UBound(Array1, 2) - LBound(Array1, 2) + 1).Value = Array1
        End With

        With Range("A" & Rows.Count).End(xlUp).Offset(IIf(IsEmpty([A1]), 0, 1), 0)
            .Resize(UBound(Array2, 1) - LBound(Array2, 1) + 1, _
                    UBound(Array2, 2) - LBound(Array2, 2) + 1).Value = Array2
        End With

    Next i

End Sub

When you run the above code, which goes back to the spreadsheet each time to write the small amount of data, this takes a long time to run. On my dual Xeon machine, like 25-30 seconds.

However, if you rewrite and populate the array FIRST, then write to the spreadsheet ONCE, it runs in about one second.

Option Explicit

Sub Testing()

    Dim Array1(0 To 99999, 0 To 2) As String
    Array1(0, 0) = "a"
    Array1(0, 1) = "b"
    Array1(0, 2) = "c"
    Array1(1, 0) = "d"
    Array1(1, 1) = "e"
    Array1(1, 2) = "f"

    Dim i As Long
    For i = 0 To 99999

        Array1(i, 0) = "a"
        Array1(i, 1) = "b"
        Array1(i, 2) = "c"

    Next i

    With Range("A" & Rows.Count).End(xlUp).Offset(IIf(IsEmpty([A1]), 0, 1), 0)
        .Resize(UBound(Array1, 1) - LBound(Array1, 1) + 1, _
                UBound(Array1, 2) - LBound(Array1, 2) + 1).Value = Array1
    End With

End Sub

I would like to see a solution which does the same thing, except being able to add "chunks" of data instead of individual items. Adding arrays to bigger arrays, ideally. Even better would be if the "parent" array somehow dynamically resized itself.

John Coleman's answer below worked great.

I actually combined a bit of Macro Man's with John's test() subroutine and this dynamically re-sizes the range:

Option Explicit

Sub test()
    Dim A As Variant, B As Variant
    ReDim A(0 To 1, 0 To 1)
    ReDim B(0 To 1, 0 To 1)
    A(0, 0) = 1
    A(0, 1) = 2
    A(1, 0) = 3
    A(1, 1) = 4
    B(0, 0) = 5
    B(0, 1) = 6
    B(1, 0) = 7
    B(1, 1) = 8

    Dim Array1 As Variant
    Array1 = Combine(A, B)

    With Range("A" & Rows.Count).End(xlUp).Offset(IIf(IsEmpty([A1]), 0, 1), 0)
    .Resize(UBound(Array1, 1) - LBound(Array1, 1) + 1, _
            UBound(Array1, 2) - LBound(Array1, 2) + 1).Value = Array1
    End With
End Sub

Answer 1

Here is a VBA function that can combine two 2-dimensional arrays into a single 2-dimensional array. It can be used either from VBA or as an array-formula directly in Excel. Iteration is unavoidable here in VBA since the language doesn't have primitives for things like concatenating arrays:

Function Combine(A As Variant, B As Variant, Optional stacked As Boolean = True) As Variant
    'assumes that A and B are 2-dimensional variant arrays
    'if stacked is true then A is placed on top of B
    'in this case the number of rows must be the same,
    'otherwise they are placed side by side A|B
    'in which case the number of columns are the same
    'LBound can be anything but is assumed to be
    'the same for A and B (in both dimensions)
    'False is returned if a clash

    Dim lb As Long, m_A As Long, n_A As Long
    Dim m_B As Long, n_B As Long
    Dim m As Long, n As Long
    Dim i As Long, j As Long, k As Long
    Dim C As Variant

    If TypeName(A) = "Range" Then A = A.Value
    If TypeName(B) = "Range" Then B = B.Value

    lb = LBound(A, 1)
    m_A = UBound(A, 1)
    n_A = UBound(A, 2)
    m_B = UBound(B, 1)
    n_B = UBound(B, 2)

    If stacked Then
        m = m_A + m_B + 1 - lb
        n = n_A
        If n_B <> n Then
            Combine = False
            Exit Function
        End If
    Else
        m = m_A
        If m_B <> m Then
            Combine = False
            Exit Function
        End If
        n = n_A + n_B + 1 - lb
    End If
    ReDim C(lb To m, lb To n)
    For i = lb To m
        For j = lb To n
            If stacked Then
                If i <= m_A Then
                    C(i, j) = A(i, j)
                Else
                    C(i, j) = B(lb + i - m_A - 1, j)
                End If
            Else
                If j <= n_A Then
                    C(i, j) = A(i, j)
                Else
                    C(i, j) = B(i, lb + j - n_A - 1)
                End If
            End If
        Next j
    Next i
    Combine = C
End Function

I tested it in 4 different ways. First I entered your two example arrays in the spreadsheets and used Combine directly in excel as an array formula:

Here A7:C10 contains the array formula

{=combine(A1:C2,A4:C5)}

and A12:F13 contains the array formula

{=combine(A1:C2,A4:C5,FALSE)}

Then, I ran the following sub:

Sub test()
    Dim A As Variant, B As Variant
    ReDim A(0 To 1, 0 To 1)
    ReDim B(0 To 1, 0 To 1)
    A(0, 0) = 1
    A(0, 1) = 2
    A(1, 0) = 3
    A(1, 1) = 4
    B(0, 0) = 5
    B(0, 1) = 6
    B(1, 0) = 7
    B(1, 1) = 8    
    Range("A15:B18").Value = Combine(A, B)
    Range("C15:F16").Value = Combine(A, B, False)    
End Sub

Output:

Answer 2

If possible, I'd prefer a solution that does not require iteration.

Try this:

Function Combine(m, n)
    Dim m1&, m2&, n1&, n2&
    m1 = UBound(m, 1): m2 = UBound(m, 2)
    n1 = UBound(n, 1): n2 = UBound(n, 2)
    With Worksheets.Add
        .[a1].Resize(m1, m2) = m
        .[a1].Resize(n1, n2).Offset(m1) = n
        Combine = .[a1].Resize(m1 + n1, m2)
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        .Delete
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    End With
End Function

Note: this is just a demo to show proof of concept. Currently it does vertical stacking of two 2d arrays. Simple to modify to also do horizontal stacking.

Note: I'm typically opposed to this sort of thing, but if you think about it, an Excel sheet is analogous to a really big 2d array and while this is indeed a sleghammer approach, it is quick and there is no iteration!