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

 

[Kyle123]

Because you need to create multiple players to add to your team. If it were outside the loop, you'd just be updating a single player

 

[mahmed1]

Oh Ok

For some reason i thought once its added in the collection, then that stored so it moved on to the next player

i.e

oPlayer.Name = ws.Range("A" & i).value
cPlayers.Add oPlayer, oPlayer.Name

Store next player to the collection using the oPlayer class

Now i can see what Rory meant by having a players class to hold all player details.
What if i was adding players to different teams also (So multiple teams)
I guess i would also have to create a Collection called teams which i can do but i would i encorporate the players and teams colection to 1 so when adding the player
i am also adding to relevant teams collection?

Here is the amended code taking your and Rory's advice

Sub test()
Dim ws As Worksheet
Dim MyTeam As Team
Dim oPlayer As Player
Dim cPlayers As Players
Set ws = Worksheets("Sheet1")
Set MyTeam = New Team
Set cPlayers = New Players
For i = 2 To 10
   Set oPlayer = New Player
   oPlayer.Name = ws.Range("A" & i).value
   cPlayers.Add oPlayer, oPlayer.Name
Next i
   MsgBox cPlayers.Count
Set ws = Nothing
Set oPlayer = Nothing
End Sub

Private cPlayers As Collection
Private Sub Class_Initialize()
Set cPlayers = New Collection
End Sub
Sub Add(PlayerName As Player, PlayerKey As String)
    cPlayers.Add PlayerName, PlayerKey
End Sub
Public Property Get Count() As Long
Count = cPlayers.Count
End Property
Public Property Get GetPlayer(PlayerNameOrNumber As Variant) As Player
Set GetPlayer = cPlayers.Item(PlayerNameOrNumber)
End Property

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

 

[RoryA]

You'd create a Team, add the Players relevant to it, then add the Team to the Teams collection.

 

[mahmed1]

You'd create a Team, add the Players relevant to it, then add the Team to the Teams collection.

Hi Rory

Ok i have add an attempt but getting this object not set error

Here is the code

I have Team, Teams (collection), Player, Players (Collection) 4 classes

This is my code (I think i am getting there eventually)

Standard Module

Sub test()
Dim ws As Worksheet
Dim MyTeam As Team
Dim cTeams As Teams
Dim MyPlayer As Player
Dim cPlayers As Players
Set ws = Worksheets("Sheet1")
Set cPlayers = New Players
Set cTeams = New Teams
For i = 2 To 10
   Set MyPlayer = New Player
   Set MyTeam = New Team
    
   MyPlayer.Name = ws.Range("A" & i).value
   MyTeam.Name = ws.Range("B" & i).value
    
   cPlayers.Add MyPlayer, MyPlayer.Name
   cTeams.Add MyTeam, MyPlayer, MyPlayer.Name
Next i
   
   MsgBox cPlayers.Count
   MsgBox cTeams.Count
End Sub

Team Class

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

Teams Class

Private cTeams As Collection
Private Sub Class_Initialize()
Set cPlayers = New Collection
End Sub
Sub Add(TeamName As Team, PlayerName As Player, TeamKey As String)
    cTeams.Add TeamName, PlayerName, TeamKey
End Sub
Public Property Get Count() As Long
Count = cTeams.Count
End Property
Public Property Get GetTeam(PlayerNameOrNumber As Variant) As Team
Set GetTeam = cPlayers.Item(PlayerNameOrNumber)
End Property

Player

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

Private cPlayers As Collection
Private Sub Class_Initialize()
Set cPlayers = New Collection
End Sub
Sub Add(PlayerName As Player, PlayerKey As String)
    cPlayers.Add PlayerName, PlayerKey
End Sub
Public Property Get Count() As Long
Count = cPlayers.Count
End Property
Public Property Get GetPlayer(PlayerNameOrNumber As Variant) As Player
Set GetPlayer = cPlayers.Item(PlayerNameOrNumber)
End Property

 

[RoryA]

You haven't initialised the Teams or Players objects before you try to use them.

 

[mahmed1]

You haven't initialised the Teams or Players objects before you try to use them.

