Check if a record exists in a VB6 collection?

Question

I've inherited a large VB6 app at my current workplace. I'm kinda learning VB6 on the job and there are a number of problems I'm having. The major issue at the moment is I can't figure out how to check if a key exists in a Collection object. Can anyone help?

Answer 1

I've always done it with a function like this:

public function keyExists(myCollection as collection, sKey as string) as Boolean
  on error goto handleerror:

  dim val as variant

  val = myCollection(sKey)
  keyExists = true
  exit sub
handleerror:
  keyExists = false
end function

Answer 2

@Mark Biek Your keyExists closely matches my standard Exists() function. To make the class more useful for COM-exposed collections and checking for numeric indexes, I'd recommend changing sKey and myCollection to not be typed. If the function is going to be used with a collection of objects, 'set' is required (on the line where val is set).

EDIT: It was bugging me that I've never noticed different requirements for an object-based and value-based Exists() function. I very rarely use collections for non-objects, but this seemed such a perfect bottleneck for a bug that would be so hard to track down when I needed to check for existence. Because error handling will fail if an error handler is already active, two functions are required to get a new error scope. Only the Exists() function need ever be called:

Public Function Exists(col, index) As Boolean
On Error GoTo ExistsTryNonObject
    Dim o As Object

    Set o = col(index)
    Exists = True
    Exit Function

ExistsTryNonObject:
    Exists = ExistsNonObject(col, index)
End Function

Private Function ExistsNonObject(col, index) As Boolean
On Error GoTo ExistsNonObjectErrorHandler
    Dim v As Variant

    v = col(index)
    ExistsNonObject = True
    Exit Function

ExistsNonObjectErrorHandler:
    ExistsNonObject = False
End Function

Public Sub TestExists()
    Dim c As New Collection

    Dim b As New Class1

    c.Add "a string", "a"
    c.Add b, "b"

    Debug.Print "a", Exists(c, "a") ' True '
    Debug.Print "b", Exists(c, "b") ' True '
    Debug.Print "c", Exists(c, "c") ' False '
    Debug.Print 1, Exists(c, 1) ' True '
    Debug.Print 2, Exists(c, 2) ' True '
    Debug.Print 3, Exists(c, 3) ' False '
End Sub

Answer 3

As pointed out by Thomas, you need to Set an object instead of Let. Here's a general function from my library that works for value and object types:

Public Function Exists(ByVal key As Variant, ByRef col As Collection) As Boolean

'Returns True if item with key exists in collection

On Error Resume Next

Const ERR_OBJECT_TYPE As Long = 438
Dim item As Variant

'Try reach item by key
item = col.item(key)

'If no error occurred, key exists
If Err.Number = 0 Then
    Exists = True

'In cases where error 438 is thrown, it is likely that
'the item does exist, but is an object that cannot be Let
ElseIf Err.Number = ERR_OBJECT_TYPE Then

    'Try reach object by key
    Set item = col.item(key)

    'If an object was found, the key exists
    If Not item Is Nothing Then
        Exists = True
    End If

End If

Err.Clear

End Function

As also advised by Thomas, you can change the Collection type to Object to generalize this. The .Item(key) syntax is shared by most collection classes, so that might actually be useful.

EDIT Seems like I was beaten to the punch somewhat by Thomas himself. However for easier reuse I personally prefer a single function with no private dependencies.

Answer 4

Using the error handler to catch cases when the key does not exists in the Collection can make debugging with "break on all errors" option quite annoying. To avoid unwanted errors I quite often create a class which has the stored objects in a Collection and all keys in a Dictionary. Dictionary has exists(key) -function so I can call that before trying to get an object from the collection. You can only store strings in a Dictionary, so a Collection is still needed if you need to store objects.

Answer 5

Better solution would be to write a TryGet function. A lot of the time you are going to be checking exists, and then getting the item. Save time by doing it at the same time.

public Function TryGet(key as string, col as collection) as Variant on error goto errhandler Set TryGet= col(key) exit function errhandler: Set TryGet = nothing
end function

Answer 6

see http://www.visualbasic.happycodings.com/Other/code10.html the implementation here has the advantage of also optionally returning the found element, and works with object/native types (according to the comments).

Answer 7

My standard function is very simple. This will work regardless of the element type, since it doesn't bother doing any assignment, it merely executes the collection property get.

Public Function Exists(ByVal oCol As Collection, ByVal vKey As Variant) As Boolean

    On Error Resume Next
    oCol.Item vKey
    Exists = (Err.Number = 0)
    Err.Clear

End Function