VBA 多维数组

VBA Multidimensional Arrays

提问by babsdoc

Is there a way to do the following in VBA?

有没有办法在 VBA 中执行以下操作？

Initialize a multidimensional array and fill it with a series of numbers,

初始化一个多维数组并用一系列数字填充它，

 1  2  3  4  5 
 6  7  8  9 10
11 12 13 14 15
16 17 18 19 20

Then remove some specific columns, for eg. columns 1, 2, 4. So that the end result is just the 3rd and 5th column.

然后删除一些特定的列，例如。第 1、2、4 列。因此最终结果只是第 3 和第 5 列。

Lastly how does one convert the final output to a normal one dimensional array.

最后，如何将最终输出转换为普通的一维数组。

回答by bonCodigo

Assume that you have these columns in an Excel sheeet. If you only have these data in these columns you may simple delete the columns you need :D Then you will end up with 2 columsn you desire. Without knowing what you really need at the end this is the best blind guess..

假设您在 Excel 工作表中有这些列。如果您在这些列中只有这些数据，您可以简单地删除您需要的列 :D 然后您将得到 2 个您想要的列。不知道你最终真正需要什么，这是最好的盲目猜测。

e.g. your columns starts at B to F:

例如，您的列从 B 到 F：

Columns("B:B").Delete Shift:=xlToLeft
Columns("C:C").Delete Shift:=xlToLeft
Columns("D:D").Delete Shift:=xlToLeft

You can use the same logic to process the array.

您可以使用相同的逻辑来处理数组。

• Transpose the array into the sheet.
• Delete the columns.
• Then transpose the left over two columns to array.

• 将数组转置到工作表中。
• 删除列。
• 然后将左边的两列转置为数组。

But what will you do with the last two columns without putting it into the sheet? Very curious. So please confirm what you need, so anyone here can help you.

但是，如果不将最后两列放入工作表，您将如何处理它？非常好奇。所以请确认您需要什么，以便这里的任何人都可以帮助您。

EDIT as per OP's comment:

根据 OP 的评论编辑：

You may take a look at this posts and articles which has manupulation of arrays in different ways:

你可以看看这篇文章和文章，它们以不同的方式操作数组：

• Excel clear cells based on contents of a list in another sheet
• VBA Arrays

• Excel 根据另一个工作表中的列表内容清除单元格
• VBA 数组

Then in order to populate a 2D array for an example in VBA, check this out:

然后为了在 VBA 中为示例填充二维数组，请查看：

Dim i As Integer, j As Integer
Dim array2D As Variant, newArray2D as Variant
'-- 0 indexed based array with 2 rows 3 columns
ReDim array2D(0 To 1, 0 To 2)

For i = LBound(array2D, 1) To UBound(array2D, 1)
    For j = LBound(array2D, 1) To UBound(array2D, 1)
        array2D(i, j) = i + j
    Next j
Next i

'--to delete the fastest is to use the above logic (worksheet)
'-- here you don't need to declare/redimentioned the array
'-- as transpose will do it with a 1 indexed based array

newArray2D = WorksheetFunction.Transpose(Sheets(2).Range("B2:D").Value)

回答by SeanC

to delete row or columns from an array, you will have to transfer the data you wish to keep to a temporary array, or overwrite the values in the array.

要从数组中删除行或列，您必须将要保留的数据传输到临时数组，或覆盖数组中的值。

To convert dimensions, a loop would be required.

要转换尺寸，需要一个循环。

Lots of functions and examples of arrays can be found here

可以在此处找到许多函数和数组示例

回答by Yaacov

Below is some sample code that fully accomplishes the task in the question, via loops/direct copy, i.e. nothing educational, but also tries to demonstrate how VBA Excel's "slicing" function with Application.WorksheetFunction.Index does not actually help with this task, even as it may be useful in other ways:

下面是一些示例代码，通过循环/直接复制完全完成了问题中的任务，即没有任何教育意义，但也试图演示 VBA Excel 的 Application.WorksheetFunction.Index 的“切片”功能如何实际上对这个任务没有帮助，即使它可能在其他方面有用：

Public Sub Answer()

    Dim i As Integer, j As Integer

    ' arrA contains the initial 4x5 multidimensional array
    Dim arrA(1 To 4, 1 To 5) As Integer
    For i = 1 To 4
        For j = 1 To 5
            arrA(i, j) = (i - 1) * 5 + j
        Next j
    Next i

    ' arrBv1 and v2 contain the 2x5 subset, just columns 3 and 5
    ' arrBv1 is obtained by direct copy
    Dim arrBv1(1 To 4, 1 To 2) As Variant
    For i = 1 To 4
        arrBv1(i, 1) = arrA(i, 3)
        arrBv1(i, 2) = arrA(i, 5)
    Next i

    ' arrBv2 is obtained by using Excel's "slicing" capability
    Dim arrBv2(1 To 4, 1 To 2) As Integer

    Dim slices(1 To 2) As Variant
    slices(1) = Application.WorksheetFunction.Index(arrA, 0, 3)
    slices(2) = Application.WorksheetFunction.Index(arrA, 0, 5)

    ' but because the slices are actually each 4x1 multidimensional
    ' array, a second loop is required to obtain a data structure
    ' actually equivalent to arrBv1, making this "shortcut" no
    ' shorter for producing a 4x2 array
    For i = 1 To 4
        arrBv2(i, 1) = slices(1)(i, 1)
        arrBv2(i, 2) = slices(2)(i, 1)
    Next i

    ' although arrBv1 and v2 are equivalent, as MsgBox does not appear
    For i = 1 To 4
        For j = 1 To 2
            If arrBv1(i, j) <> arrBv2(i, j) Then
                MsgBox ("equivalence failure with 4x2 arrays")
            End If
        Next j
    Next i

    ' arrCv1 is the 1x8 array obtained by direct copy from the 4x2 array
    Dim arrCv1(1 To 8) As Integer

    For i = 1 To 4
        arrCv1(i) = arrBv1(i, 1)
        arrCv1(i + 4) = arrBv1(i, 2)
    Next i

    ' arrCv2 is the one-dimensional array obtained from the slices, which
    ' does not lead to an additional step, but is not shorter
    ' than just using arrBv1 as immediately above or

    Dim arrCv2(1 To 8) As Integer

    For i = 1 To 4
        arrCv2(i) = slices(1)(i, 1)
        arrCv2(i + 4) = slices(2)(i, 1)
    Next i

    ' arrCv3 is the 1x8 array obtained from the original 4x5 array,
    ' shorter still

    Dim arrCv3(1 To 8) As Integer

    For i = 1 To 4
        arrCv3(i) = arrA(i, 3)
        arrCv3(i + 4) = arrA(i, 5)
    Next i

    ' and arrCv1, v2 and v3 are again all equivalent
    For i = 1 To 8
        If arrCv1(i) <> arrCv2(i) Or arrCv1(i) <> arrCv3(i) Then
            MsgBox ("equivalence failure with one-dimensional array")
        End If
    Next i

    ' so, in sum, nothing.

End Sub