Excel VBA Place values in multidimensional array

Question

I have a worksheet with this type of structure (there are more columns in the real sheet, but not many):

ColumnAValue1   ColumnBValue1   23
ColumnAValue1   ColumnBValue1   45
ColumnAValue1   ColumnBValue1   2.4
ColumnAValue1   ColumnBValue2   1
ColumnAValue1   ColumnBValue2   3
ColumnAValue2   ColumnBValue1   5
ColumnAValue2   ColumnBValue1   6
ColumnAValue2   ColumnBValue1   7
ColumnAValue2   ColumnBValue2   355
ColumnAValue2   ColumnBValue2   221

And I want to get averages, item numbers and deviation for each combination (for example, ColumnAValue1 ColumnBValue1 would be the average of 23, 45 and 2.4). So I thought that getting all data in an Array, or Collection or Dictionary (I don't know if anything like "Multidimensional Dictionary" exists) would be useful. I wanted to end with a multidimentional array (or Collection) with a structure similar to this:

AllData(
        ColumnAValue1(
                    ColumnBValue1(23,45,2.4)
                    ColumnBValue2(1,3)
                    )
        ColumnAValue2(
                    ColumnBValue1(5,6,7)
                    ColumnBValue2(355,221)
                    )
        )

I know how to obtain unique values from columns.

My two questions: 1) How can I create an Array (or Collection) with the proper Keys (ColumnAValue1 and ColumnAValue2 for the first dimention, and ColumnBValue1 and ColumnBValue2 for the second), and 2) then loop through all my data and "place" values in the corresponding subarray.

score 2 · Accepted Answer · edited May 23 '17 at 12:31

2

Sub Test()
  Dim c As Collection
  Set c = New Collection

  Dim ws As Worksheet
  Set ws = ThisWorkbook.Worksheets("Sheet1")

  Dim i As Long
  For i = 1 To 10 'Assume 10 rows
    AddToLayeredCollection c, ws.Cells(i, 3).value, ws.Cells(i, 1).value, ws.Cells(i, 2).value 'Assume two columns for keys, A and B
  Next

  'Add 'c' to the watch window and examine it
End Sub

Public Sub AddToLayeredCollection(ByVal root_collection As Collection, ByVal value As Variant, ParamArray keys() As Variant)
  Dim i As Long
  Dim target_collection As Collection

  Set target_collection = root_collection
  For i = LBound(keys) To UBound(keys)
    Set target_collection = ResolveToCollection(target_collection, keys(i))
  Next

  target_collection.Add value
End Sub

Private Function ResolveToCollection(ByVal parent_collection As Collection, ByVal key As Variant) As Collection
  On Error Resume Next
  Set ResolveToCollection = parent_collection(key)(1)
  On Error GoTo 0

  If ResolveToCollection Is Nothing Then
    Set ResolveToCollection = New Collection
    parent_collection.Add Array(key, ResolveToCollection), key
  End If
End Function

The only reason I'm using the Array() thing is to be able to retrieve keys from the collection. You can use Dictionary instead and remove the Array().

edited May 23 '17 at 12:31

Community

1
1

answered Jun 12 '16 at 18:58

GSerg

76,472
17
159
346

Wow! It does what i was asking! Still trying to get the logic of the code, but examining c in the watch windows (as you said) looks great. I'm amazed, and extremely grateful. I've been stuck four days with this before giving up and asking here... – CMArg Jun 12 '16 at 19:33
@GSerg Between Dictionary and Array which one is more preferred approach in this case. Seeking your opinion as an enthusiast to learn from your valuable experience and expertise. – skkakkar Jun 12 '16 at 19:48
@skkakkar This is a question of personal preference. I tend to use `Collection` because it's an internal class and I know it's going to be available. Using a Dictionary would be cleaner in code, but that's a dependency on an external library - then again, the library is probably going to be present on any system where you run Excel - then again, read the [comments below this answer](http://stackoverflow.com/a/5702529/11683). – GSerg Jun 12 '16 at 19:55
@GSerg Thanks for enlightening me on this issue. – skkakkar Jun 12 '16 at 20:10
@GSerg One more, if you don't mind: how to count elements in subcollection? `CStr(c.Count)` is two in the example, and I can access elements like `c(1)(1)(1)(1)(1)` (it would be 23). But how could I know that the first group has 3 elements (23,45,2.4), and the second 2 (1,3)? – CMArg Jun 12 '16 at 21:01
ok, got it: `c(1)(1)(1)(1).Count` and `c(1)(1)(2)(1).Count`. Still getting used to this "multidimensional collection". And still amazed with your skills... – CMArg Jun 12 '16 at 21:23

Excel VBA Place values in multidimensional array

1 Answers1

Linked