0 votes
in VBA by Expert (911 points)

The VBA Collection object tutorial notes there is no KeyExists method and describes that as "one of the main reasons" to prefer the Dictionary object. But the Collection object is "a default class" so it might be more convenient than the Dictionary object for a simple associative array. Is there any way to add a Collection object KeyExists method?

1 Answer

0 votes
by Expert (911 points)
edited by
 
Best answer

Try this function, which is in the M_Support module of My Excel Toolbox:

Public Function CollectionKey_Exists(C As Collection, Key As Variant) As Boolean
'
' Returns True if Key exists in Collection C; else, returns False
'
    On Error GoTo ErrorHandler
    C.Item Index:=Key
    CollectionKey_Exists = True
    Exit Function

ErrorHandler:
    On Error GoTo 0
    Err.Clear
    CollectionKey_Exists = False

End Function

 

by Expert (911 points)

I made some minor changes to the code in my previous answer to make it less obscure and improve error handling. Also, I discovered Chip Pearson's similar KeyExistsInCollection function, which does not work properly for a Collection of Objects.

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...