vba 在VBA excel中合并多维数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25961334/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
merge mutli dimensional arrays in VBA excel
提问by user147178
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.
我在使用 redim 函数和多维数组时遇到了非常困难的时间。我正在尝试将两个多维数组合并为一个。我知道每个数组的第二维总是 2，但第一维会改变。当我使用 redim 函数时，我没有收到错误消息，但它首先删除了第一个数组中的所有内容。当我使用 redim preserve 我得到一个超出范围的下标。帮助。
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
回答by user147178
Never mind. I solved the problem.
没关系。我解决了这个问题。
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
回答by Peekay
Look at the following code. There were issues in Redimming and also in Function declaration
看下面的代码。Redimming 和函数声明中存在问题
Sub test()
Dim a() As Variant
Dim b() As Variant
Dim c() As Variant
ReDim a(1, 2)
ReDim b(1, 2)
i = 0
Do While i < 2
j = 0
Do While j < 3
a(i, j) = 1
b(i, j) = 2
j = j + 1
Loop
i = i + 1
Loop
c() = merge_arrays2(a, b)
End Sub
Function merge_arrays2(first_array() As Variant, sec_array() As Variant) As Variant()
Dim i As Integer, j As Integer, m As Integer, n As Integer
m = UBound(sec_array, 2)
n = UBound(first_array, 2)
ReDim Preserve first_array(1, m + n + 1)
For j = n + 1 To m + n + 1
For i = 0 To UBound(sec_array, 1)
first_array(i, j) = sec_array(i, j-n-1)
Next
Next
merge_arrays2 = first_array
End Function
回答by L42
When you use ReDim, you are actually re-dimensioning the array without preserving its elements.
Using Preservepreserves the elements and thus solves the deleting of array elements.
The problem is, you can only re-dimension the last dimension of the array and not the 1st one.
And so you get the Subscript Out of Range Error. See here MSDN
当您使用 时ReDim，您实际上是在不保留其元素的情况下重新调整数组的大小。
usingPreserve保留元素，从而解决数组元素的删除。
问题是，您只能重新调整数组的最后一个维度而不是第一个维度。
所以你得到了Subscript Out of Range Error。看这里 MSDN
Resizing with Preserve. If you use Preserve, you can resize only the last dimension of the array. For every other dimension, you must specify the bound of the existing array.
使用保留调整大小。如果使用 Preserve，则只能调整数组最后一维的大小。对于每个其他维度，您必须指定现有数组的边界。
One way is what you've posted as answer and another way is below (for small arrays only):
一种方法是您发布的答案，另一种方法如下（仅适用于小数组）：
Function merge2Darray(arr1, arr2) As Variant
Dim tarr
tarr = Application.Transpose(arr1)
Dim i As Long
For i = LBound(arr2, 1) To UBound(arr2, 1)
ReDim Preserve tarr(1 To 2, 1 to UBound(tarr, 2) + 1)
tarr(1, UBound(tarr, 2)) = arr2(i, 0)
tarr(2, UBound(tarr, 2)) = arr2(i, 1)
Next
merge2Darray = Application.Transpose(tarr)
End Function
Take note that Application.TransposeMethod have limitations.
You can only use it on small size arrays (a few thousand).
请注意Application.Transpose方法有局限性。
您只能在小型阵列（几千个）上使用它。
