Before I waste my time trying to figure out how to do it, is it possible to
make collections of collections of data? I have a system that has several
different items listed under one larger item, which is, in turn, part of a
group of like items. I suppose a query on a cost of a part would look
something like this:

? BigCollection("SerialNumber").Part("PartNumber").C ost

Is it possible to put my objects in heirarchial stages like that?

Thanks,

David Morton

David,
Think about a typical Excel reference which may look like:
appExcel.Workbooks(1).Worksheets(1).......Value

Looks like your requirement.

If you are asking how to create this, look into classes, collections etc,
but it's certianly a feasible means of achieving your goal. (I know VB6 has
a class builder wizard that will generate the majority of code for you.
There may be an Excel add-in that would do the same.)

NickHK


"David Morton" wrote in message
1...
Before I waste my time trying to figure out how to do it, is it possible
to
make collections of collections of data? I have a system that has several
different items listed under one larger item, which is, in turn, part of a
group of like items. I suppose a query on a cost of a part would look
something like this:

? BigCollection("SerialNumber").Part("PartNumber").C ost

Is it possible to put my objects in heirarchial stages like that?

Thanks,

David Morton

David,

Look at the Collection object.
You can use a collection object to store (an almost unlimited number of)
any data type, control, object or another collection.
You can also nest collections within collections.

Regards,
Jim Cone
San Francisco, CA

"David Morton" wrote in message 1...
Before I waste my time trying to figure out how to do it, is it possible to
make collections of collections of data? I have a system that has several
different items listed under one larger item, which is, in turn, part of a
group of like items. I suppose a query on a cost of a part would look
something like this:

? BigCollection("SerialNumber").Part("PartNumber").C ost

Is it possible to put my objects in heirarchial stages like that?

Thanks,

David Morton

"Jim Cone" wrote in
:

David,

Look at the Collection object.
You can use a collection object to store (an almost unlimited number
of) any data type, control, object or another collection.
You can also nest collections within collections.

Regards,
Jim Cone
San Francisco, CA

"David Morton" wrote in message
1...
Before I waste my time trying to figure out how to do it, is it
possible to make collections of collections of data? I have a system
that has several different items listed under one larger item, which
is, in turn, part of a group of like items. I suppose a query on a
cost of a part would look something like this:

? BigCollection("SerialNumber").Part("PartNumber").C ost

Is it possible to put my objects in heirarchial stages like that?

Thanks,

David Morton



Then here's my next collection.... can collections have code of their
own?

David,
You should look at classes then.
The code below is VB wizard generated, but you can paste it into 2 class
module in Excel VBA and see how it works.

NickHK

'Class "CollectionOfItems"

'local variable to hold collection
Private mCol As Collection

Public Function Add(ItemName As String, AnotherProperty As Boolean, Optional
sKey As String) As SingleItem
'create a new object
Dim objNewMember As SingleItem
Set objNewMember = New SingleItem

'set the properties passed into the method
objNewMember.ItemName = ItemName
objNewMember.AnotherProperty = AnotherProperty
If Len(sKey) = 0 Then
mCol.Add objNewMember
Else
mCol.Add objNewMember, sKey
End If

'return the object created
Set Add = objNewMember
Set objNewMember = Nothing

End Function

Public Property Get Item(vntIndexKey As Variant) As SingleItem
'used when referencing an element in the collection
'vntIndexKey contains either the Index or Key to the collection,
'this is why it is declared as a Variant
'Syntax: Set foo = x.Item(xyz) or Set foo = x.Item(5)
Set Item = mCol(vntIndexKey)
End Property

Public Property Get Count() As Long
'used when retrieving the number of elements in the
'collection. Syntax: Debug.Print x.Count
Count = mCol.Count
End Property

Public Sub Remove(vntIndexKey As Variant)
'used when removing an element from the collection
'vntIndexKey contains either the Index or Key, which is why
'it is declared as a Variant
'Syntax: x.Remove(xyz)

