merge multi-dimensional arrays

[kylefoley76]

I have a very tough time with the redim function and the multi-dimensional arrays. I'm trying to merge two multi-dimensional arrays into one. I know that the second dimension of each array will always be 2, but the first dimension will change. When I use the redim function I do not get an error message but it erases everything that was in the first array in the first place. When I use redim preserve I get a subscript out of range. Help.

Function merge_arrays2(first_array As Variant, sec_array As Variant) As Variant


Dim i As Integer, j As Integer, m As Integer


m = UBound(sec_array)
j = UBound(first_array)


ReDim first_array(m + j, 2)


For i = 1 To UBound(sec_array)
j = j + 1
first_array(j, 1) = sec_array(i, 1)
first_array(j, 2) = sec_array(i, 2)
Next


merge_arrays2 = first_array


End Function

 

[VBA Geek]

when using Redim Preserve, you may only redimension the last dimension of the array, in your case you are trying to redimension the first dimension hence you get an error, you can only use redim preserve to redimension the second dimension

 

[mikerickson]

ReDim Preserve only works when the last dimension is being changed.

Dim myArray()
Redim myArray(1 to 3, 1 to 5) 
Redim Preserve myArray(1 to 3, 1 to 10) ' works
Redim Preserve myArray(1 to 10, 1 to 10) ' doesn't

 

[VBA Geek]

you could do something like:

passing a 3rd array which will be the merged one

Function MergeArrays(firstArray As Variant, secondArray As Variant, _
                        resultArray As Variant) As Boolean
Dim NumElements1 As Long, NumElements2 As Long, Ndx As Long, Cntr As Long


MergeArrays = False
If Not IsArray(firstArray) Then Exit Function
If Not IsArray(secondArray) Then Exit Function


' to be more robust here you could test that
' the first two arrays are allocated and are 2 dimensional
' i'll leave this out though, resultArray needs to be dynamic as well


NumElements1 = UBound(firstArray, 1) - LBound(firstArray, 1) + 1
NumElements2 = UBound(secondArray, 1) - LBound(secondArray, 1) + 1


ReDim resultArray(1 To NumElements1 + NumElements2, 1 To 2)
Cntr = 1


For Ndx = LBound(firstArray, 1) To UBound(firstArray, 1)
    resultArray(Cntr, 1) = firstArray(Ndx, 1)
    resultArray(Cntr, 2) = firstArray(Ndx, 2)
    Cntr = Cntr + 1
Next Ndx


For Ndx = LBound(secondArray, 1) To UBound(secondArray, 1)
    resultArray(Cntr, 1) = secondArray(Ndx, 1)
    resultArray(Cntr, 2) = secondArray(Ndx, 2)
    Cntr = Cntr + 1
Next Ndx


MergeArrays = True
End Function

 

[kylefoley76]

Thanks. Merge into a third array. That did it.

Dim i As Integer, j As Integer, k As Integer, third_array(), m As Integer


m = UBound(sec_array)
j = UBound(first_array)


ReDim third_array(m + j, 2)


For i = 1 To UBound(first_array)
k = k + 1
third_array(k, 1) = first_array(i, 1)
third_array(k, 2) = first_array(i, 2)
Next


For i = 1 To UBound(sec_array)
k = k + 1
third_array(k, 1) = sec_array(i, 1)
third_array(k, 2) = sec_array(i, 2)
Next


merge_arrays2 = third_array

 

[tusharm]

In addition to using a 3rd array as has already been suggested, you can also use Excel's TRANSPOSE function to make the 1st dimension the 2nd dimension, then use Redim Preserve, and then transpose again. The transpose function does have a limit of how large the array can be and you would have to test if your array fits in that limit.

You can also consider an array of arrays approach as in Preserve existing content while resizing any dimension of a matrix

I have a very tough time with the redim function and the multi-dimensional arrays. I'm trying to merge two multi-dimensional arrays into one. I know that the second dimension of each array will always be 2, but the first dimension will change. When I use the redim function I do not get an error message but it erases everything that was in the first array in the first place. When I use redim preserve I get a subscript out of range. Help.

Function merge_arrays2(first_array As Variant, sec_array As Variant) As Variant


Dim i As Integer, j As Integer, m As Integer


m = UBound(sec_array)
j = UBound(first_array)


ReDim first_array(m + j, 2)


For i = 1 To UBound(sec_array)
j = j + 1
first_array(j, 1) = sec_array(i, 1)
first_array(j, 2) = sec_array(i, 2)
Next


merge_arrays2 = first_array


End Function

 

[pgc01]

Just for fun, , not very practical. Redim both dimensions in 1 go:

Sub ReDim2D()
Dim vArray

ReDim vArray(1 To 10, 1 To 2)

vArray(2, 1) = "B"
vArray(1, 2) = 5

' like ReDim Preserve (1 to 15, 1 o 3)
vArray = Application.IfError(Application.Index(vArray, [row(1:15)], Array(1, 2, 3)), Array("", 0, False))

vArray(12, 1) = "L"
vArray(13, 2) = 12.3456
vArray(14, 3) = True

End Sub

 

[kylefoley76]

thanks tusharm and pgc the helpful suggestions. I really want to thank all of your for helping me out.

 

[ztodd]

For what it's worth, many times you can greatly simply things by not using arrays. In my experience, they are often used when they are really not needed.