Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help with Objects and Nested Collections

  Asked By: Leon    Date: Nov 26    Category: MS Office    Views: 902
  

I am trying to create my first object model to use in
Excel. I have created a unit object that belongs to a
unit’s collection. I can set and retrieve properties
to the objects in the collection just fine.

However, I want to have a GenValues value property
that contains 25 values stored in the object. These
values will come from a range in a spreadsheet. As a
result, I will either have to store the values in an
array or use two collections. The following would be
the output from the object

Units.item(1).Name Jack
Units.item(1).Status U

Units.item(1).GenValues.(1).Value 5
Units.item(1).GenValues.(2).Value 10
Units.item(1).GenValues.(25).Value 150

So far I have not been able to get an object with
nested connections to work correctly. In addition, I
have not been able to get an array to work correctly
inside an object.

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Juan Reynolds     Answered On: Nov 26

Try this...

Code for Class Module "Unit":

'Public array  not allowed in class module, so use private
'array with let and get properties
Private GV(1 To 25)
Property Let GenValues(index, value)
GV(index) = value
End Property
Property Get GenValues(index)
GenValues = GV(index)
End Property

Code in regular module:

Dim Units As New Collection
Sub TestU()
'create a new unit
Set MyUnit = New Unit
'fill in some GenValues
For i = 1 To 25
MyUnit.GenValues(i) = i ^ 2
Next
Units.Add MyUnit
'create another new unit
Set MyUnit = New Unit
'fill in some more GenValues
For i = 1 To 25
MyUnit.GenValues(i) = i ^ 3
Next
Units.Add MyUnit
'access the GenValues
x = Units(1).GenValues(2)
y = Units(2).GenValues(2)
End Sub

 
Answer #2    Answered By: Rafael Thompson     Answered On: Nov 26

The object  code that you provided worked great. I will
just use a private object array  instead of trying to
setup a collection  that is part of another object.

Does anyone know of any good sample object model
spreadsheets?

The following Microsoft link is one of the best I
found about creating objects  in Excel.
www.microsoft.com/.../ch\
04.mspx

However, the link refers to sample spreadsheets
ListComboWiz.xla and EnumWindows.xls that as far as I
know cannot be downloaded.

 
Didn't find what you were looking for? Find more on Help with Objects and Nested Collections Or get search suggestion and latest updates.




Tagged:        

 
 
 
 

Related Post