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
Else
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
Stephen Wortley:
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 August 2004, 10:33 amDick:
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.
2 August 2004, 10:58 amJamie Collins:
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
Jamie.
–
2 August 2004, 11:32 amColo:
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
Next
End Sub
Hi Dick, I placed a link to here on my tip site, see
5 August 2004, 1:21 amhttp://puremis.net/excel/code/075.shtml
Colo:
I’ve added a similar UDF in my tips site.
5 August 2004, 8:50 pmThanks for giving me an idea!
http://puremis.net/excel/code/076.shtml
Andrew Chien:
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)
regards
2 October 2008, 9:53 pmHans Schraven:
I'd prefer
Charles Williams:
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?
regards
5 October 2008, 12:12 pmCharles
Hans Schraven:
Although 'join' converts to a string, split results in an array (Variant). So there's no use in declaring strings.
5 October 2008, 3:18 pmI 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.
Charles Williams:
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.
regards
6 October 2008, 11:51 amCharles