Multidimensional array or collection

Question

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))

Answer 1

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.

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

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.

Answer 2

This may help, an array in an array is not strictly possible, what you can do what 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