How to Iterate over Keys of a .NET HashTable from VBA

Question

The .net runtime is exposed to VBA (and other COM clients) via mscorlib.dll. In theory, VBA can consume .net HashTables. In practice I am stuck. I have commented out lines that themselves don't work. The simple use case is iterating over the keys as I would for a Scripting.Dictionary

Function GenerateSampleHashTable() As Object
    Dim ht As Object
    Set ht = CreateObject("System.Collections.HashTable")
    ht.Add "Foo", "Bar"
    ht.Add "Red", "FF0000"
    ht.Add "Green", "00FF00"
    ht.Add "Blue", "0000FF"
    Set GenerateSampleHashTable = ht
End Function

Sub TestHashTable()
    Dim ht As Object
    '*** PRETEND THIS CAME OUT OF A C# COMPONENT ***
    Set ht = GenerateSampleHashTable

    Debug.Print ht.ContainsKey("Foo")

    Dim oKeys As Object
    Set oKeys = CreateObject("System.Collections.ArrayList")
    oKeys.Capacity = ht.Count

    Dim vKeys() As Variant
    vKeys() = oKeys.ToArray()



    Dim col As mscorlib.ICollection
    Set col = ht.Keys()
    'col.CopyTo oKeys, 0   'Runtime error: Type mismatch
    'col.CopyTo vKeys(), 0  'Compile Error: Type mismatch

    Dim vKeyLoop As Variant
    For Each vKeyLoop In vKeys()

        Debug.Print vKeyLoop, ht.Item(vKeyLoop)
    Next
End Sub

FWIW iterating the keys of a hash table kinda defeats the purpose of using a hash table =) ...that said `col As mscorlib.ICollection` looks right, what subtype do you get if you make it `col As Variant`? — Mathieu Guindon, Sep 20 '18 at 20:13
Could it be the extraneous `()` parens confusing VBA there? Arglist parens are required in .NET, not in VBA; should be `ht.Keys`, not `ht.Keys()`. Just a thought. I don't really think that's the problem. — Mathieu Guindon, Sep 20 '18 at 20:17
Hash lookup is O(1) - *that* is why we use a hashset. Iterating the keys is O(n). i.e. zero benefits vs e.g. any old array. — Mathieu Guindon, Sep 20 '18 at 20:38
I can see that. Just saying, regardless of what people do in many other languages, that this is not what a hashset is useful for and you might as well be using a plain old array - true in every single one of these other languages too. — Mathieu Guindon, Sep 20 '18 at 20:59

GSerg · Accepted Answer · 2018-09-20T21:31:29.580

I am not surprised that passing a VBA array to a .NET method that expects System.Array (such as CopyTo(System.Array, Integer)) causes incompatibility.

I have not achieved much success in directly using the collection returned from .Keys, so copying it to an ArrayList does seem to be a helpful workaround.

HashTable.Keys returns an ICollection, and ArrayList can accept an ICollection in its AddRange(), so this should work:

Dim ht As Object
Set ht = GenerateSampleHashTable()

Dim oKeys As Object
Set oKeys = CreateObject("System.Collections.ArrayList")
oKeys.AddRange ht.Keys()

The resulting oKeys can be enumerated directly:

Dim i As Long
For i = 0 To oKeys.Count - 1
    Debug.Print oKeys.Item(i)
Next

Or it can be enumerated with For Each with the help of a wrapper class described in Wrap .Net ArrayList with custom VBA class get iterator (where all credits go):

Dim wr As ThatCollectionWrapperClass
Set wr = New ThatCollectionWrapperClass
wr.Init oKeys  'Poor man's constructor - add that method to the class and remove its Class_Initialize

Dim k As Variant
For Each k In wr
    Debug.Print k
Next

How to Iterate over Keys of a .NET HashTable from VBA

1 Answers1