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

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

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

In a 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 ArrayList's class's method list (included in listing) I could start to play around and write some sample code against the ArrayList class. Enjoy!

Sample System.Collections.ArrayList code

'               ArrayList Adapter(?)
'                   Int32 Add(?)
'                    Void AddRange(?)
'                   Int32 BinarySearch(?)
'                   Int32 BinarySearch(?,?)
'                   Int32 BinarySearch(?,?,?,?)
'                    Void Clear()
'                  Object Clone()
'                 Boolean Contains(?)
'                    Void CopyTo(?)
'                    Void CopyTo(?,?)
'                    Void CopyTo(?,?,?,?)
'                 Boolean Equals(?)
'                   IList FixedSize(?)
'               ArrayList FixedSize_2(?)
'                   Int32 get_Capacity()
'                   Int32 get_Count()
'                 Boolean get_IsFixedSize()
'                 Boolean get_IsReadOnly()
'                 Boolean get_IsSynchronized()
'                  Object get_Item(?)
'                  Object get_SyncRoot()
'             IEnumerator GetEnumerator()
'             IEnumerator GetEnumerator(?,?)
'                   Int32 GetHashCode()
'               ArrayList GetRange(?,?)
'                    Type GetType()
'                   Int32 IndexOf(?)
'                   Int32 IndexOf(?,?)
'                   Int32 IndexOf(?,?,?)
'                    Void Insert(?,?)
'                    Void InsertRange(?,?)
'                   Int32 LastIndexOf(?)
'                   Int32 LastIndexOf(?,?)
'                   Int32 LastIndexOf(?,?,?)
'                   IList ReadOnly(?)
'               ArrayList ReadOnly_2(?)
'                    Void Remove(?)
'                    Void RemoveAt(?)
'                    Void RemoveRange(?,?)
'               ArrayList Repeat(?,?)
'                    Void Reverse()
'                    Void Reverse(?,?)
'                    Void set_Capacity(?)
'                    Void set_Item(?,?)
'                    Void SetRange(?,?)
'                    Void Sort()
'                    Void Sort(?)
'                    Void Sort(?,?,?)
'                   IList Synchronized(?)
'               ArrayList Synchronized_2(?)
'                Object[] ToArray()
'                   Array ToArray(?)
'                  String ToString()
'                    Void TrimToSize()

Sub Test()
    Dim obj As Object
    Set obj = CreateObject("System.Collections.ArrayList")
    obj.Add 20
    obj.Add 16
    obj.Add 12
    obj.Add 8
    obj.Add 4
    Debug.Assert obj.Count() = 5
    Debug.Assert obj.Item(2) = 12
    
    '*
    '* try finding a value in the ArrayList,
    '* we need to call the two argument overload (the one argument version errors)
    '*
    Debug.Assert obj.IndexOf(12, 0) = 2
    
    '*
    '* we can sort the ArrayList
    '*
    obj.Sort
    Debug.Assert obj.Item(0) = 4
    Debug.Assert obj.Item(4) = 20
    
    '*
    '* we can remove an item (cool)
    '*
    Debug.Assert obj.Item(1) = 8
    Debug.Assert obj.Count = 5
    obj.RemoveAt 1
    Debug.Assert obj.Item(1) = 12
    Debug.Assert obj.Count = 4
    
    '*
    '* we can reverse the ArrayList
    '*
    obj.Reverse
    Debug.Assert obj.Item(3) = 4
    Debug.Assert obj.Item(0) = 20
    
    '*
    '* converting to an ordinary array
    '*
    Dim oArray() As Variant
    oArray() = obj.ToArray()
    Debug.Assert oArray(3) = 4
    
End Sub

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

Sample System.Collections.Stack code

'                    Void Clear()
'                  Object Clone()
'                 Boolean Contains(?)
'                    Void CopyTo(?,?)
'                 Boolean Equals(?)
'                   Int32 get_Count()
'                 Boolean get_IsSynchronized()
'                  Object get_SyncRoot()
'             IEnumerator GetEnumerator()
'                   Int32 GetHashCode()
'                    Type GetType()
'                  Object Peek()
'                  Object Pop()
'                    Void Push(?)
'                   Stack Synchronized(?)
'                Object[] ToArray()
'                  String ToString()

Sub Test()
    Dim obj As Object
    Set obj = CreateObject("System.Collections.Stack")
    obj.Push 4
    obj.Push 8
    obj.Push 12
    
    Dim vLoop As Variant
    For Each vLoop In obj
        Debug.Print vLoop
    Next
    
    Debug.Assert obj.Peek = 12
    Debug.Assert obj.Count = 3
    Debug.Assert obj.Pop = 12
    Debug.Assert obj.Count = 2
    Debug.Assert obj.Peek = 8
    Debug.Assert obj.Pop = 8
End Sub

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

Sample System.Collections.Queue code

'                    Void Clear()
'                  Object Clone()
'                 Boolean Contains(?)
'                    Void CopyTo(?,?)
'                  Object Dequeue()
'                    Void Enqueue(?)
'                 Boolean Equals(?)
'                   Int32 get_Count()
'                 Boolean get_IsSynchronized()
'                  Object get_SyncRoot()
'             IEnumerator GetEnumerator()
'                   Int32 GetHashCode()
'                    Type GetType()
'                  Object Peek()
'                   Queue Synchronized(?)
'                Object[] ToArray()
'                  String ToString()
'                    Void TrimToSize()

Sub Test()
    Dim obj As Object
    Set obj = CreateObject("System.Collections.Queue")
    obj.Enqueue 4
    obj.Enqueue 8
    obj.Enqueue 12
    
    Dim vLoop As Variant
    For Each vLoop In obj
        Debug.Print vLoop
    Next
    
    Debug.Assert obj.Peek = 4
    Debug.Assert obj.Count = 3
    Debug.Assert obj.Dequeue = 4
    Debug.Assert obj.Count = 2
    Debug.Assert obj.Peek = 8
    Debug.Assert obj.Dequeue = 8
    Debug.Assert obj.Dequeue = 12
    Debug.Assert obj.Count = 0
End Sub