Nearly there i think - Where am i going wrong with the class

[mahmed1]

Hi

I am trying to set up a class that has a Team name, Player Name and Players (Collection to add players) but i am getting an error saying variable not set. Please advise if im on the right track and give some pointers to where i can improve

Thank You

P.s how would i encorporate into the NEW class multiple names from the sheet1 Range A2:A10 where it hold the player names

Do i need to create an instance for each player name in the loop or can i leave it as 1 instance?

i.e

For i = 2 to 10

Set AddPlayer = New PlayersTeam.Player.Name = Sheets("Sheet1").Range("A" & i).value
Players.Add Player, Team.Player.Name
Set Players = Nothing
Next i

I have set up these 3 classes.

Team clas has 2 properties - Name and Player (class) to access Player Class

Normal Module

Sub test()
Dim Team As Team
Dim Player As Player
Dim Players As Players

Set Team = New Team
Set Player = New Player
Set Players= New Players

Team.Player.Name = "Joe Bloggs"
Players.Add Player, Team.Player.Name

End Sub

Team Class module

Private pName As String
Private pPlayer As Player
Public Property Let Name(value As String)
pName = value
End Property
Public Property Get Name() As String
Name = pName
End Property
Public Property Let Player(value As Player)
Set pPlayer = value
End Property
Public Property Get Player() As Player
Set Player = pPlayer
End Property

Player Class module

Private pName As String
Public Property Let Name(value As String)
pName = value
End Property
Public Property Get Name() As String
Name = pName
End Property

Players Class Module (Collection)

Private pAddPlayers As Collection

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

Sub Add(Item As Player, Key As String)
    pAddPlayers.Add Item, Key
End Sub

Public Property Get Count() As Long
Count = pAddPlayers.Count
End Property

Public Property Get Item(NameORnumber As Variant) As Player
Set Item = pAddPlayers.Item(NameORnumber)
End Property

 

[mahmed1]

Thank You Kyle

I guess i want to be able to add players to the relevant teams and then i want to be able to get how many teams i have and how many players i have altogether aswel as how many players i have for each team so some team may have 12 and others 13 etc

 

[mahmed1]

I seem to be ok when creating 1 collection and 1 Class Team now where i am struggling is creating multiple teams and collections

I am ok with creating a Team class and then creating a player and players class to hold player properties like Name and then Players Class to add the player to the collection.

But when adding multiple teams and multiple players thata when i am struglling a bit

Once i get a breakdown on that part i will be ok with this going forward

So at present i want to hold a Team Class to hold all the Teams and Players attached to those teams
And a Player class to hold all the player details - Count how many players there are in total

Now which ever way you think is best to tackle this, i would go with that as i am new to classes

So far i have come to grips with the Let, Set, Get, Initialize, Collections but struggling with objects within objects and being more clear with this
i.e Team.Players.Name etc...

If you can give an example an breakdown on how to create multiple collections and objects like above, this will help me immensly creating multiple

I look forward to your help once again Kyle and Rory

You both have been so helpful to me

Many thanks

 

[Kyle123]

Before I give you an example, is this a learning exercise or is it a real world problem? The reason that I ask is that you can do what you want just using nested collections and it simplifies things a little.

If this is a learning exercise, I'll do it in classes

 

[mahmed1]

Before I give you an example, is this a learning exercise or is it a real world problem? The reason that I ask is that you can do what you want just using nested collections and it simplifies things a little.

If this is a learning exercise, I'll do it in classes

Hi

Thank you

Yes its a learning excercise as my new role could involve creating custome classes and i thought this would be the best way to learn.

If you could give examples of nested custome collections and classes that would be amazing and would help me massively

Ps Am i right in saying that as soon as you add to the collection, it adds a single item therefore in a loop referring to your earlier post, i have to create an instance each time else i would keep changing the variable multiple times having 1 instance

Is it the same as

Dim ws as worksheet
Set ws = sheets ("Sheet1") ' First instance
Set ws = Sheets("Sheet2") - Variable changed where as above if created new instance ws will hold Sheet1 and Sheet2 in the colection?

 

[Kyle123]

Yes, that's right, consider:

Sub test()


    Dim coll As Collection
    Dim person As Player
    
    Set person = New Player
    Set coll = New Collection
    
    person.Name = "Kyle"
    coll.Add person
    
    person.Name = "James"
    
    Debug.Print coll(1).Name


