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

.NET Framework objects in VBA

So from time to time I see a snippet of code which clearly shows a .NET Framework class being instantiated and its methods called on from VBA. At some point I will investigate just how widespread this technique can be used, in the meantime I am going to on this page collect code snippets.

ArrayList

The ArrayList is a useful collections object which could replace Scripting.Dictionary. Here is some code...

Sub TestDotNetArrayList()
    
    Dim oArrayList As Object
    Set oArrayList = CreateObject("System.Collections.ArrayList")
    oArrayList.add "c"
    oArrayList.add "a"
    oArrayList.add "b"
    Debug.Assert oArrayList.Item(0) = "c"
    Debug.Assert oArrayList.Item(1) = "a"
    Debug.Assert oArrayList.Item(2) = "b"
    
    oArrayList.Sort

    '* now sorted
    Debug.Assert oArrayList.Item(0) = "a"
    Debug.Assert oArrayList.Item(1) = "b"
    Debug.Assert oArrayList.Item(2) = "c"

End Sub

StringBuilder

The StringBuilder has a VBA equivalent of Mid$ but if you really want the .NET class then below is some code. Note Intellisense is not available and you'll have to research the method overload.

Sub TestDotNetStringBuilder()

    Dim oSB As Object
    Set oSB = CreateObject("System.Text.StringBuilder")
    
    oSB.AppendFormat_5 Nothing, "hello {0}", Array("simon")
    Debug.Assert oSB.tostring = "hello simon"
    
End Sub

SortedList

There is also a SortedList which takes key value pairs but which can be difficult to access. Here I use one to sort an ordinary Scripting.Dictionary

Sub TestSortDictionary()

    Dim dicIn As Scripting.Dictionary
    Set dicIn = New Scripting.Dictionary
    
    dicIn.Add "foo", 12
    dicIn.Add "bar", 11

    Debug.Assert dicIn.Keys()(0) = "foo"
    Debug.Assert dicIn.Keys()(1) = "bar"

    Set dicIn = SortDictionary(dicIn)
    
    Debug.Assert dicIn.Keys()(0) = "bar"
    Debug.Assert dicIn.Keys()(1) = "foo"
    

End Sub

Function SortDictionary(ByVal dicIn As Scripting.Dictionary) As Scripting.Dictionary

    Dim dicSorted As Scripting.Dictionary
    Set dicSorted = New Scripting.Dictionary
    
    Dim objSortedList As Object ' mscorlib.SortedList
    Set objSortedList = CreateObject("System.Collections.SortedList") 'New mscorlib.SortedList
    
    
    Dim vKeyLoop As Variant
    For Each vKeyLoop In dicIn.Keys
        objSortedList.Add vKeyLoop, dicIn(vKeyLoop)
    
    Next
    
    Dim lKeyLoop As Long
    For lKeyLoop = 0 To dicIn.Count - 1
        
        Dim vKey As Variant
        vKey = objSortedList.GetKeyList()(lKeyLoop)
        
        dicSorted.Add vKey, dicIn(vKey)
    
    Next lKeyLoop
    
    Set SortDictionary = dicSorted
End Function

TODO: some sample code for System.Security.Cryptography.HMACSHA256

To be honest given the naming convention of .NET library where everything starts with System. then one can peruse the registry from others.

Links

• VBA voor smarties SortedList