Excel VBA 编译错误参数 循环访问附加到类集合的数组时不可选

Question

我在循环访问附加到类的团队/组数组时不断收到错误"编译错误：参数不是可选的" 存储在集合中的事件

它在行上给出编译错误，因为它没有将对象视为数组：

For ctr = LBound(evt.Team) To UBound(evt.Team)
For ctr = LBound(evt.Group) To UBound(evt.Group)

但是当我直接打印它引用索引时，它可以工作......像这行：

Debug.Print evt.Team(0)
Debug.Print evt.Group(1)

我的要求很简单。

1. 从 Excel 数据中读取数据并将每个事件存储在集合中以供进一步处理
团队
2. 和组数组被添加到类事件中，这些事件仅存储团队详细信息及其各自的适用(勾选为"Y"(组

到目前为止，这工作正常，数据正确存储在事件集合中。

当我尝试迭代集合并尝试循环访问团队和组数组时，会出现问题。

事件模块代码

Option Explicit
Dim ws As Excel.Worksheet
Dim colEvents As Collection
Dim hdrRow1, hdrRow2, startRow, startCol, lastRow, lastCol
Dim startTeam, endTeam, startGroup, endGroup
Dim ctr, ctrRow, ctrCol
Sub Main()
Set ws = ThisWorkbook.Sheets("Events")
Set colEvents = New Collection
With ws
lastRow = (.UsedRange.Rows.Count)
lastCol = (.UsedRange.Columns.Count)
'Debug.Print "Last Row: " & lastRow & " - " & "Last Column: " & lastCol
hdrRow1 = 1
hdrRow2 = 2
startRow = 3
startCol = 1
startTeam = 4
endTeam = 6
startGroup = 7
endGroup = 11
'Get Groups
For ctrRow = startRow To lastRow
Dim oEvent As clsEvent
Set oEvent = New clsEvent
'Get No, Name, Type
oEvent.No = .Cells(ctrRow, startCol)
oEvent.Name = .Cells(ctrRow, startCol + 1)
oEvent.EType = .Cells(ctrRow, startCol + 2)
'Get Team Details
ctr = 0
For ctrCol = startTeam To endTeam
oEvent.Team(ctr) = .Cells(ctrRow, ctrCol).Value
ctr = ctr + 1
Next
'Get Group Details
ctr = 0
For ctrCol = startGroup To endGroup
If (.Cells(ctrRow, ctrCol).Value = "Y") Then
oEvent.Group(ctr) = .Cells(hdrRow2, ctrCol).Value
ctr = ctr + 1
End If
Next
colEvents.Add oEvent
Next
End With
'Check Collection
Dim evt As clsEvent
For Each evt In colEvents
Debug.Print "No: " & evt.No
Debug.Print "Name: " & evt.Name
Debug.Print "Type: " & evt.EType
'Loop through Team array
Debug.Print "Team Details: "
For ctr = LBound(evt.Team) To UBound(evt.Team)
Debug.Print evt.Team(ctr)
Next
'Loop through Group array
Debug.Print "Group Details"
For ctr = LBound(evt.Group) To UBound(evt.Group)
Debug.Print evt.Group(ctr)
Next
Next
End Sub

类模块事件

Option Explicit
Private pNo As Integer
Private pName As String
Private pEType As String
Private pTeam(2) As Integer
Private pGroup() As String
'Prop No
Public Property Get No() As Integer
No = pNo
End Property
Public Property Let No(ByVal vNewValue As Integer)
pNo = vNewValue
End Property
'Prop Events
Public Property Get Name() As String
Name = pName
End Property
Public Property Let Name(ByVal vNewValue As String)
pName = vNewValue
End Property
'Prop Event Type
Public Property Get EType() As String
EType = pEType
End Property
Public Property Let EType(ByVal vNewValue As String)
pEType = vNewValue
End Property
'Prop Type
Public Property Get Team(ByVal index As Long) As Integer
Team = pTeam(index)
End Property
Public Property Let Team(ByVal index As Long, ByVal vNewValue As Integer)
pTeam(index) = vNewValue
End Property
'Prop Group
Public Property Get Group(ByVal index As Long) As String
Group = pGroup(index)
End Property
Public Property Let Group(ByVal index As Long, ByVal vNewValue As String)
If (Not pGroup) = -1 Then
ReDim Preserve pGroup(0)
End If
If (index > UBound(pGroup)) Then ReDim Preserve pGroup(index)
pGroup(index) = vNewValue
End Property
Private Sub Class_Initialize()
'statements
End Sub
Private Sub Class_Terminate()
'statements
End Sub

Answer 1

Team

不是一个数组，这就是为什么VBA不把它当作一个数组。

Team(和Group(是索引属性。他们抽象化了封装数据存储在数组中的事实。对于调用代码，数据也可以存储在Collection、Dictionary、ArrayList、HashSet中。它根本没有区别：给定一个索引，该属性能够检索和返回一个项目。

通常，在自定义集合类上公开索引属性 - 并公开此类属性以及其他成员，如Count、Add和Clear。

但这不是自定义集合类。

因此，解决方案可能是公开TeamCount和GroupCount属性。请注意，公开UBound(pTeam)或UBound(pGroup)会使您的减法变得泄漏，如果您后来决定宁愿有一个Collection来保存封装的数据，则可能会导致问题。

Public Property Get TeamCount() As Long
TeamCount = UBound(pTeam) + 1 '<~ array is zero-based, so count is +1
End Property
Public Property Get GroupCount() As Long
If UBound(pGroup) >= 0 Then '<~ would be -1 if uninitialized
GroupCount = UBound(pGroup) + 1
End If
End Property

然后你可以做：

For ctr = 0 To evt.TeamCount - 1

请注意，这仍然是一个泄漏的抽象：封装数组的零开始的性质到处都在流血。

更好的抽象将使它能够工作：

For Each t In evt.Teams

有几种方法可以实现这一点 - 这是最简单(也可能是性能最低的方法(：

Public Property Get Teams() As Collection
Dim result As Collection
Set result = New Collection
Dim i As Long
For i = LBound(pTeams) To UBound(pTeams)
result.Add pTeams(i)
Next
Set Teams = result
End Property