Error codes to be expected from the VBA.Collection class?

[mdlueck]

Greetings,

I have found, today, very odd errors in code dealing with the VBA.Collection object.

A friend has proposed that perhaps VBA returns a different error when a particular entry was not found in a completely empty VBA.Collection object verses a VBA.Collection having one or more objects inside it.

I have based my use of custom collection classes on this article:
http://www.databaseadvisors.com/new...ng custom collections in microsoft access.asp

And per the author's leading, I have associated Err 5 / "Invalid Procedure Call Or Argument" with the object not being in the collection.

Today, much to my horror, I see after one object has been placed into the VBA.Collection, the error being returned an Err 9 / "Subscript Out Of Range" instead of the anticipated 5.

So, is this the expected behavior for VBA.Collection classes?

Error coming from this Exists method:

'Returns true if the key value or index exists in the collection
Function Exists(ByVal vntIndex As Variant) As Boolean
On Error GoTo Err_Exists

  Dim objTemp As clsObjXMLFASMessagesItem

  Set objTemp = m_PrivateCollection.Item(vntIndex)
  Exists = True

Exit_Exists:
  Set objTemp = Nothing
  Exit Function

Err_Exists:
  If Err.Number <> 5 Then
    'Further error handling here for other error types
    Call errorhandler_Logger("Class: " & TypeName(Me) & ", Function: Exists()")
  End If
  Exists = False
  Resume Exit_Exists

End Function

Screen capture of the VBA Editor / Debugger / Watches window with Err 5 / "Invalid Procedure Call Or Argument" and completely empty VBA.Collection:

Screen capture of the VBA Editor / Debugger / Watches window with Err 9 / "Subscript Out Of Range" and NOT completely empty VBA.Collection:

 

[gemma-the-husky]

Michael - maybe it depends how you are searching for an item

if you are iterating all the objects until you read past the last item, then you will get that error, I suppose.

is it anything to do with the collection being zero-baseed?

ie, if you have one item it will be collection(0)

if this is the problem, you should get the same error if you try and read the last item of the collection, however many you have.

I don't use collections much, so I this is just conjecture.

----
out of interest, why is the index argument a variant, and not a long or integer

 

[mdlueck]

Well, in another FE DB of this application, I captured the usual Err 5 / "Invalid Procedure Call Or Argument" that I was expecting.

However, this class I did not update with my discovery that I am able to search the VBA.Collection objects based on both the Integer ID the object is stored at OR the String Key value assigned to the object when it was added to the VBA.Collection.

Perhaps could "String verses Variant" variable containing that which the VBA.Collection is being asked "Exists?" of make the difference?

 

[mdlueck]

maybe it depends how you are searching for an item

if you are iterating all the objects until you read past the last item, then you will get that error, I suppose.

m_PrivateCollection is the actual name of the bare VBA.Collection object. So I am using the Item method of that VBA.Collection class to go straight for the particular entry in the VBA.Collection to find out if it exists in the VBA.Collection or not. No loop / iteration code involved.

is it anything to do with the collection being zero-baseed?

ie, if you have one item it will be collection(0)

I never defined what the counting should be based on.

[B][COLOR=Blue]Private m_PrivateCollection As VBA.Collection[/COLOR][/B]

Private Sub Class_Initialize()
On Error GoTo Err_Initialize

  [B][COLOR=Blue]Set m_PrivateCollection = New VBA.Collection[/COLOR][/B]

Exit_Initialize:
  Exit Sub

Err_Initialize:
  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Subroutine: Class_Initialize()")
  'Disable further error handling. Since the code was already handling an error, if we raised the error without first
  'turning it off, that would result in an "Automation Error" aka Double Trap
  On Error GoTo 0
  'Raise the error to the caller program
  Err.Raise Number:=vbObjectError + 1, _
            Source:="Class: " & TypeName(Me) & ", Subroutine: Class_Initialize()", _
            Description:="Failed to Class_Initialize() class instance"
  Resume Exit_Initialize

