VBA - .NET Interop - System.Collections.SortedList sample code

In the previous post I showed how to call .NET reflection from VBA to get a list of methods of the .NET System.Collections.* classes. I wanted the method list because I felt deprived without the VBA Intellisense. Once I had the SortedList's class's method list (included in listing) I could start to play around and write some sample code against the SortedList class. Enjoy!

I'd like to stop using Scripting.Dictionary (Microsoft Scripting Runtime) and the System.Collections.SortedList pretty much does everything I need so far. I can get the Keys and Values (aka Items) into an array by writing a helper function which itself uses an System.Collections.ArrayList. In addition the System.Collections.SortedList has extra methods such as ContainsValue, IndexOfKey, IndexofValue which give it more features than a Scripting.Dictionary. Thumbs up for this.

Sample System.Collections.SortedList code

Option Explicit

'                    Void Add(?,?)
'                    Void Clear()
'                  Object Clone()
'                 Boolean Contains(?)
'                 Boolean ContainsKey(?)
'                 Boolean ContainsValue(?)
'                    Void CopyTo(?,?)
'                 Boolean Equals(?)
'                   Int32 get_Capacity()
'                   Int32 get_Count()
'                 Boolean get_IsFixedSize()
'                 Boolean get_IsReadOnly()
'                 Boolean get_IsSynchronized()
'                  Object get_Item(?)
'             ICollection get_Keys()
'                  Object get_SyncRoot()
'             ICollection get_Values()
'                  Object GetByIndex(?)
'   IDictionaryEnumerator GetEnumerator()
'                   Int32 GetHashCode()
'                  Object GetKey(?)
'                   IList GetKeyList()
'                    Type GetType()
'                   IList GetValueList()
'                   Int32 IndexOfKey(?)
'                   Int32 IndexOfValue(?)
'                    Void Remove(?)
'                    Void RemoveAt(?)
'                    Void set_Capacity(?)
'                    Void set_Item(?,?)
'                    Void SetByIndex(?,?)
'              SortedList Synchronized(?)
'                  String ToString()
'                    Void TrimToSize()

Sub Test()

    Dim sortedList As Object  '* this tracks uniqueness
    Set sortedList = CreateObject("System.Collections.SortedList")
    
    '*
    '* load up the sorted list just like a dictionary
    '*
    sortedList.Add "Red", "FF0000"
    sortedList.Add "Green", "00FF00"
    sortedList.Add "Blue", "0000FF"

    '*
    '* getting the Keys or Values to a Variant Array requires an interim step
    '* which we packed into a function below
    '*
    Dim vKeys
    vKeys = SortedListToArray(sortedList, True)
    Debug.Assert vKeys(0) = "Blue"      '* sorted
    Debug.Assert vKeys(1) = "Green"     '* sorted
    Debug.Assert vKeys(2) = "Red"       '* sorted

    Dim vValues
    vValues = SortedListToArray(sortedList, False)
    Debug.Assert vValues(0) = "0000FF"      '* sorted
    Debug.Assert vValues(1) = "00FF00"      '* sorted
    Debug.Assert vValues(2) = "FF0000"      '* sorted
    
    '*
    '* ContainsKey and IndexOfKey
    '* (Scripting.Dictionary only has equiavlent of ContainsKey(), Exists() )
    '*
    Debug.Assert sortedList.ContainsKey("Red")
    Debug.Assert Not sortedList.ContainsKey("Yellow")

    Debug.Assert sortedList.IndexOfKey("Red") = 2 '* not 0 because it is sorted!


    '*
    '* ContainsValue and IndexOfValue
    '* (Scripting.Dictionary has neither of these features)
    '*

    Debug.Assert sortedList.ContainsValue("FF0000")
    Debug.Assert Not sortedList.ContainsValue("FFFF00")

    Debug.Assert sortedList.IndexOfValue("FF0000") = 2 '* not 0 because it is sorted!

End Sub

Function SortedListToArray(ByVal sortedList As Object, ByVal bKeysOrValue As Boolean)
    '*
    '* getting the Keys or Values (aka Items) to a Variant Array
    '* requires an interim step with a temporary ArrayList
    '*
    
    If Not TypeName(sortedList) = "SortedList" Then Err.Raise vbObjectError, , "#argument should be a SortedList!"
    
    Dim arrayListValues As Object
    Set arrayListValues = CreateObject("System.Collections.ArrayList")
    If bKeysOrValue Then
        arrayListValues.AddRange sortedList.GetKeyList
    Else
        arrayListValues.AddRange sortedList.GetValueList
    End If
    SortedListToArray = arrayListValues.ToArray()
    
End Function