Object property taken from worksheet cell?

[nigelandrewfoster]

Hello, feeling my way forward in object oriented approach to structuring my code. Quick question. In the example I'm working on I have a class called clsTeam. I give the team a name using oTeam.Name="Team 1", for example:

Option Explicit

Private pName As String


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


Public Property Let Name(Value As String)
    pName = Value
End Property

How can I modify the code so that it always takes the value from a table of names in the worksheet please (i.e. is always linked to the cell object)? What I want to be able to achieve is to be able to use Teams(1).Name to retrieve the cell contents of cell A2 in a worksheet, Teams(2).Name to retrieve the name in A3, etc. Once I have learned to do this, I can then utilise Teams(1).Colour to retrieve the .Interior.Color property of that cell directly. Is this possible? The only way forward I could think of was by retrieving the index of the Team object from the Teams collection and using that as an offset, but couldn't get any further.

Thanks for your time.

 

[RoryA]

That doesn't seem like great design to me, but if you want the Team to be linked directly to a specific cell, you could assign the cell directly as a property of the class so that your other properties can simply read that cell directly.

 

[nigelandrewfoster]

Thanks. I will try. The team names and colours are the only things the user is to provide. Given the fact objects cease to be when macros finish running I need a way of storing their values. The rest can be rebuilt each time the macro runs. I'm really using this as an exercise in understanding objects with a view to eventually moving towards other languages.

 

[nigelandrewfoster]

So more generally, how can you define a property of an object using the object's index as an argument in its definition?

 

[RoryA]

If you have a collection of the objects, accessing one object via its index automatically gives you an object reference and thus access to all its properties, so I don't really understand the question, I'm afraid.