End Sub

Private Sub Class_Terminate()
On Error GoTo Err_Terminate

  [B][COLOR=Blue]Set m_PrivateCollection = Nothing[/COLOR][/B]

Exit_Terminate:
  Exit Sub

Err_Terminate:
  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Subroutine: Class_Terminate()")
  Resume Exit_Terminate

End Sub

I only reacently realized that with VBA.Collection objects there are TWO ways to get an object back out of the collection:

1) Knowing the desired object's Key
2) Knowing the Long Integer assigned to the object by the VBA.Collection

out of interest, why is the index argument a variant, and not a long or integer

Because of the above recent discovery...

 

[mdlueck]

To test if the Err 9 / "Subscript Out Of Range" conditon might have been caused by my recent change from String to Variant variable type, I converted the working class to Variant. Sure enough, Err 5 the first time an object is Added to the VBA.Collection, and each subsequent call to Add I saw Err 9.

I put the code back to String variables, all Err 5's.

I put the code back to Variant variables, Err 5 when the VBA.Collection is empty, and Err 9 after that.

I guess I am taking "Tour de Code" and updating the error handler to allow both Err 5 or Err 9 to be soft ignored.

How the original code worked for the articles author is beyond me...

Updating my custom collection classes as follows:

'Returns true if the key value or index exists in the collection
Function Exists(ByVal vntIndex As Variant) As Boolean
On Error GoTo Err_Exists

  Dim objTemp As clsObjXMLFASMessagesItem

  Set objTemp = m_PrivateCollection.Item(vntIndex)
  Exists = True

Exit_Exists:
  Set objTemp = Nothing
  Exit Function

Err_Exists:
  [B][COLOR=Blue]If Not ((Err.Number = 5) Or (Err.Number = 9)) Then[/COLOR][/B]
    'Further error handling here for other error types
    Call errorhandler_Logger("Class: " & TypeName(Me) & ", Function: Exists()")
  End If
  Exists = False
  Resume Exit_Exists

End Function

 

[ChrisO]

Too much stuff; condensed from the posts…

You appear to have two classes:-
clsObjXMLFASMessagesItems
and
clsObjXMLFASMessagesItem

It also appears that clsObjXMLFASMessagesItems inherits clsObjXMLFASMessagesItem.

It also appears that in class clsObjXMLFASMessagesItem there is a Private Collection named m_PrivateCollection.

In a standard module:-

Option Compare Database
Option Explicit


Sub TestIt()
    Dim objTemp As New clsObjXMLFASMessagesItems

    MsgBox objTemp.Exists("Sam")

End Sub

In Class clsObjXMLFASMessagesItems:-

Option Compare Database
Option Explicit

Private objTemp As New clsObjXMLFASMessagesItem


Function Exists(ByVal vntIndex As Variant) As Boolean

    Exists = objTemp.Exists(vntIndex)
  
End Function

In Class clsObjXMLFASMessagesItem:-

Option Compare Database
Option Explicit

Private m_PrivateCollection As VBA.Collection
'

Private Sub Class_Initialize()

    Set m_PrivateCollection = New VBA.Collection
    m_PrivateCollection.Add "Fred", "Sam"

End Sub

Function Exists(ByVal vntIndex As Variant) As Boolean
    Dim vntItem As Variant

    On Error Resume Next
        vntItem = m_PrivateCollection.Item(vntIndex)
        Exists = IIf((Err.Number), False, True)
    Err.Clear

End Function

Chris.

 

[mdlueck]

Too much stuff; condensed from the posts…

sssiiiggghhh... I referenced where I read / found what I implemented. Here it is again...

I have based my use of custom collection classes on this article:
http://www.databaseadvisors.com/new...ng custom collections in microsoft access.asp

You appear to have two classes:-
clsObjXMLFASMessagesItems
and
clsObjXMLFASMessagesItem

This is correct.

It also appears that clsObjXMLFASMessagesItems inherits clsObjXMLFASMessagesItem.

