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]

i have a couple of questions

when retrieving the team names i can see you defined to the type as variant. Why is that?
i tried to in the main sub get the intellisense when trying retrieve the name of teams which i dont get so i tried to change the variant to Team but that dont work


any chance you can advise


thank you

Because the team names do not return Teams, they return an array containing the team names. Arrays tend to be passed around as variants in vba

 

[mahmed1]

Thank you kyle

so when returning teams or players which are stored in a collection or dictionary and they are passed to a variable, they are passed as am array therefore will be a variant type?

Is there a way to get the intellisense or is this an occasion where that wont happen?

ie for each oteam in league. Teams
Msgbox oteam.name (i dont get the intellisense for the .name)
next oteam

 

[Kyle123]

No, passing an array passes an array - the item and key properties of the dictionary return an array, enumerating over it passes back whatever is in the value.

If you want the intellisense you will need to create a strongly typed collection / dictionary - which was the point of the video you were watching

 

[mahmed1]

Thank You Kyle

I have another question

With this line of code in the teams class, when stepping through, i can see it jumps to the Team class which is fine but t is defined as a variant in this sub so how does it know to look at the team class?
I know the Team object with t was created outside that sub and we define t again LIKE dim t therefore unsure how it knows

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

 

[Kyle123]

t doesn't need to know what it is. t is simply a reference to the current item in the array we are looping through. Since the only thing in the array are Team objects, then t will be a Team.

I think you need to do some reading on what objects and variables actually are along with reading up on references and how they are passed. After that I think all this will make much more sense.

 

[mahmed1]

Thank YOu Kyle

You have really been awesome

I have 1 slight problem

What if i had more teams - I know i could add them in the code like the examples you have given but this could be inefficient as i have a list on my sheet

Now say in sheet1 i had the teams and name like this

Team Name
Boys Team - Kyle
Boys Team - Andy
Girls Team - Gemma
Over 65 - Andrew

Etc Say from A2:B10
Theses will be sorted

How can i encorporate this in code so rather than hard coding then put the players in the teams via the input on sheet1?

Many Thanks

 

[Kyle123]

How you choose to do it is mostly down to design choice. I'd change the add code in the Teams and Team class to return a Player/Team if it already exists, then add a method to the Teams class to add a team and player in a single call:

Team Class

Public Function AddPlayer(PlayerName As String) As Player


    If Not p_Players.exists(PlayerName) Then
        Dim p As Player
        Set p = New Player
        p.Name = PlayerName
        p_Players.Add PlayerName, p
    End If
    
    Set AddPlayer = p_Players(PlayerName)
    
End Function

Teams Class

Public Function AddTeam(TeamName As String) As Team
        
    If Not p_Teams.exists(TeamName) Then
        Dim t As Team
        Set t = New Team
        t.Name = TeamName
        p_Teams.Add TeamName, t
    End If


    Set AddTeam = p_Teams(TeamName)
    
End Function
Public Function AddTeamAndPlayer(TeamName As String, PlayerName As String) As Player
   Set AddTeamAndPlayer = AddTeam(TeamName).AddPlayer(PlayerName)
End Function

Then I can simply use:

Dim League As New Teams
Dim l As Long


For x = 2 To 10
    With Sheets(1)
        'League.AddTeamAndPlayer "Boys Team", "Kyle"
        League.AddTeamAndPlayer .Range("a" & x).value, .Range("b" & x).value
    End With
End Sub

 

[mahmed1]

Hi Kyle - Once again thank you so much. I think I now have a really good understanding in regards to class modules thanks to yourself and I am practicing to get more used to it

I did not know that you could pass a collection to another collection

From my understanding, would I be correct in saying the way this code is working is something like this

Public Function AddTeam(TeamName As String) As Team

If Not cTeams.Exists(TeamName) Then

Dim t As Team

Set t = New Team

t.Name = TeamName

cTeams.Add TeamName, t

End If

Set AddTeam = cTeams(TeamName)

End Function

