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.

Community · Accepted Answer · 2017-05-23 12:31:48Z

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, 2017 at 12:31

CommunityBot

11 silver badge

answered Jun 12, 2016 at 18:58

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

CMArg Over a year ago

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

skkakkar Over a year ago

@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.

GSerg Over a year ago

@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.

CMArg Over a year ago

@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 Over a year ago

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

Collectives™ on Stack Overflow

Excel VBA Place values in multidimensional array

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related