StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POGet item in VBA Collection by Name
text
Body
copied!<p>Ahoy hoy,</p> <p>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 <code>invalid call or argument</code> error when I try to <code>Get</code> something by a string. Thanks in advance for even reading this too, any help is appreciated. <\edit></p> <p>Here's the collection:</p> <pre><code>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 </code></pre> <p>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: <code>Debug.Print myFlds.Item("Customer").colNbr</code></p> <p>And the object class just in case:</p> <pre><code>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 </code></pre> <p>And why not that function too:</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload