Create 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 can't figure out how to do it.

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 can probably tell, I don't know much about VBA or object oriented programming, I just have one task I'm trying to accomplish. The code above results in a compile error, so is obviously not the correct way to do this, is there a simple way to do what I'm trying for or not really?

You can't declare variables like that. `CPlayer` is a class? — findwindow, Jun 16 '16 at 20:33
Yeah, CPlayer is a Class with a Name Property. Is this impossible then? — Qiri, Jun 16 '16 at 20:34
You _might_ be able to create an array of classes. This is beyond me though XD — findwindow, Jun 16 '16 at 20:35
Dim Player&k can't be declared as such, "&" is a reserved character. — Sgdva, Jun 16 '16 at 21:27
Yeah, I didn't make it clear, what I wanted was a join, so that it would go: Player0 on k=0, Player1 on k=1, and so on. — Qiri, Jun 16 '16 at 21:35
Some friendly advice offered with the best of intentions: You are dealing with classes, arrays and (if you take the advise from below) collections. It may be time to put on the big-boy pants and stop using the [ActiveCell property](https://msdn.microsoft.com/en-us/library/office/ff193314.aspx) and [.Offset](https://msdn.microsoft.com/en-us/library/office/ff840060.aspx). Read through [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251) and take the training wheels off. — , Jun 16 '16 at 22:37

score 3 · Accepted Answer · answered Jun 16 '16 at 22:05

Try this. Starting with k=0 will mess up 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

score 1 · Answer 2 · answered Jun 16 '16 at 22:29

I would avoid an array of class object and use a collection instead. Those could be deemed very close to the same thing in some circles but there are essential differences like not having to ReDim to expand the collection.

CPlayer class

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 code sheet

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's name to the Immediate window
        Debug.Print Players(2).Name
        Debug.Print Players(3).Name

    End With
End Sub

So that builds a collection of your players and offers two methods of retrieving the .Name property.

The answer proposed by @Fredrik is essentially the same as above but offers a different method of applying the name from the worksheet into the collection. — , Jun 16 '16 at 22:33
And he beat you by 24min. Just lost _all_ my respect for you =P — findwindow, Jun 16 '16 at 23:01
It takes a few minutes to build a class and rewrite a sub procedure as well as sample data (after waiting for the OP to respond to a comment asking for clarification). :( — , Jun 16 '16 at 23:03
Oh, you built the class so you can test it. Fine, I guess you can get _some_ respect back. — findwindow, Jun 16 '16 at 23:04

Create New Object For Each Cell

2 Answers2