Public Function AddTeamAndPlayer(TeamName As String, PlayerName As String) As Player

Set AddTeamAndPlayer = AddTeam(TeamName).AddPlayer(PlayerName)

End Function

Public Function AddTeamAndPlayer(TeamName As String, PlayerName As String) As Player

**********Set AddTeamAndPlayer = Whatever is in Addteam and then whatever is within that (Like a nested if FUNCTION)

Set AddTeamAndPlayer = AddTeam(TeamName).AddPlayer(PlayerName)

End Function

So by Setting AddTeam by using the AddTeam function is saying

Set AddTeam to collection("Boys Team") so now this line becomes collection Boys Team

Set AddTeamAndPlayer = AddTeam(Collection "Boys Team") .AddPlayer(PlayerName)

and then it processes the next part .AddPlayer(PlayerName)

and by setting the player to the AddTeam its setting AddPlayer to the collection which holds that player details.

P.s why is the SET and AS PLAYER needed in the AddTeamandPlayer Function as I cant see how this is being used again in the main code so could I have not done

Public Function AddTeamAndPlayer(TeamName As String, PlayerName As String)

AddTeamAndPlayer = AddTeam(TeamName).AddPlayer(PlayerName)

End Function

<tbody>
</tbody><colgroup><col></colgroup>

 

[Kyle123]

I can't tell what you are trying to say as I can't really make out your code. Could you please edit and put it in code tags?

 

[mahmed1]

Sorry i dont know why it printed like that

Hi Kyle - Once again Thank you so much. I think I now have a really good understanding in regards to class modules thanks to yourself and I am practicing to get more used to it

I did not know that you could pass a collection to another collection

From my understanding, would I be correct in saying the way this code is working is something like this

So by Setting AddTeam by using the AddTeam function is saying

Set AddTeam to collection("Boys Team") so now this line becomes collection Boys Team

Set AddTeamAndPlayer = AddTeam(Collection "Boys Team") .AddPlayer(PlayerName)

and then it processes the next part .AddPlayer(PlayerName)

and by setting the player to the AddTeam its setting AddPlayer to the collection which holds that player details.

P.s why is the SET and AS PLAYER needed in the AddTeamandPlayer Function as I cant see how this is being used again in the main code so could I have not done

Public Function AddTeamAndPlayer(TeamName As String, PlayerName As String)

AddTeamAndPlayer = AddTeam(TeamName).AddPlayer(PlayerName)

End Function

<tbody>

[TR]
[TD][B]Public Function AddTeam(TeamName As String) As Team[/B][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][B]If Not cTeams.Exists(TeamName) Then[/B]
[/TD]
[/TR]
[TR]
[TD][B]Dim t As Team[/B]
[/TD]
[/TR]
[TR]
[TD][B]Set t = New Team[/B]
[/TD]
[/TR]
[TR]
[TD][B]t.Name = TeamName[/B]
[/TD]
[/TR]
[TR]
[TD][B]cTeams.Add TeamName, t[/B]
[/TD]
[/TR]
[TR]
[TD][B]End If[/B]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][B]Set AddTeam = cTeams(TeamName)[/B]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][B]End Function[/B]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]

[TR]
[TD][B]Public Function AddTeamAndPlayer(TeamName As String, PlayerName As String) As Player[/B]
[/TD]
[/TR]
[TR]
[TD][B]Set AddTeamAndPlayer = AddTeam(TeamName).AddPlayer(PlayerName)[/B]
[/TD]
[/TR]
[TR]
[TD][B]End Function[/B]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Public Function AddTeamAndPlayer(TeamName As String, PlayerName As String) As Player
[/TD]
[/TR]
[TR]
[TD][B]**********Set AddTeamAndPlayer = Whatever is in Addteam and then whatever is within that (Like a nested if FUNCTION)[/B]
[/TD]
[/TR]
[TR]
[TD]Set AddTeamAndPlayer = AddTeam(TeamName).AddPlayer(PlayerName)
[/TD]
[/TR]
[TR]
[TD]End Function
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]

</tbody>