VBA - .NET Interop - System.Collections.HashTable 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 HashTable's class's method list (included in listing) I could start to play around and write some sample code against the HashTable class.

I'd like to stop using Scripting.Dictionary (Microsoft Scripting Runtime) and the System.Collections.SortedList pretty much does everything I need so far. The HashTable is another possible substitute but lacks the sorting and indeed the arrival sequence that you get with a Scripting.Dictionary. Because it does not sort IndexOf is not implemented. I suppose HashTable has performance advantages over SortedList.

All 5 of the (non-generic) collection classes are featured in this C# Corner article Overview of Collection, Array List, Hash Table, Sorted List, Stack and Queue .

Sample System.Collections.HashTable code

Option Explicit

'                    Void Add(?,?)
'                    Void Clear()
'                  Object Clone()
'                 Boolean Contains(?)
'                 Boolean ContainsKey(?)
'                 Boolean ContainsValue(?)
'                    Void CopyTo(?,?)
'                 Boolean Equals(?)
'                   Int32 get_Count()
'                 Boolean get_IsFixedSize()
'                 Boolean get_IsReadOnly()
'                 Boolean get_IsSynchronized()
'                  Object get_Item(?)
'             ICollection get_Keys()
'                  Object get_SyncRoot()
'             ICollection get_Values()
'   IDictionaryEnumerator GetEnumerator()
'                   Int32 GetHashCode()
'                    Void GetObjectData(?,?)
'                    Type GetType()
'                    Void OnDeserialization(?)
'                    Void Remove(?)
'                    Void set_Item(?,?)
'               Hashtable Synchronized(?)
'                  String ToString()

Sub Test()

    Dim hashTable As Object  '* this tracks uniqueness
    Set hashTable = CreateObject("System.Collections.HashTable")
    
    '*
    '* load up the sorted list just like a dictionary
    '*
    hashTable.Add "Red", "FF0000"
    hashTable.Add "Green", "00FF00"
    hashTable.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 = hashTableToArray(hashTable, True)
    Debug.Assert vKeys(0) = "Blue"
    Debug.Assert vKeys(1) = "Red"
    Debug.Assert vKeys(2) = "Green"

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

    'Debug.Assert hashTable.IndexOfKey("Red") = 2 '* doesn't exist


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

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

    'Debug.Assert hashTable.IndexOfValue("FF0000") = 2 '* doesn't exist

End Sub

Function hashTableToArray(ByVal hashTable 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(hashTable) = "Hashtable" Then Err.Raise vbObjectError, , "#argument should be a HashTable!"
    
    Dim arrayListValues As Object
    Set arrayListValues = CreateObject("System.Collections.ArrayList")
    If bKeysOrValue Then
        arrayListValues.AddRange hashTable.Keys
    Else
        arrayListValues.AddRange hashTable.Values
    End If
    hashTableToArray = arrayListValues.ToArray()
    
End Function