mCol.Remove vntIndexKey
End Sub

Public Property Get NewEnum() As IUnknown
'this property allows you to enumerate
'this collection with the For...Each syntax
Set NewEnum = mCol.[_NewEnum]
End Property

Private Sub Class_Initialize()
'creates the collection when this class is created
Set mCol = New Collection
End Sub

Private Sub Class_Terminate()
'destroys collection when this class is terminated
Set mCol = Nothing
End Sub


'Class "SingleItem"

'local variable(s) to hold property value(s)
Private mvarItemName As String 'local copy

Private mvarAnotherProperty As Boolean 'local copy

Public Property Let AnotherProperty(ByVal vData As Boolean)
'used when assigning a value to the property, on the left side of an
assignment.
'Syntax: X.AnotherProperty = 5
mvarAnotherProperty = vData
End Property

Public Property Get AnotherProperty() As Boolean
'used when retrieving value of a property, on the right side of an
assignment.
'Syntax: Debug.Print X.AnotherProperty
AnotherProperty = mvarAnotherProperty
End Property

Public Property Let ItemName(ByVal vData As String)
'used when assigning a value to the property, on the left side of an
assignment.
'Syntax: X.ItemName = 5
mvarItemName = vData
End Property

Public Property Get ItemName() As String
'used when retrieving value of a property, on the right side of an
assignment.
'Syntax: Debug.Print X.ItemName
ItemName = mvarItemName
End Property


--------------- cut -------------
David Morton



Then here's my next collection.... can collections have code of their
own?

David Morton wrote ...

Before I waste my time trying to figure out how to do it, is it possible to
make collections of collections of data? I have a system that has several
different items listed under one larger item, which is, in turn, part of a
group of like items. I suppose a query on a cost of a part would look
something like this:

? BigCollection("SerialNumber").Part("PartNumber").C ost

Is it possible to put my objects in heirarchial stages like that?

I think the term for this code construct is 'tunnelling'. I'm a great
fan of this kind of object model design. When implementing, I find it
easiest to have a parent class. For example:

m_Schema.Tables("MyTable").Columns("MyCol1").Name

This requires five classes:

CSchema
CTables
CTable
CColumns
CColumn

