How to join/combine arrays

[Chris Macro]

I am trying to figure out how to join array1 with array 2 and then that array with array3

Thought process

1. array1=array1 & array2
2. array1=array1 & array3

Example:
array1(a,b)
array2(c,d,e)
array3(f,g,h,i,j,k)

Process:
1. array1 = array(a,b,c,d,e)
2. array1 =array(a,b,c,d,e,f,g,h,i,j,k)​

 

[Andrew Poulsom]

You should find what you want here:

http://www.cpearson.com/Excel/VBAArrays.htm

 

[Chris Macro]

I have read through that page a couple of times and I am guessing I need to use the ConcatenateArrays function. I am just not sure what that function is and how to use it??

 

[Andrew Poulsom]

The VBA code is towards the bottom of the page.

 

[Chris Macro]

So is this a function within VBA or something that someone created? Do I just need to place the below code within my sub function?

Public Function ConcatenateArrays(ResultArray As Variant, ArrayToAppend As Variant, _
        Optional NoCompatabilityCheck As Boolean = False) As Boolean

 

[chirp]

i found that site to be extremely confusing when i was starting out with vba.

it is GOOD, in the fact that each function is very robust, but it is BAD for understanding what is happening.

here is a very basic function that "adds" array2 to array1:

Public Function joinArraysE(Array1, Array2, _
                            Optional tst As Boolean)
Dim i As Long, addSize As Long, uBnd1 As Long, lbnd2 As Long

tst = False
On Error GoTo exitFunc

If IsEmpty(Array1) Then
    Array1 = Array2
    tst = True
    Exit Function
End If

uBnd1 = UBound(Array1)
lbnd2 = LBound(Array2)
addSize = ubound(array2)-lbnd2+1

ReDim Preserve Array1(LBound(Array1) To uBnd1 + addSize)

For i = 1 To addSize
    Array1(uBnd1 + i) = Array2(lbnd2 + i - 1)  'this loops through all elements in the array so big arrays=slow
Next

tst = True
exitFunc:
if err.number<>0 then msgbox err.description
End Function

 

[Chris Macro]

ok, so using you function I made a small example sub.....I am not sure that I am calling the function correctly???

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> test()<br><br><SPAN style="color:#00007F">Dim</SPAN> myarray1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> myarray2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><br>myarray1 = Range("D2,F2")<br>myarray2 = Range("D3,F3")<br><br>joinarraysE(myarray1,myarray2)<br><br>Range("C5") = myarray1(5)<br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[chirp]

so this only joins 1d arrays

what are you going to use this for?

if you want to join multi-columned ranges then i would refer you back to the cPearson site.

if you want to just join one-columned ranges, or one-dimensioned arrays, then you can use this...all it does is redim the first array and add the second.

this will take values in the first range and join them with those in the second

Sub tst()

Dim myarray1 As Variant
Dim myarray2 As Variant

myarray1 = rangeToArray1(Range("a1:a5"))
myarray2 = rangeToArray1(Range("b1:b5"))
Call joinArraysE(myarray1, myarray2)

Range("c1").Resize(UBound(myarray1) - LBound(myarray1) + 1) = Application.Transpose(myarray1)

End Sub

'boring and slow converts a multi area range to a single dimensioned array
Public Function rangeToArray1(inputRange As Range, _
                            Optional tst As Boolean) As Variant

Dim tmpArr() As Variant
Dim tCell As Range
Dim i As Long

tst = False
On Error GoTo exitFunc

ReDim tmpArr(0 To inputRange.Cells.count - 1)

For Each tCell In inputRange
    tmpArr(i) = tCell.Value2
    i = i + 1
Next

rangeToArray1 = tmpArr
tst = True
exitFunc:
End Function

 

[Chris Macro]

Thank you both. That solved my problem!!!