CODEWITHSUNDEEP
vbaexcelcollections
Bippy
Bippy

Reputation: 95

Get item in VBA Collection by Name

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 argument error when I try to Get something by a string. Thanks in advance for even reading this too, any help is appreciated. <\edit>

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

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

Upvotes: 3

Views: 23414

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

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.

So, change the line in your Add method to

DRAFields.Add fld, fld.Name

and you'll be able to access items by their Name property.

Upvotes: 10

Related Questions