vba 多维数组或集合

Multidimensional array or collection

提问by awariat

I have code:

我有代码：

 Dim products As Variant
 LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
 products = Array("MS-CHOPMAT-6", "MS-BOARDS-3", "MS-CHOP-LR")

For x = LastRow To 1 Step -1

order_quantity = Range("$E$" & x).Value
item_price = Range("$F$" & x).Value

' if value not found inside the array using the "MATCH" function
If IsError(Application.Match(Range("$D$" & x).Value, products, 0)) Then
 Range("$H$" & x).Value = "ERROR - " & order_quantity
Else ' successful "MATCH" inside the array
 Range("$H$" & x).Value = order_quantity * 3
End If

Next

but instead of having simple array I need array multidimensional or "collections". How to change this code to work with collections or multidimensional array

但是我不需要简单的数组，而是需要多维数组或“集合”。如何更改此代码以使用集合或多维数组

like:

喜欢：

products = Array(Array("MS-CHOPMAT-6", 11,"w"), Array("MS-BOARDS-3", 12, 4),   Array("MS-CHOP-LR", 13, 5))

回答by Gary Evans

This may help, an array in an array is not strictly possible, what you can dowhat I have done in the past is mimic an array in an array action by creating your own delimiter as shown below.

这可能会有所帮助，数组中的数组并不是严格可行的，您可以做的我过去所做的是通过创建自己的分隔符来模拟数组操作中的数组，如下所示。

Public Sub Sample()
Dim AryTable()  As String
Dim AryRow()    As String
Dim VntCell     As Variant
Dim LngID       As Long

'AryTable is root array
ReDim AryTable(2)

'Below is the population of the array, using #~# as a delimiter, whatever
'you feel will not come up will be best
'In the past to be safe I used #UnliklyDivider# as my delimiter, to make 
'sure it was never confused or come up in
'real data
AryTable(0) = "1#~#Field1#~#Field2#~#Field3"
AryTable(1) = "1#~#Field1#~#Field2#~#Field3#~#Field4#~#Field5"
AryTable(2) = "1#~#Field1#~#Field2#~#Field3#~##~#Field5"

'This goes through each row in the array, using each one as an array in its 
'own right
For LngID = 0 To UBound(AryTable, 1)
    AryRow = Split(AryTable(LngID), "#~#")
    For Each VntCell In AryRow
        Debug.Print LngID & ": " & VntCell
    Next
Next

End Sub

回答by Gary Evans

The two dimensional answer to this would be as below. There are many way to do this, this is simply an example. Two dimensional arrays are great but need some thought around implementation, ideally you would want to use some form of recursion to populate it, the example below simple sets them in a static manner.

对此的二维答案如下。有很多方法可以做到这一点，这只是一个例子。二维数组很棒，但需要对实现进行一些思考，理想情况下，您希望使用某种形式的递归来填充它，下面的示例简单地以静态方式设置它们。

Public Sub Sample()
Dim AryTable()  As String
Dim LngRow      As Long
Dim LngCol      As Long

'Below is a two dimensional array, think of it as a
'table with 3 rows and 5 columns (the base is zero
'so it is not 2 rows and 4 columns as it may look)
ReDim AryTable(2, 4)

'We can then populate (or not) each 'cell' of the array

'Row 1
AryTable(0, 0) = "1"
AryTable(0, 1) = "Field1"
AryTable(0, 2) = "Field2"
AryTable(0, 3) = "Field3"

'Row 2
AryTable(1, 0) = "2"
AryTable(1, 1) = "Field1"
AryTable(1, 2) = "Field2"
AryTable(1, 3) = "Field3"
AryTable(1, 4) = "Field4"

'Row 3
AryTable(2, 0) = "3"
AryTable(2, 1) = "Field1"
AryTable(2, 2) = "Field2"
AryTable(2, 4) = "Field4"

'Ubound by the first dimension to go through the rows
For LngRow = 0 To UBound(AryTable, 1)

    'Ubound by the second dimension to go through the columns
    For LngCol = 0 To UBound(AryTable, 2)
        Debug.Print AryTable(LngRow, 0) & ": " & AryTable(LngRow, LngCol)
    Next
Next

End Sub

Point to note, if you don't declare the size of the array at the start you can change it later.

需要注意的是，如果您在开始时没有声明数组的大小，您可以稍后更改它。

This is declared (and can not be changed later): -

这是声明的（以后不能更改）：-

Dim AryTable(1,2) as string

This is not declared (and can be changed later): -

这没有声明（以后可以更改）：-

Dim AryTable() as string

When yo have not declared its size (so can change it) you must size it before use. There are two ways to do it, reset or preserve.

当你没有声明它的大小（所以可以改变它）你必须在使用前确定它的大小。有两种方法可以做到，重置或保留。

This will clear the array and set it to the new size, I.e. If the array was previously 100 in size and had data it in, the below would remove all the data but make it larger.

这将清除数组并将其设置为新的大小，即如果数组以前的大小为 100 并且其中有数据，则以下将删除所有数据但使其更大。

Redim AryTable(200)

If the array was previously 100 in size and had data it in, the below would retain all the data and make it larger

如果数组以前的大小为 100 并且其中有数据，则以下将保留所有数据并使其更大

Redim Preserve AryTable(200)

On a two dimensional array you can only adjust the second dimension. The below is ok: -

在二维数组上，您只能调整第二个维度。下面是好的： -

Redim AryTable(2,4)
Redim Preserve AryTable(2,8)

The below will fail: -

以下将失败： -

Redim AryTable(2,4)
Redim Preserve AryTable(4,8)

With this in mind if you want to use a two dimensional array to store data like a table, use the first dimension to be columns and the second to be the rows, columns counts rarely change but row may be added.

考虑到这一点，如果您想使用二维数组像表格一样存储数据，请使用第一个维度作为列，第二个维度作为行，列数很少变化，但可能会添加行。