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

 

[RoryA]

It doesn't make sense for Team to have one Player variable. It should have a Players variable, if you think about it.

 

[mahmed1]

---

It doesn't make sense for Team to have one Player variable. It should have a Players variable, if you think about it.

Duh that's true

I was trying to have a Team that has a name
and then Team.Player.Name to add a player and then players class to add the player from the team

I am still getting variable not set even though i have changed from player to players

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

 

[RoryA]

You need to change the variable type, not just its name...

 

[mahmed1]

Not sure what im doing wrong - i thought i had changed accordingly?

Have i done things right by adding a player class and players class or would you have just a players class? (Collection)

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

 

[mahmed1]

Hi Rory

I cant seem to figure out where i am going wrong

I have realised that maybe i dont need 3 classes (Team, Player and players(collection) and have put the collection in the team class but still getting Object error

Please see the amended code

Standard

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

Class Team

Private cPlayers As Collection
Private pName As String
Private pPlayers As Player

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

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

Sub Add(PlayerName As Player, PlayerKey As String)
    cPlayers.Add PlayerName, pPlayers.Name
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

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

 

[RoryA]

This:

Private pPlayers As Player

should be:

Private pPlayers As Players

and your properties should be adjusted similarly to use Players rather than Player.

It makes sense to me to have a Players collection for your Player objects.

 

[Kyle123]

You haven't created an instance of the Player object when you attempt to access it here:

MyTeam.Players.Name = ws.Range("A" & i).value

However your code is illogical, you create a team for each row and then remove it.

I think you want:

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


End Sub

You also need to change:

Sub Add(PlayerName As Player, PlayerKey As String)
    cPlayers.Add PlayerName, pPlayers.Name
End Sub

to:

Sub Add(PlayerName As Player, PlayerKey As String)
    cPlayers.Add PlayerName, PlayerKey
End Sub

 

[mahmed1]

This:

Private pPlayers As Player

should be:

Private pPlayers As Players

and your properties should be adjusted similarly to use Players rather than Player.

It makes sense to me to have a Players collection for your Player objects.

Hi Rory

Am i right in saying that the way i set up 3 classes
Team, Player and players is ok and then change pPlayers to Players to access the Collection class? or are you saying remove the player class and just have the team and players class?

 

[mahmed1]

You haven't created an instance of the Player object when you attempt to access it here:

MyTeam.Players.Name = ws.Range("A" & i).value

However your code is illogical, you create a team for each row and then remove it.

For i = 2 To 10
Set oPlayer = New Player
oPlayer.Name = ws.Range("A" & i).value
MyTeam.Add oPlayer, oPlayer.Name
Next i


I think you want:

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


End Sub

You also need to change:

Sub Add(PlayerName As Player, PlayerKey As String)
    cPlayers.Add PlayerName, pPlayers.Name
End Sub

to:

Sub Add(PlayerName As Player, PlayerKey As String)
    cPlayers.Add PlayerName, PlayerKey
End Sub

Hi Kyle

Thank you

Can you please advise 1 thing
Why is the o.Player in the loop and could it not be outside the loop?
I know it creates an instance each time but i am just curious to why we need to create the instance multiple times