Nesting Collections

[lisamariechemistry]

I keep coming across situations where it would be really useful to
create collections within collections, but I just can't quite work it
out. Here's the closest I've gotten: I have a class module called
Sample, with the following public variables (among others):
Name as string
SampleType as string
Elements as collection

in my main sub:

dim Sample as Sample
dim Samples as collection

set Samples = new collection

for each File in application.filesearch.foundfiles
set Sample = new sample
set Sample.Elements = new collection
Sample.Name = (somevalue)
for i = 1 to 10
Sample.Elements.add (somevalue, somekey & i)
next i
debug.print sample.elements(somekey) 'OK so far
samples.add Sample '(with Sample.Name as key)
Next File

For each Sample in Samples
debug.print Sample.Name 'Works
debug.print sample.elements.count 'Works
debug.print Sample.Elements(somekey1)
'Gives Error '424' Object Required
debug.print Sample.elements(1) 'gives error also
Next Sample

Anyone out there know the proper way to nest collections or an elegant
alternate? Thanks! -Lisa

 

[Gary Keramidas]

this may be irrelevant to you, but thought i'd pass it along.

instead of :
For each Sample in Samples

maybe:
For j = 1 To samples.Count
debug.print samples(j)
next

and if you happened to add to the collection like this:
combStr = string1 & "," & string2
samples.add combStr, cstr(combStr)

you could use the split command

for j = 1 to sample.count
str1 = split(samples(0),",")
str2 = split(samples(1), ",")
next

i use something like this when i need to create set of unique records based on 2
criteria. then i use the autofilter to filter on the 2 criteria to perform my
calcs on.

 

[Dave D-C]

Does this work for you?
It works for me (XL97/WIN98)

Class Module clsSample:
Public Name As String
Public SampleType As String
Public Elements As Collection

Sub Main()
'Dim Sample As Sample ' you should be shot!
Dim Sample As clsSample, Samples As Collection
Dim zFile, i1%, s1$

Set Samples = New Collection

For Each zFile In Array("One", "Two")
Debug.Print "--Building " & zFile
Set Sample = New clsSample
Sample.Name = (zFile & "Name")
Set Sample.Elements = New Collection
Sample.Elements.Add "Alpha", "AlphaKey"
Sample.Elements.Add "Beta", "BetaKey"
Debug.Print Sample.Elements("AlphaKey")
Debug.Print Sample.Elements("BetaKey")
Samples.Add Sample ' without a key
s1 = Samples(1).Name ' by index, ok
's1 = Samples(s1).Name ' by key, ng
Next zFile

For Each Sample In Samples
Debug.Print "--" & Sample.Name
Debug.Print Sample.Elements.Count
Debug.Print Sample.Elements("AlphaKey")
Debug.Print Sample.Elements("BetaKey")
Debug.Print Sample.Elements(1)
Debug.Print Sample.Elements(2)
Next Sample
End Sub ' D-C Dave