Combining Arrays

Juan Pablo’s ArrayUnion function. He posted it in a comment, but since I know I’ll be looking for it later, I’m putting it in the Code Library.

Pass two arrays as arguments and it will return one array that contains all the elements of the two arrays passed.

Function ArrayUnion(ByVal va1 As Variant, ByVal va2 As Variant) As Variant
    Dim i As Long, Upper As Long
    If TypeName(va1) = “Empty” Then
        va1 = va2
        Upper = UBound(va1)
        If LBound(va2) = 0 Then Upper = Upper + 1
        ReDim Preserve va1(LBound(va1) To UBound(va1) + UBound(va2) – LBound(va2) + 1)
        For i = LBound(va2) To UBound(va2)
            va1(Upper + i) = va2(i)
        Next i
    End If
    ArrayUnion = va1
End Function
Posted in Uncategorized

12 thoughts on “Combining Arrays

  1. I was admiring this code, and came up with the following puzzle…

    I regularly use multi-dimensional arrays, would it be possible to write a generalised function/procedure to merge two of these?

    I can/have created the rank/dimension check – to ensure that they are indeed mergeable, and can easily use the code above for the mechanics of the merge. However, I am drawing a blank when it comes to trying to create the ReDim statement to extend the size of the final dimension….

    Is there a way of doing this? Or do you have to hard code the ReDim for each of the possible ranks (1 to 60) = probably best to use a Select…Case Statement.

    Thanks for an always informative and thoughtprovoking ‘blog

    Stephen Wortley

  2. Stephen: I’ve tried that same thing when I wanted a generic array sorting routine that would handle any number of dimensions. After a while, I decided that it was easier to have two routines; one for single dimension arrays and one for double dimension arrays. I have yet to use a three dimension array. It might be worth another look, though.

  3. I’ve tested this in the Immediate Window, here are the results:

    a = Array(Sheet1)
    b = Array(ThisWorkbook)
    c = ArrayUnion(a,b)
    ? err.Description
    Object doesn’t support this property or method



  4. Hi Jamie,

    I’m afaraid the arguments must be an array. Please give this a try.

    Sub TestingFunction()
    Dim ele
    a = Array(1, 2, 3)
    b = Array(4, 5, 6)
    c = ArrayUnion(a, b)
    For Each elm In c
    Debug.Print elm
    End Sub

    Hi Dick, I placed a link to here on my tip site, see

  5. Two comments:
    (1) the checking of Empty array does not work,
    TypeName() of an NOT allocated dynamic array of integer will return “Integer()”

    2) the for-loop to copy the second array: the LBound of second array is not always one, suggest to change to
    va1(Upper + (i-LBound(va2)+1) = va2(i)


  6. I’d prefer

    arrayunion=split(join(va1,“|”) & “|” & join(va2,“|”),“|”)
  7. Hans,

    I think your code always converts to strings?

    And since string handling, particularly declaring strings, is usually very inefficient in VBA I would guess using Split and Join is slow. Have you done any tests?


  8. Although ‘join’ converts to a string, split results in an array (Variant). So there’s no use in declaring strings.
    I combined 2 arrays consisting of 100 elements to an array of 200 elements.
    My one-liner took 1,3 msec, the ‘loop’ did it in ,3 msec.( test performed on a very slow computer PII, 350 MHz)
    Although the difference in speed amounts to a factor 4, no user will notice the difference.

  9. Hans,

    Having tested it I think the result of your one-liner is always a variant containing strings, which could be unfortunate if you were feeding it numbers.

    I agree that most of the time the speed difference does not matter, but for larger data volumes the speed difference could be significant.
    If you built a UDF that combined 2 10000 element arrays and had 1000 instances of the udf the time difference on my fast machine would be about 20 seconds.


  10. Hi, I went throught Juan Pablo’s code. I had not worked with arrays very often yet, and I like the simplicity of this code. I was wondering, if you’d want to not add the 2nd vector below the first vector, but as a new column, how would you code that?

    Really curious to know!

    Thanks, Maurik

  11. This code is really awesome. For people, who want to join more arrays, you repeat the function for every new array. If you want to display your array in a column, just use =Transpose(ArrayUnion(..)

    thanks for the code.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.