topic Re: in VBA Forum

Nested collections?

Anonymous — Wed, 19 Dec 2001 15:36:39 GMT

I have a collection which contains multiple collections inside and I was wondering how to access each item of the nested collection. For example list1 would be ((1 2 3),(4 5 6)) and if I wanted to access item 4 of the second list what would be the syntax? I tried list1(2)(1) but it didn't work.

Re: Nested collections?

Anonymous — Thu, 20 Dec 2001 05:30:54 GMT

style="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">

"GARY NTX" <gadamson@psp.com>
wrote in message
href="news:f0a4244.-1@WebX.maYIadrTaRb">news:f0a4244.-1@WebX.maYIadrTaRb...

I have a collection which contains multiple collections inside and I was
wondering how to access each item of the nested collection. For example list1
would be ((1 2 3),(4 5 6)) and if I wanted to access item 4 of the second list
what would be the syntax? I tried list1(2)(1) but it didn't work.

list1(2)(0)

Re:

Anonymous — Thu, 20 Dec 2001 07:42:44 GMT

That doesn't work either...I get errors when I try that.I guess there is no way to have a collection inside a collection because the internal items can't be accessed.

Re:

Anonymous — Thu, 20 Dec 2001 08:14:41 GMT

You can access nested collections.

Public Sub test()
Dim colOne As Collection
Dim colTwo As Collection

Set colOne = New Collection
Set colTwo = New Collection

colTwo.Add Item:="ItemOne", Key:="kOne"
colTwo.Add Item:="ItemTwo", Key:="kTwo"
colTwo.Add Item:="ItemThree", Key:="kThree"

colOne.Add Item:=colTwo, Key:="kOne"
colOne.Add Item:=colTwo, Key:="kTwo"

'print the second item of the first nested collection
'via a position index
Debug.Print colOne(1).Item(2)

'print the third item of the second nested collection
'via a specific key value
Debug.Print colOne("kTwo").Item("kThree")

End Sub

--
Happy Holidays...
Bobby C. Jones
http://www.acadx.com

Re:

Anonymous — Thu, 20 Dec 2001 09:06:13 GMT

OK your program works, but I can't seem to get mine to work although it
seems to be syntaxed right.
Please see comment (1).

Public Sub test()
Dim rno As Integer, cno As Integer, tno As Integer
Dim RowList As New Collection, ExcelList As New Collection
tno = 1
For rno = 1 To 3
For cno = 1 To 3
RowList.Add Str(tno)
tno = 1 + tno
Next cno
ExcelList.Add RowList
For cno = 1 To RowList.Count
RowList.Remove 1
Next cno

'(1) ExcelList should be = (("1","2","3"),("4","5","6"),("7","8","9"))
'so if I wanted to get "6", which item 3 in collection 2 what is the
syntax?
'I tried ExcelList(2).Item(3), ExcelList(2)(3) and ExcelList(2).(3) to
no avail

Next rno
End Sub

Re:

Anonymous — Thu, 20 Dec 2001 09:57:21 GMT

If you set a break point and step through your procedure in the debugger
looking at the Locals window, you will see that when you remove the items
for RowList it also removes the same nested items from ExcelList - when it
is done ExcelList is empty.

In place of the Remove loop, try

Set RowList = Nothing

I haven't used nested collections so I don't know if this is the proper
thing to do but, it works.

Glen

"GARYNTX" wrote in message
news:0924A11C3069752D7CC9A56216D8DE30@in.WebX.maYIadrTaRb...
> OK your program works, but I can't seem to get mine to work although it
> seems to be syntaxed right.
> Please see comment (1).
>
> Public Sub test()
> Dim rno As Integer, cno As Integer, tno As Integer
> Dim RowList As New Collection, ExcelList As New Collection
> tno = 1
> For rno = 1 To 3
> For cno = 1 To 3
> RowList.Add Str(tno)
> tno = 1 + tno
> Next cno
> ExcelList.Add RowList
> For cno = 1 To RowList.Count
> RowList.Remove 1
> Next cno
>
> '(1) ExcelList should be = (("1","2","3"),("4","5","6"),("7","8","9"))
> 'so if I wanted to get "6", which item 3 in collection 2 what is the
> syntax?
> 'I tried ExcelList(2).Item(3), ExcelList(2)(3) and ExcelList(2).(3) to
> no avail
>
> Next rno
> End Sub
>
>

