按名称获取 VBA 集合中的项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19613506/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get item in VBA Collection by Name
提问by Bippy
Ahoy hoy,
嘿嘿，
I'm trying to do stuff to a custom object in a custom collection by referencing it's name property in VBA Excel. I swear it worked before (or at least didn't throw an error) and now its kaput. I'm getting an invalid call or argumenterror when I try to Getsomething by a string. Thanks in advance for even reading this too, any help is appreciated. <\edit>
我正在尝试通过在 VBA Excel 中引用它的 name 属性来对自定义集合中的自定义对象执行操作。我发誓它以前有效（或者至少没有抛出错误），现在它的 kaput 。invalid call or argument当我尝试Get通过字符串执行某些操作时出现错误。提前感谢您甚至阅读本文，任何帮助表示赞赏。<\编辑>
Here's the collection:
这是集合：
Option Explicit
Private DRAFields As New Collection
Sub Add(Name As String, Optional colNbr As Long, Optional Exists As Boolean)
Dim fld As New DRAFld
fld.colNbr = colNbr
fld.Name = Name
fld.Exists = Exists
DRAFields.Add fld
End Sub
Property Get Item(NameOrNumber As Variant)
Set Item = DRAFields(NameOrNumber) '<------- Error here
End Property
The collections has items added by passing an array of names in to a function and the collection is returned without issue. I can iterate over by using the key. But the error happens if get as such: Debug.Print myFlds.Item("Customer").colNbr
集合通过将名称数组传递给函数来添加项目，并且集合返回没有问题。我可以使用密钥进行迭代。但是如果得到这样的错误就会发生：Debug.Print myFlds.Item("Customer").colNbr
And the object class just in case:
和对象类以防万一：
Option Explicit
Private clmNbrPvt As Long
Private namePvt As String
Private existsPvt As Boolean
Public Property Get colNbr() As Long
colNbr = clmNbrPvt
End Property
Public Property Let colNbr(lngParam As Long)
clmNbrPvt = lngParam
End Property
Public Property Get Name() As String
Name = namePvt
End Property
Public Property Let Name(strParam As String)
namePvt = strParam
End Property
Public Property Get Exists() As Boolean
Exists = existsPvt
End Property
Public Property Let Exists(booParam As Boolean)
existsPvt = booParam
End Property
And why not that function too:
为什么不是这个功能：
Function validateAndBuildDRAFields(ByRef arrReqFields() As String, _
inputSheet As Worksheet, _
Optional VBAModule As String) As clsDRAFields
Dim lEndCol As Long: lEndCol = Standard.zGetLastColumn(inputSheet, 1)
Dim i As Long
Dim x As Long
Dim intExit As Long
Dim myDRAFields As New clsDRAFields
Set validateAndBuildDRAFields = myDRAFields
'Builds myDRAFields items from arrReqFields
For i = LBound(arrReqFields) To UBound(arrReqFields)
myDRAFields.Add arrReqFields(i)
Next i
'checks if required fields exist on input sheet
'if found then sets column number and exists = true
For i = 1 To myDRAFields.Count
For x = 1 To lEndCol
If inputSheet.Cells(1, x) = myDRAFields.Item(i).Name Then
myDRAFields.Item(i).colNbr = x
myDRAFields.Item(i).Exists = True
intExit = intExit + 1
Exit For
End If
Next x
If intExit = UBound(arrReqFields) + 1 Then Exit For
Next i
' tells user if there are any missing fields and ends if true
If (Not intExit = UBound(arrReqFields) + 1) Or _
intExit = 0 Then
For i = 1 To myDRAFields.Count
If myDRAFields.Item(i).Exists = False Then
Call Standard.TheEndWithError("I couldn't find the " & myDRAFields.Item(i).Name & _
" column in your file. Please add " & myDRAFields.Item(i).Name & _
" to your DRA Layout.", False, VBAModule)
End If
Next i
Set myDRAFields = Nothing
Standard.TheEnd
End If
End Function
回答by Dick Kusleika
To access a collection item by its key, you have to supply a key when you add the item to the collection. The key is optional. When you access a collection item with a string, the Item method assumes you want to match the key. When you use an integer, it assumes you want the positional index.
要通过键访问集合项，您必须在将项添加到集合时提供键。密钥是可选的。当您使用字符串访问集合项时，Item 方法假定您要匹配键。当您使用整数时，它假定您需要位置索引。
So, change the line in your Add method to
因此，将 Add 方法中的行更改为
DRAFields.Add fld, fld.Name
and you'll be able to access items by their Name property.
并且您将能够通过它们的 Name 属性访问项目。