I thought i did that with this line of code in the standard module
Dim ws As Worksheet
Dim MyTeam As Team
Dim cTeams As Teams
Dim MyPlayer As Player
Dim cPlayers As Players
Set ws = Worksheets("Sheet1")
Set cPlayers = New Players
Set cTeams = New Teams


is the code done the right way or have i added too much and did it long winded way? Trying to learn from the best

 

[mahmed1]

You haven't initialised the Teams or Players objects before you try to use them.

This is the line where i am getting an error

For i = 2 To 10
   Set MyPlayer = New Player
   Set MyTeam = New Team
 
   MyPlayer.Name = ws.Range("A" & i).value
   MyTeam.Name = ws.Range("B" & i).value
    
   cPlayers.Add MyPlayer, MyPlayer.Name
   MsgBox cPlayers.Count
   
   cTeams.Add MyTeam, MyPlayer, MyPlayer.Name
   MsgBox cTeams.Count
    
Next i

Private cTeams As Collection
Private Sub Class_Initialize()
Set cTeams = New Collection
End Sub
Sub Add(TeamName As Team, PlayerName As Player, PlayerKey As String)
    cTeams.Add TeamName, PlayerName, PlayerKey
End Sub
Public Property Get Count() As Long
Count = cTeams.Count
End Property
Public Property Get GetTeam(PlayerNameOrNumber As Variant) As Team
Set GetTeam = cPlayers.Item(PlayerNameOrNumber)
End Property

 

[RoryA]

You did - sorry, I didn't see those lines.

Your code keeps changing in between posts - please post what you have now.

(in the previous version you were not initialising the cTeams colleciton in the class. You're also passing three arguments to the Add method of a Collection which won't work.)

 

[mahmed1]

You did - sorry, I didn't see those lines.

Is till get an error on the highlighted parts in red see (prev code)

Ultimately what i want is to have

A Players collection so i can retrieve the team of that player and the count of players overall
A Teams collection so i can add teams, retrieve a players from that team, count all the teams and all the players (how many) for that team

I think i may be slightly off but cant seem to figure why i get the object error/type mismatch now for the teams collection

Sorry (i promise this is the code) It's because i was trying to change to fix the error and then posted the latest codes

Here is the code

Standard Module

Sub test()
Dim ws As Worksheet
Dim MyTeam As Team
Dim cTeams As Teams
Dim MyPlayer As Player
Dim cPlayers As Players
Set ws = Worksheets("Sheet1")
Set cPlayers = New Players
Set cTeams = New Teams
For i = 2 To 10
   Set MyPlayer = New Player
   Set MyTeam = New Team
   
   MyPlayer.Name = ws.Range("A" & i).value
   MyTeam.Name = ws.Range("B" & i).value
    
   cPlayers.Add MyPlayer, MyPlayer.Name
   cTeams.Add MyTeam, MyPlayer, MyPlayer.Name
   
   MsgBox cTeams.Count
   MsgBox cPlayers.Count
    
Next i
End Sub

Player Class

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

Private cPlayers As Collection
Private Sub Class_Initialize()
Set cPlayers = New Collection
End Sub
Sub Add(PlayerName As Player, PlayerKey As String)
    cPlayers.Add PlayerName, PlayerKey
End Sub
Public Property Get Count() As Long
Count = cPlayers.Count
End Property
Public Property Get GetPlayer(PlayerNameOrNumber As Variant) As Player
Set GetPlayer = cPlayers.Item(PlayerNameOrNumber)
End Property

Team Class

Private pName As String
Private pPlayers As New 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 Players(value As Player)
Set pPlayers = value
End Property
Public Property Get Players() As Player
Set Players = pPlayers
End Property

Teams Class

Private cTeams As Collection
Private Sub Class_Initialize()
Set cTeams = New Collection
End Sub
Sub Add(TeamName As Team, PlayerName As Player, PlayerKey As String)
    cTeams.Add TeamName, PlayerName, PlayerKey
End Sub
Public Property Get Count() As Long
Count = cTeams.Count
End Property
Public Property Get GetTeam(PlayerNameOrNumber As Variant) As Team
Set GetTeam = cPlayers.Item(PlayerNameOrNumber)
End Property

 

[Kyle123]

You can't add three elements to a collection like that, it is simply a key and value, not a key and two values - you need to have a think about how you want to approach this. It usually helps by drawing it on paper