.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