How to check the key is exists in collection or not

Question

I want to check collection variable contains the key or not in visual basic 6.0 Below is the collection variable I am having

pcolFields As Collection

and I want to check whether it contains the field Event_Code. I am doing this as below but it not worked for me.

    If IsMissing(pcolFields("Event_Code")) = False Then
        'Do Something
    End If

You may also get some help [here](http://stackoverflow.com/questions/40651/check-if-a-record-exists-in-a-vb6-collection). — Brian Hooper, Feb 17 '17 at 08:44
Possible duplicate of [Check if a record exists in a VB6 collection?](http://stackoverflow.com/questions/40651/check-if-a-record-exists-in-a-vb6-collection) — Brian Hooper, Feb 17 '17 at 08:45
For [fastest possible implementation](http://www.vbforums.com/showthread.php?822115-searching-through-a-collection) you'll need a custom made typelib with tweaked `ICollection` interface. — wqw, Feb 20 '17 at 11:18

score 3 · Answer 1 · answered Feb 17 '17 at 08:38

3

Here is an example solution with try-catch:

Private Function IsMissing(col As Collection, field As String)
On Error GoTo IsMissingError
    Dim val As Variant
    val = col(field)
    IsMissing = False
    Exit Function
IsMissingError:
    IsMissing = True
End Function

Use it like this:

Private Sub Form_Load()
    Dim x As New Collection
    x.Add "val1", "key1"

    Dim testkey As String
    testkey = "key2"
    If IsMissing(x, testkey) Then
        Debug.Print "Key is Missing"
    Else
        Debug.Print "Val is " + x(testkey)
    End If

    Exit Sub
End Sub

You could also try a to Implement or Subclass the Collection and add a "has" Function

answered Feb 17 '17 at 08:38

efkah

1,628
16
30

The Collection object is useful but primitive with no nice features such as you require. Basic solution is as per this answer - try to reference and use error detection as a try catch construct. The same is true of detecting if a member (record) is present. The Dictionary object is sometimes a better alternative, but if all you need is to answer the question then the try-catch pattern is the optimum solution. – Vanquished Wombat Feb 17 '17 at 12:01
1

Beware of the performance deathtraps of Scripting.Dictionary. Several of the members of this class extract and return arrays of the entire keys or items contents. These can be a killer unless the Count is fairly small. – Bob77 Feb 18 '17 at 00:29

Guillermo Phillips · Answer 2 · 2017-02-17T13:15:17.853

Collections are not useful if you need to check for existence, but they're useful for iteration. However, collections are sets of Variants and so are inherently slower than typed variables.

In nearly every case it's more useful (and more optimal) to use a typed array. If you need to have a keyed collection you should use the Dictionary object.

Some examples of general ways of using typed arrays:

Dim my_array() As Long ' Or whichever type you need
Dim my_array_size As Long
Dim index As Long
Dim position As Long

' Add new item (push)
ReDim Preserve my_array(my_array_size)
my_array(my_array_size) = 123456 ' something to add
my_array_size = my_array_size + 1

' Remove item (pop)
my_array_size = my_array_size - 1
If my_array_size > 0 Then
    ReDim Preserve my_array(my_array_size - 1)
Else
    Erase my_array
End If

' Remove item (any position)
position = 3 'item to remove
For index = position To my_array_size - 2
    my_array(index) = my_array(index + 1)
Next
my_array_size = my_array_size - 1
ReDim Preserve my_array(my_array_size - 1)

' Insert item (any position)
ReDim Preserve my_array(my_array_size)
my_array_size = my_array_size + 1
For index = my_array_size - 1 To position + 1 Step -1
    my_array(index) = my_array(index - 1)
Next
my_array(position) = 123456 ' something to insert

' Find item
For index = 0 To my_array_size - 1
    If my_array(index) = 123456 Then
        Exit For
    End If
Next
If index < my_array_size Then
    'found, position is in index
Else
    'not found
End If

Whilst it may seem like a lot code. It is way faster. Intellisense will also work, which is a bonus. The only caveat is if you have very large data sets, then redim starts to get slow and you have to use slightly different techniques.

You can also use a Dictionary, be sure to include the Microsoft Scripting Runtime reference in your project:

Dim dict As New Dictionary
Dim value As Long

dict.Add "somekey", 123456

dict.Remove "somekey"

value = dict.Item("somekey")

If dict.Exists("somekey") Then
    ' found!
Else
    ' not found
End If

Dictionaries like collections just hold a bunch of Variants, so can hold objects etc.

score 1 · Accepted Answer · answered Jun 22 '21 at 15:06

1

We can check following code into vb.net code

If Collection.ContainsKey(KeyString) Then

'write code

End if

Collection is variable of Dictionary and KeyString is a key string which we need to find into collection

answered Jun 22 '21 at 15:06

mahendra rajput

26
2

score 0 · Answer 4 · answered Dec 05 '18 at 13:32

The method from efkah will fail if the Collection contains objects rather than primitive types. Here is a small adjustment:

'Test if a key is available in a collection
Public Function HasKey(coll As Collection, strKey As String) As Boolean
    On Error GoTo IsMissingError
        Dim val As Variant
'        val = coll(strKey)
        HasKey = IsObject(coll(strKey))
        HasKey = True
        On Error GoTo 0
        Exit Function
IsMissingError:
        HasKey = False
        On Error GoTo 0
End Function

How to check the key is exists in collection or not

4 Answers4

Linked