CSchema is the parent class. It has a property Tables, which returns
an instances of the CTables collection class (essentially a wrapper
for a underlying Collection object). CTables has an Item property
(wraps the Collection's Item method) which returns and instance of
CTable held in the Collection. CTable has a Columns property which
returns a CColumns object which has an Item property etc etc.

Excel is at a couple of a disadvantages over VB6.0 here. First, to get
the nice neat tunnelling as above, the Item property of the collection
classes must be the default member of the class, otherwise you'd have
to use the slightly less satisfactory:

m_Schema.Tables.Item("MyTable").Columns.Item(1).Na me

There is no native way of specifying the default value doing this in
Excel VBA but there is a 'hack': export the class, add the magic line
using a text editor then reimport to Excel.

Another disadvantage of Excel is that classes types are limited to
Private and PublicNotCreatable. Ideally, I'd like the parent CSchema
to be the only class in the model that can be instantiated:

Private m_Schema As CSchema
...
Set m_Schema = New CSchema

The child CTable class would only be creatable using:

Dim oTable As CTable
Set oTable m_Schema.Tables.Add(<required arguments)

Think of the Excel object model: you can't create a worksheet using:

Dim ws As Excel.Worksheet
Set ws = New Excel.Worksheet

but you can use:

Dim ws As Excel.Worksheet
Set ws = ThisWorkbook.Worksheets.Add

Although object creation cannot be restricted in this way use custom
classes, it remains a good model to follow.

The following is the bare essentials of the five classes and some code
to test the classes. To 'hack' the default member for the collection
classes (CTables and CColumns), follow the embedded instructions.

' <In a standard module ---
Option Explicit

Private m_Schema As CSchema

Sub Test()

Set m_Schema = New CSchema

m_Schema.Tables.Add("MyTable1").Columns.Add "MyCol1"

m_Schema.Tables(1).Columns.Add "MyCol2"

Dim oTable As CTable
Set oTable = m_Schema.Tables.Add("MyTable2")
oTable.Columns.Add "MyCol1"
oTable.Columns.Add "MyCol2"
oTable.Columns.Add "MyCol3"

MsgBox m_Schema.Tables("MyTable1").Columns(1).Name

MsgBox m_Schema.Tables(2).ColumnListDelimited

End Sub
' </In a standard module ---


' <CSchema ---
Option Explicit

Private m_Tables As CTables

Public Property Get Tables() As CTables
Set Tables = m_Tables
End Property

Private Sub Class_Initialize()
Set m_Tables = New CTables
End Sub
' </CSchema ---


' <CTables ---
Option Explicit

Private m_colTables As Collection

Public Property Get Item(ByVal Index As Variant) As CTable
'Attribute Item.VB_UserMemId = 0
' *** Export to a file, uncomment the above Attribute line using
' a text editor then re-import ***
On Error Resume Next
Set Item = m_colTables.Item(Index)
End Property

Public Property Get NewEnum() As IUnknown
'Attribute NewEnum.VB_UserMemId = -4
'Attribute NewEnum.VB_MemberFlags = "40"
' *** Export to a file, uncomment the above two Attribute lines using
' a text editor then re-import ***Set NewEnum =
m_colColumns.[_NewEnum]
Set NewEnum = m_colTables.[_NewEnum]
End Property

Friend Function Add(ByVal Name As String) As CTable

Dim oTable As CTable
Set oTable = New CTable

oTable.Init Name

m_colTables.Add oTable, Name

Set Add = oTable

End Function

Friend Property Get Count() As Long

Count = m_colTables.Count

End Property

Friend Function Remove(ByVal Index As Variant) As Boolean

On Error Resume Next
m_colTables.Remove Index
Remove = (Err.Number = 0)

End Function

Private Sub Class_Initialize()

Set m_colTables = New Collection

End Sub
' </CTables ---


' <CTable ---
Option Explicit

Private m_Columns As CColumns
Private m_strName As String

Private Sub Class_Initialize()

Set m_Columns = New CColumns

End Sub

Public Property Get Name() As String

Name = m_strName

End Property

Friend Function Init( _
ByVal Name As String _
) As Boolean

m_strName = Name

Init = True

End Function

Public Property Get Columns() As CColumns

Set Columns = m_Columns

End Property

Public Property Get ColumnListDelimited( _
Optional Delimiter As String = ", " _
) As String

Dim oColumn As CColumn
Dim strReturn As String

If Columns.Count < 1 Then
Exit Property
End If

For Each oColumn In m_Columns
strReturn = strReturn & oColumn.Name & Delimiter
Next

' Remove trailing delimiter
strReturn = Left$(strReturn, Len(strReturn) - Len(Delimiter))

ColumnListDelimited = strReturn

End Property
' </CTable ---


' <CColumns ---
Option Explicit

Private m_colColumns As Collection

Public Property Get Item(ByVal Index As Variant) As CColumn
'Attribute Item.VB_UserMemId = 0
' *** Export to a file, uncomment the above Attribute line using
' a text editor then re-import ***
On Error Resume Next
Set Item = m_colColumns.Item(Index)
End Property

Public Property Get NewEnum() As IUnknown
'Attribute NewEnum.VB_UserMemId = -4
'Attribute NewEnum.VB_MemberFlags = "40"
' *** Export to a file, uncomment the above two Attribute lines using
' a text editor then re-import ***Set NewEnum =
m_colColumns.[_NewEnum]
Set NewEnum = m_colColumns.[_NewEnum]
End Property

Friend Function Add( _
ByVal Name As String _
) As CColumn

Dim oColumn As CColumn
Set oColumn = New CColumn

oColumn.Init Name

m_colColumns.Add oColumn, Name

Set Add = oColumn

End Function

Friend Property Get Count() As Long

Count = m_colColumns.Count

End Property

Friend Function Remove(ByVal Index As Variant) As Boolean

On Error Resume Next
m_colColumns.Remove Index
Remove = (Err.Number = 0)

End Function

Private Sub Class_Initialize()

Set m_colColumns = New Collection

End Sub
' </CColumns ---


' <CColumn ---
Option Explicit

Private m_strName As String

Public Property Get Name() As String
Name = m_strName
End Property

Friend Function Init( _
ByVal Name As String _
) As Boolean

m_strName = Name

Init = True

End Function
' </CColumn ---


Jamie.

--

That might possibly be the most excellent explanation I have ever
received on any programming whatever. Kudos! And thanks!

-Dave

(Jamie Collins) wrote in
om:

David Morton wrote ...

Before I waste my time trying to figure out how to do it, is it
possible to make collections of collections of data? I have a system
that has several different items listed under one larger item, which
is, in turn, part of a group of like items. I suppose a query on a
cost of a part would look something like this:

? BigCollection("SerialNumber").Part("PartNumber").C ost

Is it possible to put my objects in heirarchial stages like that?

I think the term for this code construct is 'tunnelling'. I'm a great
fan of this kind of object model design. When implementing, I find it
easiest to have a parent class. For example:

m_Schema.Tables("MyTable").Columns("MyCol1").Name

This requires five classes:

CSchema
CTables
CTable
CColumns
CColumn

CSchema is the parent class. It has a property Tables, which returns
an instances of the CTables collection class (essentially a wrapper
for a underlying Collection object). CTables has an Item property
(wraps the Collection's Item method) which returns and instance of
CTable held in the Collection. CTable has a Columns property which
returns a CColumns object which has an Item property etc etc.

Excel is at a couple of a disadvantages over VB6.0 here. First, to get
the nice neat tunnelling as above, the Item property of the collection
classes must be the default member of the class, otherwise you'd have
to use the slightly less satisfactory:

m_Schema.Tables.Item("MyTable").Columns.Item(1).Na me

There is no native way of specifying the default value doing this in
Excel VBA but there is a 'hack': export the class, add the magic line
using a text editor then reimport to Excel.

Another disadvantage of Excel is that classes types are limited to
Private and PublicNotCreatable. Ideally, I'd like the parent CSchema
to be the only class in the model that can be instantiated:

Private m_Schema As CSchema
...
Set m_Schema = New CSchema

The child CTable class would only be creatable using:

Dim oTable As CTable
Set oTable m_Schema.Tables.Add(<required arguments)

Think of the Excel object model: you can't create a worksheet using:

Dim ws As Excel.Worksheet
Set ws = New Excel.Worksheet

but you can use:

Dim ws As Excel.Worksheet
Set ws = ThisWorkbook.Worksheets.Add

Although object creation cannot be restricted in this way use custom
classes, it remains a good model to follow.

The following is the bare essentials of the five classes and some code
to test the classes. To 'hack' the default member for the collection
classes (CTables and CColumns), follow the embedded instructions.

' <In a standard module ---
Option Explicit

Private m_Schema As CSchema

Sub Test()

Set m_Schema = New CSchema

m_Schema.Tables.Add("MyTable1").Columns.Add "MyCol1"

m_Schema.Tables(1).Columns.Add "MyCol2"

Dim oTable As CTable
Set oTable = m_Schema.Tables.Add("MyTable2")
oTable.Columns.Add "MyCol1"
oTable.Columns.Add "MyCol2"
oTable.Columns.Add "MyCol3"

MsgBox m_Schema.Tables("MyTable1").Columns(1).Name

MsgBox m_Schema.Tables(2).ColumnListDelimited

End Sub
' </In a standard module ---


' <CSchema ---
Option Explicit

Private m_Tables As CTables

Public Property Get Tables() As CTables
Set Tables = m_Tables
End Property

Private Sub Class_Initialize()
Set m_Tables = New CTables
End Sub
' </CSchema ---


' <CTables ---
Option Explicit

Private m_colTables As Collection

Public Property Get Item(ByVal Index As Variant) As CTable
'Attribute Item.VB_UserMemId = 0
' *** Export to a file, uncomment the above Attribute line using
' a text editor then re-import ***
On Error Resume Next
Set Item = m_colTables.Item(Index)
End Property

Public Property Get NewEnum() As IUnknown
'Attribute NewEnum.VB_UserMemId = -4
'Attribute NewEnum.VB_MemberFlags = "40"
' *** Export to a file, uncomment the above two Attribute lines using
' a text editor then re-import ***Set NewEnum =
m_colColumns.[_NewEnum]
Set NewEnum = m_colTables.[_NewEnum]
End Property

Friend Function Add(ByVal Name As String) As CTable

Dim oTable As CTable
Set oTable = New CTable

oTable.Init Name

m_colTables.Add oTable, Name

Set Add = oTable

End Function

Friend Property Get Count() As Long

Count = m_colTables.Count

End Property

Friend Function Remove(ByVal Index As Variant) As Boolean

On Error Resume Next
m_colTables.Remove Index
Remove = (Err.Number = 0)

End Function

Private Sub Class_Initialize()

Set m_colTables = New Collection

End Sub
' </CTables ---


' <CTable ---
Option Explicit

Private m_Columns As CColumns
Private m_strName As String

Private Sub Class_Initialize()

Set m_Columns = New CColumns

End Sub

Public Property Get Name() As String

Name = m_strName

End Property

Friend Function Init( _
ByVal Name As String _
) As Boolean

m_strName = Name

Init = True

End Function

Public Property Get Columns() As CColumns

Set Columns = m_Columns

End Property

Public Property Get ColumnListDelimited( _
Optional Delimiter As String = ", " _
) As String

Dim oColumn As CColumn
Dim strReturn As String

If Columns.Count < 1 Then
Exit Property
End If

For Each oColumn In m_Columns
strReturn = strReturn & oColumn.Name & Delimiter
Next

' Remove trailing delimiter
strReturn = Left$(strReturn, Len(strReturn) - Len(Delimiter))

ColumnListDelimited = strReturn

End Property
' </CTable ---


' <CColumns ---
Option Explicit

Private m_colColumns As Collection

Public Property Get Item(ByVal Index As Variant) As CColumn
'Attribute Item.VB_UserMemId = 0
' *** Export to a file, uncomment the above Attribute line using
' a text editor then re-import ***
On Error Resume Next
Set Item = m_colColumns.Item(Index)
End Property

Public Property Get NewEnum() As IUnknown
'Attribute NewEnum.VB_UserMemId = -4
'Attribute NewEnum.VB_MemberFlags = "40"
' *** Export to a file, uncomment the above two Attribute lines using
' a text editor then re-import ***Set NewEnum =
m_colColumns.[_NewEnum]
Set NewEnum = m_colColumns.[_NewEnum]
End Property

Friend Function Add( _
ByVal Name As String _
) As CColumn

Dim oColumn As CColumn
Set oColumn = New CColumn

oColumn.Init Name

m_colColumns.Add oColumn, Name

Set Add = oColumn

End Function

Friend Property Get Count() As Long

Count = m_colColumns.Count

End Property

Friend Function Remove(ByVal Index As Variant) As Boolean

On Error Resume Next
m_colColumns.Remove Index
Remove = (Err.Number = 0)

End Function

Private Sub Class_Initialize()

Set m_colColumns = New Collection

End Sub
' </CColumns ---


' <CColumn ---
Option Explicit

Private m_strName As String

Public Property Get Name() As String
Name = m_strName
End Property

Friend Function Init( _
ByVal Name As String _
) As Boolean

m_strName = Name

Init = True

End Function
' </CColumn ---


Jamie.

--