No, the Items class is a custom collection containing instances of the Item class.

It also appears that in class clsObjXMLFASMessagesItem there is a Private Collection named m_PrivateCollection.

The collection is inside the Items class, not the Item class.

 

[ChrisO]

I do not believe in gurus so I don’t care about that link.

From me:-
>>It also appears that clsObjXMLFASMessagesItems inherits clsObjXMLFASMessagesItem.<<
From you:-
>>No, the Items class is a custom collection containing instances of the Item class.<<
How do those two statements differ?

Because of the difference between the post times of post #6 and post #7 I very much doubt that you have had the time to reproduce a working example of what I wrote in post #6. 28 minutes to reproduce #6 in a new database, test and comprehend it is too short.

>>The collection is inside the Items class, not the Item class.<<
Why is it? That might be the problem. It appears to me that the Items (Plural) should inherit the Item (Singular). You should be aware that Classes can inherit from another Class. The Plural can inherit from the Singular. The Children can inherit from the Parent. A Child can inherit from more than one Parent. If it does then that Parent is still singular. The Parent may inherit from its Parent but that Child still inherits from its Singular Parent which may, in turn, incorporate inheritance from its Parent.

So the Child, by referencing a singular Parent, can also inherit from the Parent.Parent.

28 minutes is not enough time to learn this stuff.

Chris.

 

[mdlueck]

I do not believe in gurus so I don’t care about that link.

Your loss.

Because of the difference between the post times of post #6 and post #7 I very much doubt that you have had the time to reproduce a working example of what I wrote in post #6. 28 minutes to reproduce #6 in a new database, test and comprehend it is too short.

I already posted the 1 LOC fix solution, in #5. So this case is closed.

I found it very interesting that the core VBA.Collection class returns different return codes based on what type of variable is used to indicate the desired index location in the VBA.Collection. Others should take note / be aware.

 

[stopher]

You should be aware that Classes can inherit from another Class. The Plural can inherit from the Singular. The Children can inherit from the Parent. A Child can inherit from more than one Parent. If it does then that Parent is still singular. The Parent may inherit from its Parent but that Child still inherits from its Singular Parent which may, in turn, incorporate inheritance from its Parent.

This is not the case in VBA afaik. VBA does not support inheritance an is one of the reasons why it is not a proper OO language. The nearest VBA gets is Implements but that only inherits the interface and nothing else. In any case, I'm struggling to see how inheritance helps here.

@Chris
Regarding your code in post #6, I'm struggling to see how this supports the OPs requirement. Sure it provides a way to test for existence in a collection via a parent but the whole point of the OPs question is based around the model of an encapsulated collection of objects. Or am I missing something? I'm sure you are on to something, I'm just not seeing it.

@Michael
I think my preferred approach would be to simply iterate the collection since that's what collections are good at. Catching errors is a last resort imho.

One other thing to consider is the Dictionary class since is comes with the Exists method and other benefits. You need to reference the Microsoft Scripting Runtime to employ dictionaries.
See:
http://msdn.microsoft.com/en-us/library/office/aa164502(v=office.10).aspx
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html

Note that a dictionary is not indexed so only accessed via a key.

I appreciate you already have a solution so I write this for further interest only.

Chris

 

[mdlueck]

@Michael
I think my preferred approach would be to simply iterate the collection since that's what collections are good at. Catching errors is a last resort imho.

In my case, I need to be able to access objects in the "container" directly, not having to iterate through the "container" to get to the object I am looking for.

One other thing to consider is the Dictionary class since is comes with the Exists method and other benefits. You need to reference the Microsoft Scripting Runtime to employ dictionaries.
See:
http://msdn.microsoft.com/en-us/library/office/aa164502(v=office.10).aspx
http://www.experts-exchange.com/Sof...A_3391-Using-the-Dictionary-Class-in-VBA.html

Note that a dictionary is not indexed so only accessed via a key.

I appreciate you already have a solution so I write this for further interest only.

Thank you for the suggestion, Chris. Noted for future reference.