Collection object fails within class

F

FP1

I'm creating a wrapper class for a collection object, mainly so I can
test whether a given records exists. I'm baffled as to why I'm
getting "Invalid procedure or argument" in the exists function when I
try to extract the value, whether it actually exists or not. I've
tried collection.item and collection(item), same result. BTW, the
values are numbers that I convert to strings.

Option Compare Database
Option Explicit
'this class object extends the collection object to include an
'exists' boolean function

Private colStatus As VBA.Collection


Private Sub Class_Initialize()
Set colStatus = New Collection
End Sub
Public Sub add(item)
If exists(item) = False Then
colStatus.add (CStr(item))
End If
End Sub

Public Function count()
count = colStatus.count
End Function

Public Sub remove(item)
If exists(item) = True Then
colStatus.RemoveItem (CStr(item))
End If
End Sub

Function exists(item) As Boolean
On Error GoTo does_not_exist
Dim value As String
Dim strItem
strItem = CStr(item)
value = colStatus.item(strItem)
exists = True
Exit Function
does_not_exist:
exists = False
On Error Resume Next
End Function
 
D

Douglas J. Steele

I'd recommend renaming the method: Exists is a reserved word (although I
will acknowledge that it should only be reserved by JET, SQL Server and
ODBC)

You could try setting a key: strictly changing a number to a string using
the CStr function isn't actually sufficient for VBA in many cases.

Option Compare Database
Option Explicit
'this class object extends the collection object to include an
'exists' boolean function

Private colStatus As VBA.Collection

Private Sub Class_Initialize()
Set colStatus = New Collection
End Sub

Public Sub add(item)
If exists(item) = False Then
colStatus.add (CStr(item)), "Key-" & item
End If
End Sub

Public Function count()
count = colStatus.count
End Function

Public Sub remove(item)
If exists(item) = True Then
colStatus.RemoveItem "Key-" & item
End If
End Sub

Function exists(item) As Boolean
On Error GoTo does_not_exist
Dim value As String
Dim strItem
strItem = "Key-" & item
value = colStatus.item(strItem)
exists = True
Exit Function
does_not_exist:
exists = False
On Error Resume Next
End Function
 
F

FP1

In addition to Douglas' comments:

I'd think about renaming your "Item" variables as well, since
Collections have a method with that name and it is not at all clear
what value/object has been assigned to the variable you've given the
same name too. Not sure if its a reserved word, but I'd treat it like
one myself.
Public Sub add(sItem as String), etc.
would go a long way to making Type-mismatch errors call attention to
themselves.

Also:
does_not_exist:
exists = False
On Error Resume Next
Click to expand...
Why Resume Next rather than Exit Function? How will your function ever
return False (once its working)?

HTH,


FP1 said:
I'm creating a wrapper class for a collection object, mainly so I can
test whether a given records exists. I'm baffled as to why I'm
getting "Invalid procedure or argument" in the exists function when I
try to extract the value, whether it actually exists or not. I've
tried collection.item and collection(item), same result. BTW, the
values are numbers that I convert to strings.

Option Compare Database
Option Explicit
'this class object extends the collection object to include an
'exists' boolean function

Private colStatus As VBA.Collection


Private Sub Class_Initialize()
Set colStatus = New Collection
End Sub
Public Sub add(item)
If exists(item) = False Then
colStatus.add (CStr(item))
End If
End Sub

Public Function count()
count = colStatus.count
End Function

Public Sub remove(item)
If exists(item) = True Then
colStatus.RemoveItem (CStr(item))
End If
End Sub

Function exists(item) As Boolean
On Error GoTo does_not_exist
Dim value As String
Dim strItem
strItem = CStr(item)
value = colStatus.item(strItem)
exists = True
Exit Function
does_not_exist:
exists = False
On Error Resume Next
End Function
Click to expand...

Thanks, guys. I'll try those. It occurred to me briefly that a name
conflict might 'exist' but I sort of dismissed it. I'll try it.
Click to expand...
 
G

George Nicholson

In addition to Douglas' comments:

I'd think about renaming your "Item" variables as well, since Collections
have a method with that name and it is not at all clear what value/object
has been assigned to the variable you've given the same name too. Not sure
if its a reserved word, but I'd treat it like one myself.
Public Sub add(sItem as String), etc.
would go a long way to making Type-mismatch errors call attention to
themselves.

Also:
does_not_exist:
exists = False
On Error Resume Next
Click to expand...
Why Resume Next rather than Exit Function? How will your function ever
return False (once its working)?