Re:

Anonymous — Thu, 20 Dec 2001 10:09:23 GMT

The correct syntax is ExcelList(2).Item(3) However, the problem that you
are experiencing is that everything you add to the RowList collection is
immediately removed. Your logic for storing your data is dead on center.
You just need to use the right tools. You can either create a new
collection for each RowList or my suggestion is to create a class object.
This is a bare bones class with no error checking. After you've pasted the
code into the class module and renamed it, run the test sub. It'll print 6
in the Immediate window, the 3rd Item in the 2nd 'List'.

Public Sub test()
Dim rno As Integer, cno As Integer, tno As Integer
Dim oRowList As RowList, ExcelList As New Collection
tno = 1
For rno = 1 To 3
Set oRowList = New RowList
For cno = 1 To 3
'I changed it from Str() to the newer CStr() func.
oRowList.Add CStr(tno)
tno = 1 + tno
Next cno
ExcelList.Add oRowList

Set oRowList = Nothing
Next rno

Debug.Print ExcelList(2).Item(3)
End Sub

'Paste this code into a class module
'and name it RowList
Option Explicit
Private colRow As Collection

Private Sub Class_Initialize()
Set colRow = New Collection
End Sub

Private Sub Class_Terminate()
Set colRow = Nothing
End Sub

Public Sub Add(ByVal Item As Variant)
colRow.Add Item
End Sub

Public Function Item(ByVal key As Variant) As Variant
Item = colRow.Item(key)
End Function

--
Happy Holidays...
Bobby C. Jones
http://www.acadx.com

Re:

Anonymous — Thu, 20 Dec 2001 11:12:05 GMT

Ya, or you could just do what Glen said 🙂 Although you have to admit that
it's not as cool as creating your own custom objects.
--
Happy Holidays...
Bobby C. Jones
http://www.acadx.com

Re:

Anonymous — Thu, 20 Dec 2001 12:40:23 GMT

WOW! I am new to this VB stuff and I didn't know about the Locals window or the Watch window, so I didn't know what was happening to my program. That makes debugging incredibly easier.

Re:

Anonymous — Thu, 20 Dec 2001 13:14:46 GMT

Eek-gads man!! Reminds me of the good old days of debugging lisp without
the vlide 🙂
--
Happy Holidays...
Bobby C. Jones
http://www.acadx.com

Re: Nested collections?

Anonymous — Thu, 20 Dec 2001 16:03:36 GMT

Here's another way to look at it from the Print Area concept (I didn't quite
understand what you were looking for before)...

....Set ExcelSheet = Excel.ActiveWorkbook.Sheets("Sheet1")

Dim theList As New Collection
Dim theEList As New Collection
Dim theArea, theRange
Dim i, j, c, r

theArea = ExcelSheet.PageSetup.PrintArea
r = Range(theArea).Columns.Count
Set theRange = ExcelSheet.Range(theArea)

i = 1
j = 1
For Each c In theRange.Cells
theList.Add c.Value
If j = r Then
theEList.Add theList, Str(i)
Set theList = Nothing
j = 0
i = i + 1
End If
j = j + 1
Next

If (theEList.Count > 0) Then
MsgBox theEList.Count & "-" & theEList(2).Item(3)
End If

"GARYNTX" wrote in message
news:f0a4244.-1@WebX.maYIadrTaRb...
> I have a collection which contains multiple collections inside and I was
wondering how to access each item of the nested collection. For example
list1 would be ((1 2 3),(4 5 6)) and if I wanted to access item 4 of the
second list what would be the syntax? I tried list1(2)(1) but it didn't
work.
>