Excel vba - joining two arrays

Question

I need to join two arrays vd and vd1 into vdu.

ReDim vdu(1 To (UBound(vd, 1) + UBound(vd1, 1)), 1 To 1)

For i = 1 To UBound(vd, 1)
   vdu(i, 1) = vd(i, 1)
Next i

For j = i To UBound(vdu, 1)
   vdu(j, 1) = vd1(j - i + 1, 1)
Next j

First, I get and "out of range" error.

Second, in the end I will have 18 arrays that I'll need to join, so I don't know if this is the best idea for joining them.

Which line do you get the `out of range` error on? Also are the arrays 1D or 2D? — Tom, Sep 05 '17 at 09:44

Tom · Accepted Answer · 2017-09-05T11:04:07.023

try with

    ReDim vdu(LBound(vd) To UBound(vd) + UBound(vd1), 1 To 1)

    For i = LBound(vdu) To UBound(vdu)
        If i <= UBound(vd) Then
            vdu(i, 1) = vd(i, 1)
        Else
            vdu(i, 1) = vd1(i - UBound(vd), 1)
        End If
    Next i

Update for second part of question I'd convert your merge code into a function

Public Function MergeArrays(arr1 As Variant, arr2 As Variant) As Variant
    Dim i As Long
    Dim arr As Variant
    ReDim arr(LBound(arr1, 1) To UBound(arr1, 1) + UBound(arr2, 1), 1 To 1)

    For i = LBound(arr, 1) To UBound(arr, 1)
        If i <= UBound(arr1, 1) Then
            arr(i, 1) = arr1(i, 1)
        Else
            arr(i, 1) = arr2(i - UBound(arr1, 1), 1)
        End If
    Next i
    MergeArrays = arr
End Function

And then pass each array to it 1 at a time e.g.

arr = MergeArrays(vd1, vd2)
arr = MergeArrays(arr, vd3)
arr = MergeArrays(arr, vdx)

You could loop through this by storing your arrays in an array or dictionary and looping through that instead as well

Other option

Public Function MergeArrays(ParamArray arrays() As Variant) As Variant
    Dim i As Long, j As Long, cnter As Long, UBoundArr As Long, OldUBoundArray As Long
    Dim arr() As Variant

    For j = LBound(arrays) To UBound(arrays)
        UBoundArr = UBoundArr + UBound(arrays(j), 1)
    Next j

    ReDim arr(1 To UBoundArr, 1 To 1)
    For j = LBound(arrays) To UBound(arrays)
        For i = LBound(arrays(j)) To UBound(arrays(j))
            arr(i + OldUBoundArray, 1) = arrays(j)(i, 1)
        Next i
        OldUBoundArray = OldUBoundArray + UBound(arrays(j), 1)
    Next j

    MergeArrays = arr
End Function

This method uses a ParamArray. If you're not sure what that is look it up but effectively you're able to pass an unspecified amount of arguments to the function. Therefore with this function you can combine any amount of arrays (of the same shape and same base i.e. x to x, 1 to 1) and it will combine them. Call like

arr = MergeArrays(vd, vd1, vd2,....,vd18)

Yes, this works. Thanks! What do you think about second question. — user155754, Sep 05 '17 at 10:22
It depends what the arrays are. Are they all going to be the same shape? and are you joining them in 2's so that you'll end up with 9 arrays or do you mean you'll be joining all 18? — Tom, Sep 05 '17 at 10:24
Maybe, the "best" way is now to add 3rd to joined first two and so on. — user155754, Sep 05 '17 at 10:33
Had another thought. Have a look at my second update. It will allow you to merge all 18 arrays in one line by using what is known as a Param Array. Google it if you don't know but effectively you can pass any number of arguments to the function in one go without specifying them initially. Can be very useful in situations like this — Tom, Sep 05 '17 at 11:05

score 0 · Answer 2 · 2017-09-05T11:15:25.707

getMasterArray will return an array that combines up to 60 different 2d arrays into one. getMasterArray also give you the option of returning a 0 or based array.

Sub TestgetMasterArray()
    Dim data
    data = getMasterArray(False, Range("List1").Value, Range("List2").Value, Range("List3").Value, Range("List4").Value)
    Worksheets("Result").Range("A1").Resize(UBound(data), UBound(data, 2)).Value = data
End Sub

Function getMasterArray(Base0 As Boolean, ParamArray Arrays() As Variant)
    Dim result As Variant, v As Variant
    Dim Count As Long, Count2 As Long, lowBound As Integer, lOffset As Integer, x As Long, x1 As Long, y As Long

    For Each v In Arrays
        Count = Count + UBound(v) + IIf(LBound(v) = 0, 1, 0)
        y = UBound(v, 2) + IIf(LBound(v, 2) = 0, 1, 0)
        If y > Count2 Then Count2 = y
    Next

    lowBound = IIf(Base0, 0, 1)
    ReDim result(lowBound To Count, lowBound To Count2)

    For Each v In Arrays
        If LBound(v, 2) > LBound(result, 2) Then
            lOffset = -1
        ElseIf LBound(v, 2) < LBound(result, 2) Then
            lOffset = 1
        End If

        For x = LBound(v) To UBound(v)
            For y = LBound(v, 2) To UBound(v, 2)
                result(lowBound, y + lOffset) = v(x, y)
            Next
            lowBound = lowBound + 1
        Next
    Next
    getMasterArray = result
End Function

Sample data generated by ockaroo.com

Excel vba - joining two arrays

2 Answers2

Linked