End Sub

I'll put together a working example for you. It's worth noting that Excel makes this difficult and you have to write more code than you would in other languages since it isn't really very object orientated and doesn't support inheritance

 

[mikerickson]

I would avoid having both a Players object and a Team object.

You are setting up an Object Model for this sport.
Unless a group of players (a Players object) acts in some way that is different than a Team acts, Players should be a property of a Team.

A naked Players Collection is probably easier to implement.
Similarly with a Teams object vs. a League object.

Perhaps something like this. Note that all players are added via the League object. Note also that in this formulation the player's Name must be unique.
Note also that the back-referning .League property of a clsTeam object and the .Team property of a clsPlayer object, that the explicit .Disolve methods must be used.

(Yes, I know that I use Public variables where others would use a private variable, a Property Get and a Property Let.)

In clsPlayer

' in clsPlayer

Public Name As String
Public Team As clsTeam

in clsTeam

' in clsTeam

Public Losses As Long
Public League As clsLeague
Public Name As String
Public Players As Collection
Public Wins As Long

Function Add(Name As String) As clsPlayer
    Set Add = League.AddPlayer(Name, ToTeam:=Me)
End Function

Property Get Count() As Long
    Count = Players.Count
End Property

Sub Disolve()
    Dim onePlayer As clsPlayer
    Set League = Nothing
    For Each onePlayer In Players
        Set onePlayer.Team = Nothing
        Set onePlayer = Nothing
    Next onePlayer
    Set Players = Nothing
End Sub

Property Get WinningPercentage() As Double
    If (Losses + Wins) = 0 Then
        WinningPercentage = -1
    Else
        WinningPercentage = Wins / (Losses + Wins)
End Property

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

in clsLeague

' in clsLeague

Public Name As String
Public Teams As Collection
Const NullTeamName As String = "Unassigned"

Function AddPlayer(Name As String, Optional ToTeam As Variant) As clsPlayer
    Dim newPlayer As New clsPlayer
    
    If IsMissing(ToTeam) Then Set ToTeam = Me.NullTeam
    If TypeName(ToTeam) = "String" Then Set ToTeam = Me.Teams(ToTeam)
    
    With newPlayer
        .Name = Name
        Set .Team = ToTeam
    End With
    
    ToTeam.Players.Add Item:=newPlayer, Key:=newPlayer.Name
    
    Set AddPlayer = newPlayer
    Set newPlayer = Nothing
End Function

Function AddTeam(Name As String) As clsTeam
    Dim newTeam As New clsTeam
    With newTeam
        .Name = Name
        Set .League = Me
    End With
    Teams.Add Item:=newTeam, Key:=newTeam.Name
    Set AddTeam = newTeam
    Set newTeam = Nothing
End Function

Sub Disolve()
    Dim oneTeam As clsTeam
    For Each oneTeam In Teams
        oneTeam.Disolve
        Set oneTeam = Nothing
    Next oneTeam
    Set Teams = Nothing
End Sub

Property Get NullTeam() As clsTeam
    Set NullTeam = Me.Teams(NullTeamName)
End Property

Property Get Players(Optional IncludeNonPlayers As Boolean = False) As Collection
    Dim Result As New Collection
    Dim oneTeam As clsTeam, onePlayer As clsPlayer
    For Each oneTeam In Teams
        If oneTeam.Name <> NullTeamName Or IncludeNonPlayers Then
            For Each onePlayer In oneTeam.Players
                Result.Add Item:=onePlayer, Key:=onePlayer.Name
            Next onePlayer
        End If
    Next oneTeam
    Set Players = Result
End Property

Sub TransferPlayerTo(Player As Variant, ToTeam As Variant)
    Dim FromTeam As clsTeam
    If TypeName(Player) = "String" Then Set Player = Me.Players(True)(Player)
    If TypeName(ToTeam) = "String" Then Set ToTeam = Me.Teams(ToTeam)
    Set FromTeam = Player.Team
    
    FromTeam.Players.Remove Player.Name
    Set Player.Team = ToTeam
    ToTeam.Players.Add Item:=Player, Key:=Player.Name
End Sub

Private Sub Class_Initialize()
    Set Teams = New Collection
    Me.AddTeam NullTeamName
End Sub

Private Sub Class_Terminate()
    
End Sub

These would be used like

' in normal module

