Create a new object for each cell

Question

I have a long list in an Excel spreadsheet, and for each cell I would like to create a new object, but I cannot figure out how to do this.

I have something like:

Dim k As Integer
k = 0

Do

     If ActiveCell.Offset(k, 0).Value = "" Then Exit Do
     Dim Player&k As New CPlayer   

      'Attempting to create a new object with name Player0, Player1, ...

     Set Player&k.Name = ActiveCell.Offset(k, 0).Value
     k = k + 1
Loop

As you probably can say, I know little about VBA or object-oriented programming, I have only one task that I am trying to accomplish. The above code leads to a compilation error, so obviously this is not the right way to do this, is there an easy way to do what I'm trying or not really?

Answer 1

Try it. Starting with k = 0 will ruin things. Changed so that it starts with 1.

Dim Players As Collection
Set Players = New Collection

Dim Player As CPlayer

Dim k As Integer
k = 1

Do
    If ActiveCell.Offset(k-1, 0).Value = "" Then Exit Do
    Set Player = New CPlayer
    Players.Add Player

    Players(k).Name = ActiveCell.Offset(k-1, 0).Value
    k = k + 1
Loop

Answer 2

. , , ReDim .

CPlayer

Option Explicit

Private pName As String

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

Module1

Option Explicit

Sub playerNames()
    Dim Players As Collection
    Dim player As CPlayer, k As Long

    Set Players = New Collection

    With ActiveSheet  'this would be better as something like With Worksheets("Sheet1")
        For k = 2 To .Cells(Rows.Count, "F").End(xlUp).Row
            If CBool(Len(.Cells(k, "F").Value2)) Then
                Set player = New CPlayer
                player.Name = .Cells(k, "F").Value2
                Players.Add player
            End If
        Next k

        'enumerate the payer names to the Immediate window
        For Each player In Players
            Debug.Print player.Name
        Next player

        'send the second and third player name to the Immediate window
        Debug.Print Players(2).Name
        Debug.Print Players(3).Name

    End With
End Sub

, .Name.