VBA - .NET - Writing a System.Collections.SortedList to an Excel Worksheet

I have been playing around with some .NET objects that are creatable and usable from VBA. I was wondering if I could stop using Scripting.Dictionary and start using the .net collections classes. I use Scripting.Dictionary for a great many use cases because it exports variants arrays which are easily passed around and pastable onto a worksheet. What would code to write the contents of a System.Collections.SortedList look like?

On a documentation note, we have to wrestle with the lack of Intellisense (even if you do early bind to mscorlib!), I will return to that topic soon but for the meantime see here for a list of methods and properties of SortedList. I investigated them for a while and here are my findings.

• The SortedList.Keys() property returns an ICollection which is unusable from VBA;
• The SortedList.GetKey() method takes an index parameter to intuitively return a single item;
• The SortedList.GetKeyList() returns an IList which is usable from VBA.

We can use SortedList.GetKeyList() directly ourselves or we can pass the return result to ArrayList.AddRange(). This gives rise to three different approaches and code is given below for each. Enjoy!

Option Explicit

Function CreateSortedList() As Object
    '* common code to all attempts
    Dim objSortedList As Object ' mscorlib.SortedList
    Set objSortedList = CreateObject("System.Collections.SortedList") 'New mscorlib.SortedList
    objSortedList.Add "Red", 0
    objSortedList.Add "Green", 0
    objSortedList.Add "Blue", 0
    Set CreateSortedList = objSortedList

End Function

Sub WriteASortedListToASheet_UseArrayListToHelp()

    '*
    '* This approach has fewer lines of code but I suspect not the fastest
    '*

    Dim objSortedList As Object ' mscorlib.SortedList
    Set objSortedList = CreateSortedList
    
    '*
    '* calling SortedList.GetKeyList() and passing results to ArrayList
    '*
    
    Dim oKeys As Object
    Set oKeys = CreateObject("System.Collections.ArrayList")
    oKeys.AddRange objSortedList.GetKeyList()

    Sheet1.Cells(1, 1) = "ArrayList.ToArray()"
    Sheet1.Cells(2, 1).Resize(objSortedList.Count, 1).Value2 = Application.Transpose(oKeys.ToArray)

End Sub

Sub WriteASortedListToASheet_ReverseArrayManuallyWithGetKey()

    '*
    '* This approach builds a pastable array manually by looping through for each element
    '*

    Dim objSortedList As Object ' mscorlib.SortedList
    Set objSortedList = CreateSortedList
    
    ReDim vKeyList(1 To objSortedList.Count, 1 To 1)
    Dim lKeyLoop As Long
    For lKeyLoop = 0 To objSortedList.Count - 1
        vKeyList(lKeyLoop + 1, 1) = objSortedList.GetKey(lKeyLoop)
    
    Next lKeyLoop
    Sheet1.Cells(1, 3) = "GetKey()"
    Sheet1.Cells(2, 3).Resize(objSortedList.Count, 1).Value2 = vKeyList

End Sub

Sub WriteASortedListToASheet_ReverseArrayManuallyWithGetKeyList()

    '*
    '* This approach builds a paste-able array manually by looping through for each element of a KeyList
    '*

    Dim objSortedList As Object ' mscorlib.SortedList
    Set objSortedList = CreateSortedList
        
    Dim objList As Object 'IList
    Set objList = objSortedList.GetKeyList()

    ReDim vKeyList(1 To objSortedList.Count, 1 To 1)
    Dim lKeyLoop As Long
    For lKeyLoop = 0 To objSortedList.Count - 1
        vKeyList(lKeyLoop + 1, 1) = objList.Item(lKeyLoop)
    
    Next lKeyLoop
    
    Sheet1.Cells(1, 5) = "GetKeyList()"
    Sheet1.Cells(2, 5).Resize(objSortedList.Count, 1).Value2 = vKeyList

End Sub