VBA Collection Related Functions

VBA Collections have a number of gotchas: (1) Retreival of objects from the collection, versus values.
If we are retrieving an obect we must use the form Set Value = coll(Key) whereas to retrieve a value the form is Value = coll(Key) Using the value syntax when the member is an object or vice versa causes a runtime error, Which can be a problem where the member's type is not known ahead of time. Note that using this framework's CollectionTryGetItem() function supports a consistent retrieval method for both situations, and CollectionItemIsObject() identifies the appropriate retrieval method.

Public Sub CollectionAddOrUpdate(Key As String, ByRef Value As Variant, coll As Collection)

CollectionAddOrUpdate("one", 1, TestColl) CollectionAddOrUpdate("one", 2, TestColl) Debug.Print TestColl.Item("one) => False CollectionItemIsObject("db", TestColl) 2 => True

Public Function CollectionItemIsObject(Key As String, coll As Collection) As Boolean

TestColl.Add (1,"one") TestColl.Add (CurrentDb(),"db") CollectionItemIsObject("one", TestColl) => False CollectionItemIsObject("db", TestColl) => True

Public Function CollectionTryGetItem(Key As String, coll As Collection, ByRef Value As Variant) As Boolean

TestColl.Add (1,"one") TestColl.Add (2,"two") TestColl.Add (3,"three") KeyInDictionary("one", TestColl) => True KeyInDictionary("four", TestColl) => False

Public Function KeyInDictionary(Key As String, coll As Collection) As Boolean

TestColl.Add (1,"one") TestColl.Add (2,"two") TestColl.Add (3,"three") KeyInDictionary("one", TestColl) => True KeyInDictionary("four", TestColl) => False