How do I Merge two Arrays in VBA?

Question

Given

Dim arr1 As Variant
Dim arr2 As Variant
Dim arr3 As Variant

arr1 = Array("A", 1, "B", 2)
arr2 = Array("C", 3, "D", 4)

Question

What kind of operations can I perform on arr1 and arr2 and assign the result to arr3 getting something like that:

arr3 = ("A", "C", 1, 3, "B", "D", 2, 4)

Hint (due to comment): "1) the elements in arr1 are names and in arr2 are values, the final elements in arr3 are actually name-value pairs, so as long as they as paired I won't care if they are not in order."

Solution 1

Unfortunately, the Array type in VB6 didn't have all that many razzmatazz features. You are pretty much going to have to just iterate through the arrays and insert them manually into the third

Assuming both arrays are of the same length

Dim arr1() As Variant
Dim arr2() As Variant
Dim arr3() As Variant

arr1() = Array("A", 1, "B", 2)
arr2() = Array("C", 3, "D", 4)

ReDim arr3(UBound(arr1) + UBound(arr2) + 1)

Dim i As Integer
For i = 0 To UBound(arr1)
    arr3(i * 2) = arr1(i)
    arr3(i * 2 + 1) = arr2(i)
Next i

Updated: Fixed the code. Sorry about the previous buggy version. Took me a few minutes to get access to a VB6 compiler to check it.

Solution 2

Try this:

arr3 = Split(Join(arr1, ",") & "," & Join(arr2, ","), ",") 

Solution 3

This function will do as JohnFx suggested and allow for varied lengths on the arrays

Function mergeArrays(ByVal arr1 As Variant, ByVal arr2 As Variant) As Variant
    Dim holdarr As Variant
    Dim ub1 As Long
    Dim ub2 As Long
    Dim bi As Long
    Dim i As Long
    Dim newind As Long

        ub1 = UBound(arr1) + 1
        ub2 = UBound(arr2) + 1

        bi = IIf(ub1 >= ub2, ub1, ub2)

        ReDim holdarr(ub1 + ub2 - 1)

        For i = 0 To bi
            If i < ub1 Then
                holdarr(newind) = arr1(i)
                newind = newind + 1
            End If

            If i < ub2 Then
                holdarr(newind) = arr2(i)
                newind = newind + 1
            End If
        Next i

        mergeArrays = holdarr
End Function

Solution 4

I tried the code provided above, but it gave an error 9 for me. I made this code, and it worked fine for my purposes. I hope others find it useful as well.

Function mergeArrays(ByRef arr1() As Variant, arr2() As Variant) As Variant

    Dim returnThis() As Variant
    Dim len1 As Integer, len2 As Integer, lenRe As Integer, counter As Integer
    len1 = UBound(arr1)
    len2 = UBound(arr2)
    lenRe = len1 + len2
    ReDim returnThis(1 To lenRe)
    counter = 1

    Do While counter <= len1 'get first array in returnThis
        returnThis(counter) = arr1(counter)
        counter = counter + 1
    Loop
    Do While counter <= lenRe 'get the second array in returnThis
        returnThis(counter) = arr2(counter - len1)
        counter = counter + 1
    Loop

mergeArrays = returnThis
End Function

Solution 5

It work if Lbound is different than 0 or 1. You Redim once at start

Function MergeArrays(ByRef arr1 As Variant, ByRef arr2 As Variant) As Variant

'Test if not isarray then exit
If Not IsArray(arr1) And Not IsArray(arr2) Then Exit Function

Dim arr As Variant
Dim a As Long, b As Long 'index Array
Dim len1 As Long, len2 As Long 'nb of item

'get len if array don't start to 0
len1 = UBound(arr1) - LBound(arr1) + 1
len2 = UBound(arr2) - LBound(arr2) + 1

b = 1 'position of start index
'dim new array
ReDim arr(b To len1 + len2)
'merge arr1
For a = LBound(arr1) To UBound(arr1)
    arr(b) = arr1(a)       
    b = b + 1 'move index
Next a
'merge arr2
For a = LBound(arr2) To UBound(arr2)
    arr(b) = arr2(a)
    b = b + 1 'move index
Next a

'final
MergeArrays = arr

End Function

View more solutions

Comments

[vmasule]

Summing the UBounds gives a size off-by-one, and the write index into the output array should be stepped separately from read index into the source arrays. Let this be a lesson in how annoying VBA arrays can be to work with!

[vmasule]

Just to expound on annoying VBA arrays...especially when combining Excel and VBA, the main thing you need to know is that arrays can have arbitrary lower bounds. If you don't specifiy one, then the LB is set by the Option Base setting. But arrays created with Array() and ParamArrays always have a LB of 0. Arrays passed from Excel always have a LB of 1. It's no big deal when iterating a single array - use For Each or LBound and UBound - but working with two arrays at once suddenly means you have to think about details like bounds and indices...

[Mike Woodhouse]

I'm not recommending it, but default LB can be set to 1 if you use Option Base 1

[vmasule]

I really hate "Option Base". It's like a mysterious action at a distance, module-by-module, just to avoid typing in a lower bound. I know it pre-dates VB/VBA, though, and was relevant once upon a time...

[vmasule]

I was wrong about the LB of an array created with Array() in my comment above. It is affected by the Option Base setting. ParamArrays are not, though.

[seadoggie01]

Note to anyone finding this... it actually merges the arrays... doesn't keep the order of the elements if that's important to you.

[Malan Kriel]

For in case, the arrays contain a comma, can use Split(Join(arr1, Chr(1)) & Chr(1) & Join(arr2, Chr(1)), Chr(1))

[Naresh]

Suitable for 2 one dimensional arrays of same or different sizes. Results could be checked with Debug.Print Join(Array1, ",") Debug.Print Join(Array2, ",") Debug.Print Join(JointArray, ",")

[Elikill58]

Can you edit your answer to explain why and how this code will fix the question ?

[T.M.]

Fyi Posted an extension to your fine solution, using the new ArrayToText() function and allowing to return numeric values as further benefit. @user3286479