Missing function for OData and Office


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
      End If
    Next
  Next
    
  Set objResult = New Collection
  
  ' Put all keys in a collection.
  For Each objKey In objKeys
    objResult.Add objKey
  Next
  
  Set GetDistinctKeys = objResult
End Function

Thanks to Matt Chappel of www.coretech.net for spotting this one, and enjoy!

Comments (0)

Skip to main content