Read Dictionary in Loop, associate column header names

[joemeshuggah]

Sample code:

' clsCustomer Class Module Code
Public CustomerID As String
Public Amount As Long
Public Items As Long


Sub test()

Dim dict As New Scripting.Dictionary

Set oCust = New clsCustomer

' Set the values
oCust.CustomerID = "bob"
oCust.Amount = 1000
oCust.Items = 500

' Add the new clsCustomer object to the dictionary
dict.Add oCust.CustomerID, oCust


Dim key As Variant
For Each key In dict.Keys
Debug.Print dict(key).CustomerID
Debug.Print dict(key).Amount
Debug.Print dict(key).Items
Next key


End Sub

Two questions regarding above:
1. Is it possible to read through a dictionary without having to explicitly reference each item? Meaning, in the example above, instead of having to explicitly write out debug.print for CustomerID, Amount, and Items, is it possible to reference them via a loop through the items in clsCustomer? Something like:

Dim key As Variant
For Each key In dict.Keys
For Each x in clsCustomer
debug.print dict(key).x
Next x
Next key

2. Is it possible to associate a column header name to each item? Meaning, if I am writing the dictionary to a worksheet, when CustomerID is being written, I'd like the first row to show column header "Customer ID". I realize you can hardcode the column header and write the dictionary starting in row 2, but if the order of the dictionary items changes, the column headers would need to be manually re-ordered as well, which I want to avoid.

Thanks!

 

[StephenCrump]

1. Meaning, in the example above, instead of having to explicitly write out debug.print for CustomerID, Amount, and Items, is it possible to reference them via a loop through the items in clsCustomer?

No, not directly. If you want to keep the separate property names .CustomerID, .Amount and .Items (i.e. as opposed to having some generic collection name), you could add a MyItem property to clsCustomer:

Public Property Get MyItem(i As Long) As Variant

   Select Case i
      Case 1: MyItem = Me.CustomerID
      Case 2: MyItem = Me.Amount
      Case 3: MyItem = Me.Items
   End Select

End Property

2. Is it possible to associate a column header name to each item? Meaning, if I am writing the dictionary to a worksheet, when CustomerID is being written, I'd like the first row to show column header "Customer ID". I realize you can hardcode the column header and write the dictionary starting in row 2, but if the order of the dictionary items changes, the column headers would need to be manually re-ordered as well, which I want to avoid.

I am guessing from the question that you think you are going to be able to write the dictionary directly to the worksheet? That won't happen - your code will dictate what property goes in what column, and hence you will have control over where to put the appropriate headers.

 

[joemeshuggah]

Thanks! I think I was able to work out your property suggestion to work for my purposes. However, I'm not clear on my second question. Are you saying there is no way to reference a desired column header for the MyItem(i) that is being evaluated? E.g if MyItem(i) is Me.Amount, I was hoping there would be a way within the program to say the desired column header is "Dollar Amount", preferably within the property sub so that it is easy to see and maintain that "Dollar Amount" is associated to Me.Amount.