为每个单元格创建新对象

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.

我在excel电子表格中有一个很长的列表，对于每个单元格我想创建一个新对象，但我无法弄清楚如何做到这一点。

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?

您可能会说，我对VBA或面向对象编程知之甚少，我只想完成一项任务。上面的代码会导致编译错误，所以显然不是正确的方法，有没有一种简单的方法来做我正在尝试或不做的事情？

2 个解决方案

#1

Try this. Starting with k=0 will mess up things. Changed so that it starts with 1.

尝试这个。从k = 0开始会搞砸事情。更改为以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

#2

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.

我会避免使用类对象数组而是使用集合。在某些圈子里，这些可能被视为非常接近同一件事，但是有一些本质区别，比如没有ReDim来扩展收藏。

CPlayer class

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

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'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.

这样就构建了一个玩家集合，并提供了两种检索.Name属性的方法。

#1