The last entry of Consuming OData with Office VBA series had a small glitch – one of the helper functions is missing. The code isn’t too tricky to write, but it’s a handy little thing to have in your VBA toolkit. It simply takes a collection of dictionaries, and returns a new collection with all the keys, with duplicates removed.
Function GetDistinctKeys(ByVal objCollection As Collection) As Collection
Dim objResult As Collection
Dim objDictionary As Dictionary
Dim objKeys As Dictionary
Dim objKey As Variant
‘ Gather each key.
Set objKeys = New Dictionary
For Each objDictionary In objCollection
For Each objKey In objDictionary.Keys
If Not objKeys.Exists(objKey) Then
objKeys.Add objKey, Nothing
Set objResult = New Collection
‘ Put all keys in a collection.
For Each objKey In objKeys
Set GetDistinctKeys = objResult
Thanks to Matt Chappel of www.coretech.net for spotting this one, and enjoy!