HTH,
 
F

FP1

For a good discussion of what names to avoid, see what Allen Browne
has at http://www.allenbrowne.com/AppIssueBadWord.html
Click to expand...

Thanks. Though I've now determined it isn't a name. Still a mystery.
Any other ideas? This is the new version.


Private colStatus As Collection


Private Sub Class_Initialize()
Set colStatus = New Collection
End Sub

Public Sub addIt(MyKey, Optional itemValue)
If ItExists(MyKey) = False Then
If Not IsNull(itemValue) Then
colStatus.add MyKey, itemValue
Else
colStatus.add MyKey
End If
End If
End Sub

Public Function countIt()
countIt = colStatus.count
End Function

Public Sub removeIt(MyKey)
If ItExists(MyKey) = True Then
colStatus.remove MyKey
End If
End Sub

Function ItExists(MyKey) As Boolean
On Error GoTo does_not_exist
Dim MyValue As String
MyValue = colStatus.item(MyKey) 'error will trip if not present
ItExists = True
Exit Function

does_not_exist:
ItExists = False
On Error Resume Next
End Function
 
G

George Nicholson

colStatus.add MyKey, itemValue

This is backwards. For collections, the item-to-add is the 1st argument (and
I don't think its optional), Key is the *2nd* argument. ItExists has simply
been looking for a key value that was never added to the collection. I would
have expected a different error message, but it seems to work if the
following change is made:

colStatus.add itemValue, MyKey

HTH,
 
F

FP1

colStatus.add MyKey, itemValue
Click to expand...

This is backwards. For collections, the item-to-add is the 1st
argument (and I don't think its optional), Key is the *2nd* argument.
ItExists has simply been looking for a key value that was never added
to the collection. I would have expected a different error message,
but it seems to work if the following change is made:

colStatus.add itemValue, MyKey

HTH,


FP1 said:
Thanks. Though I've now determined it isn't a name. Still a
mystery. Any other ideas? This is the new version.


Private colStatus As Collection


Private Sub Class_Initialize()
Set colStatus = New Collection
End Sub

Public Sub addIt(MyKey, Optional itemValue)
If ItExists(MyKey) = False Then
If Not IsNull(itemValue) Then
colStatus.add MyKey, itemValue
Else
colStatus.add MyKey
End If
End If
End Sub

Public Function countIt()
countIt = colStatus.count
End Function

Public Sub removeIt(MyKey)
If ItExists(MyKey) = True Then
colStatus.remove MyKey
End If
End Sub

Function ItExists(MyKey) As Boolean
On Error GoTo does_not_exist
Dim MyValue As String
MyValue = colStatus.item(MyKey) 'error will trip if not present
ItExists = True
Exit Function

does_not_exist:
ItExists = False
On Error Resume Next
End Function
Click to expand...

You're right about the key. I guess you can't trust the help
documentation, which says it's reverse, and also says the second
parameter is optional. Anyway, thanks!
Click to expand...
 
G

George Nicholson

We have different documentation, evidently.

FWIW, the arguments are reversed when using Add with the Dictionary object
(key, item w/Dictionary. item, key w/Collection). This could easily be the
source of some confusion (I know its why it took me so long to spot the
problem...)
and also says the second parameter is optional
Click to expand...
With collections, the 2nd (key) argument *is* optional. But if you don't
supply it, you will only be able to use an item's positional index when
referencing it.

HTH,


 
F

FP1

We have different documentation, evidently.

FWIW, the arguments are reversed when using Add with the Dictionary
object (key, item w/Dictionary. item, key w/Collection). This could
easily be the source of some confusion (I know its why it took me so
long to spot the problem...)
and also says the second parameter is optional
Click to expand...
With collections, the 2nd (key) argument *is* optional. But if you
don't supply it, you will only be able to use an item's positional
index when referencing it.

HTH,


FP1 said:
You're right about the key. I guess you can't trust the help
documentation, which says it's reverse, and also says the second
parameter is optional. Anyway, thanks!
Click to expand...

I was using Dictionary as a substitute, but it's not included in
standard VBA, so I wanted to use collection. I ended up using named
parameters just to be sure!
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why is collection failing? 0
ItemAdd and FolderAdd events problem with Public Folders 0
Cannot reference object in custom collection 1
Collection Object 12
Creating a function that returns a collection... 1
How can I use a collection inside a class? 7
Create a collection of custom classes in another custom class 3
Check to see if object key is in collection 2

Top