Sub test()
    Dim aLeague As New clsLeague
    
    With aLeague
        With .AddTeam("Tigers")
            .Add "Bob"
            .Add "Sam"
            .Add "Tony"
        End With
        With .AddTeam("Lions")
            .Add "Mary"
            .Add "Joan"
        End With
        
        .AddPlayer "Donny"
        .AddPlayer "Bob Smith", ToTeam:=.Teams("Lions")
        .TransferPlayerTo "Donny", ToTeam:="Tigers"

        MsgBox .Teams("Tigers").Count
        
        .Disolve
    End With
    
    Set aLeague = Nothing
End Sub

 

[Kyle123]

Mike's is a little more fleshed out than mine, and I wouldn't have bothered with the below if I'd checked the thread first:

Teams

Private p_Teams As Dictionary


Public Function AddTeam(teamName As String) As Team
    Dim t As Team
    
    Set t = New Team
    
    t.Name = teamName
    p_Teams.Add teamName, t
    
    Set AddTeam = t
    
End Function
Public Function GetTeamByName(teamName As String) As Team
    Set GetTeamByName = p_Teams(teamName)
End Function


Public Property Get TotalPlayers() As Long
    
    Dim t
    For Each t In p_Teams.Items
        TotalPlayers = TotalPlayers + t.PlayersInTeam
    Next t
    
End Property


Public Property Get Teams() As Variant
    Teams = p_Teams.Items
End Property


Private Sub Class_Initialize()
    Set p_Teams = New Dictionary
End Sub

Team

Private p_Name As String
Private p_Players As Dictionary


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


Public Property Let Name(value As String)
    p_Name = value
End Property


Public Function AddPlayer(PlayerName As String) As Player


    Dim p As Player
    Set p = New Player
    p.Name = PlayerName
    p_Players.Add PlayerName, p
    
End Function


Public Property Get PlayersInTeam() As Long
    PlayersInTeam = p_Players.Count
End Property


Public Property Get Players() As Variant
    Players = p_Players.Items
End Property




Private Sub Class_Initialize()
    Set p_Players = New Dictionary
End Sub

Player

Private p_Name As String


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


Public Property Let Name(value As String)
    p_Name = value
End Property

Used as follows:

Sub test()
    
    Dim oLeague As Teams
    Dim oPlayer
    Dim oTeam
    
    Set oLeague = New Teams
    
    With oLeague.AddTeam("Boys Team")
        .AddPlayer "Kyle"
        .AddPlayer "James"
        .AddPlayer "Bob"
        .AddPlayer "Pete"
    End With
    
    With oLeague.AddTeam("Girls Team")
        .AddPlayer "Mary"
        .AddPlayer "Helen"
        .AddPlayer "Sarah"
    End With
    
    Debug.Print "Total Players: ", oLeague.TotalPlayers
    
    'Looping through the teams
    For Each oTeam In oLeague.Teams 'Loop Through teams
        Debug.Print "Members For : " & oTeam.Name
        Debug.Print "----------------------------------------------------"
        For Each oPlayer In oTeam.Players 'Loop through members
            Debug.Print oPlayer.Name
        Next oPlayer
    Next oTeam
    
    
    'You can also access properties directly
    Debug.Print "The Boys Team Has " & oLeague.GetTeamByName("Boys Team").PlayersInTeam & " Players"
    
End Sub

 

[Kyle123]

Mike's structure is somewhat more advanced than mine and deals with some complex issues, notably circular referencing.

However the thing that we have in common is the abstraction of creating a new player/team into the holding object and then returning it. This keeps your code cleaner and easier to read since you don't need to keep creating an instance of a team/player in your calling code. You can simply use an add method

 

[mikerickson]

The point I was emphasizing is that the OP is creating an Object Model.
The Object Model should model reality. Objects in the model should be matched by real objects (League, Team, Player).
One should avoid creating objects and properties and that are programming overhead. It's not completely unavoidable, but helps the model be more easy in use.

 

[mahmed1]

Thank you so much

i will analyse the code

Kyle i could see you used dictionary instead of collection, is it because you have more flexibility with dict compared to collection? I guess id just have to replace dict for coll if i wanted to do it that way

having said that am
i right in saying the way to approach was to

1) have a teams class which will add teams
2) have a team class to add player to team

with the way i originally did the code, was i doing it right although the most long winded way?

ie team, teams, player and players class?