Joining two arrays in vba?

Question

How do I combine these arrays with the outcome of (2, 4, 5, 3, 7, 6)?

array1 = Array(4,5,3,7,6)

array2 = Array(2)

Answer 1

Late to the party, but I'll also add my two cents

You could simply copy one of the two arrays into a new array. Then Redim Preserve that to be the size of the two original arrays to then loop only the first array. The following code is basic, but does the job quick without converting any data type:

Sub Test()

Dim arr1 As Variant: arr1 = Array(4, 5, 3, 7, 6)
Dim arr2 As Variant: arr2 = Array(2)
Dim arr3 As Variant: arr3 = arr2

ReDim Preserve arr3(UBound(arr1) + Ubound(arr2) + 1)
For x = (UBound(arr3) - UBound(arr1)) To UBound(arr3)
    arr3(x) = arr1(x - UBound(arr2) - 1)
Next x

End Sub

---

To demonstrate the return of different Data Type using some Type conversions:

Sub Test()

Dim arr1 As Variant: arr1 = Array(CDbl(4), CLng(5), CStr(3), CDate(7), CCur(6))
Dim arr2 As Variant: arr2 = Array(2)
Dim arr3 As Variant: arr3 = arr2

ReDim Preserve arr3(UBound(arr1) + Ubound(arr2) + 1)
For x = (UBound(arr3) - UBound(arr1)) To UBound(arr3)
    arr3(x) = arr1(x - UBound(arr2) - 1)
Next x

End Sub

Answer 2

Joining two arrays

As an alternative to the correct and working approach proposed by Scott Craner

Create a third array that is empty the size of both arrays combined, then loop through each array adding the items one by one.

... I demonstrate a way to

• insert only the element(s) of the 2nd array by a loop into a main array, whereas
• the main array gets only restructured by a one liner via Application.Index().

As this function would change results to a 1-based array, I redimension the array back to a zero-based one. Furthermore I added an optional display in the VBE's Immediate Window resulting to 2|4|5|3|7|6 values:

1st step: Simple demo with same array values as in OP (Insertion of 1 element)

Sub SimpleDemo()
'[0]declare and assign zero-based 1-dimensioned arrays
    Dim main, newTop
    main = Array(4, 5, 3, 7, 6)
    newTop = Array(2)              ' only one element in a first step
'[1]transform main array by inserting(/i.e. repeating) "another" 1st element
    main = Application.Index(main, Array(1, 1, 2, 3, 4, 5)) ' changes to 1-based 1-dim array
    ReDim Preserve main(0 To UBound(main) - 1)              ' back to zero-based 1-dim array
'[2]overwrite new first element by the 1st(only) element of newTop
    main(0) = newTop(0)
'[3](optional) display in VBE's Immediate Window: main(0 To 5) ~> 2|4|5|3|7|6
    Debug.Print "main(" & LBound(main) & " To " & UBound(main) & ") ~> " & _
                Join(main, "|")
End Sub

2nd step: More generalized approach using a AddElem procedure

The above demo inserts only one element. Therefore I coded a AddElem procedure and a help function addedElems() to allow the insertion of more elements. Assumption is made that all 1-dim arrays are zero-based as in the original post; could be adapted easily btw :-)

Sub AddElem(main, newTop)
' Purp. : add/insert other array element(s) on top of zero-based main array
' Author: https://stackoverflow.com/users/6460297/t-m
' Date  : 2020-02-05
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' a)insert newTop element(s) on top of main array
    main = Application.Index(main, addedElems(main, newTop)) ' changes temporarily to 1-based mainay!
' b)make main array zero-based again (optional)
    ReDim Preserve main(0 To UBound(main) - 1)
' c)overwrite inserted starting element(s) by the newTop element(s)
    Dim i&: For i = 0 To UBound(newTop): main(i) = newTop(i): Next i
End Sub

Help function addedElems()

Function addedElems(main, newTop) As Variant()
'Note : help function called by AddElem()
'Purp.: return ordinal element counters of combined arrays
    Dim i&, n&: n = UBound(main) + UBound(newTop) + 1
    ReDim tmp(0 To n)
    For i = 0 To UBound(newTop): tmp(i) = i: Next i
    For i = i To n: tmp(i) = i - UBound(newTop): Next i
    addedElems = tmp        ' return combined elem counters,  e.g. Array(1,2, 1,2,3,4,5)
End Function

Example call

I changed the values of OP's second array slightly (Array(2) ~>Array(20,21) to demonstrate the insertion of more elements, thus resulting in a combined Array(20,21,2,4,5,3,7,6).

Sub ExampleCall()
'[0]declare and assign zero-based 1-dimensional arrays
    Dim main, newTop
    main = Array(4, 5, 3, 7, 6)
    newTop = Array(20, 21)
'[1]Add/Insert newTop on top of main array
    AddElem main:=main, newTop:=newTop     ' or simply: AddElem main, newTop

'[2](optional) display in VBE's Immediate Window: ~~> main(0 To 6) ...20|21|4|5|3|7|6
    Debug.Print "main(" & LBound(main) & " To " & UBound(main) & ") ..." & _
                Join(main, "|")
End Sub

Related link

Similarly you can study some pecularities of the Application.Index() function applied on 2-dim arrays at Insert first column in datafield array without loops or API calls

Answer 3

You could potentially Join() and concatenate your two arrays, and then Split() the result back to a new array:

array3 = Split(Join(array2, ",") & "," & Join(array1, ","), ",")

Explanation:
Join() will return a string that has each element in the array (first parameter) delimited by a "," (second parameter). We concatenate those two joined arrays with one more comma to get a string like 2,4,5,3,7,6. We then use Split() to turn that string back into an array telling Split() that the delimter is a comma ",".

Answer 4

You could use arrayLists. This also provides for an easy sort if wanted.

Option Explicit
Public Sub test()
    Dim list1 As Object, list2 As Object

    Set list1 = CreateObject("System.Collections.Arraylist")
    Set list2 = CreateObject("System.Collections.Arraylist")
    list1.Add 4
    list1.Add 5
    list1.Add 3
    list1.Add 7
    list1.Add 6
    list2.Add 2
    list1.addRange list2
    list1.Sort
End Sub