Using .NET HashTable Return Type in VBA

Question

I have created a .NET library in VB.NET and there is a function that returns an object of HashTable.

I have been searching for how to access the elements in the HashTable object in Excel VBA but can't find a solution. I am new to VBA so pardon me. I have searched but can't find a way out.

For instance, after something like this, I don't know how to access the data.

   Dim hashData As Object
   Set hashData = obj.getHashData

Please help

Answer 1

Dim hashData As Object
Set hashData = obj.getHashData

If getHashData is returning a HashTable, then hashData is a late-bound HashTable, and you can invoke its members, including its Item property:

Dim value As Variant
value = hashData.Item("key")

You're not getting compile-time validation on the late-bound member calls against Object, so you need to be particularly careful for typos, because Option Explicit cannot save you when late binding is involved. Refer to the HashTable documentation linked above for what members you can invoke.

Adding a reference to mscorlib.tlb (you'll find it under C:\Windows\Microsoft.NET\Framework\v4.0.30319, or reference the equivalent from \Framework64 if your Excel is 64-bit - bitness of the library needs to match the bitness of the host application) would normally allow for early binding, but while this library is COM-visible, it's intended to be used from managed (.net) code, so you're accessing these objects from interfaces - the concrete types don't expose any members directly:

Knowing that Hashtable implements the IDictionary interface, we can use early binding and get compile-time validation and IntelliSense if we declare hashData As IDictionary:

Dim hashData As mscorlib.IDictionary
Set hashData = New mscorlib.Hashtable
hashData.Add "foo", 42
Debug.Print hashData.Item("foo") 'prints 42

Note that the Item property is exposed as the default member:

This means you can have the Item member call implicit, exactly as you could do with any standard VBA collection object:

Dim hashData As mscorlib.IDictionary
Set hashData = New mscorlib.Hashtable
hashData.Add "foo", 42
Debug.Print hashData("foo") 'prints 42

Early-bound code is much easier to write, especially when you're not familiar with the types involved. However if the project is referencing the 64-bit framework and your macros need to run on 32-bit Excel, you'll want to stick to late binding to avoid binding issues.

Also note, iterating the Hashtable object with a For Each loop isn't going to work, because of how enumerators work in VBA vs how they work in .NET; the Keys and Values collections are objects implementing the ICollection interface, so iterating them will be non-trivial as well: a For Each loop won't work, and while you can set up a For i = 0 To hashData.Keys.Count - 1, you can't get the item at index i from an ICollection.

But we know that ICollection inherits IEnumerable, and IEnumerable does work with For Each, so we can cast the Keys collection to IEnumerable, and iterate all keys and values like so:

Dim hashData As mscorlib.IDictionary
Set hashData = obj.getHashData

Dim hashKeys As mscorlib.IEnumerable
Set hashKeys = hashData.Keys

Dim k As Variant
For Each k In hashKeys
    Debug.Print k, hashData(k) 'outputs the key and its associated value
Next

The problem is that you can't cast to IEnumerable with late-bound code or without a reference to mscorlib.tlb, and late binding somehow won't see the GetEnumerator member, so this raises error 438:

Dim hashKeys As Object
Set hashKeys = hashData.Keys

Dim k As Variant
For Each k In hashKeys ' error 438, hashKeys isn't exposing the enumerator
    Debug.Print k, hashData(k) 
Next

Conclusion: if you need the VBA code to run on both 32 and 64 bit hosts, you'll have to jump through hoops to get late-bound code to work. I would recommend working early-bound with the 64-bit framework if you're on a 64-bit host, and distributing a separate copy of the macro that references the 32-bit framework for 32-bit hosts. A bit of a pain to distribute, but less painful than getting late bound code to work.

Answer 2

I understand that a lot of time has passed since this was asked/answered but please allow me to answer, for the sake of others like me who are trying to find a way to iterate through a HashTable's keys (in VBA's VBIDE).

By reading through Mathieu Guindon's answer (which is quite awesome) and this as well as this, I would like to present my 2cents that we can do this without having to add references and late-bound (also working inside VBA's Immediate Window).

As shown in the attached screenshot, we just need to add HashTable's keys to an ArrayList by using .AddRange method of that ArrayList like:

ArrayList.AddRange HashTable.Keys

This was also mentioned here.

Since HashTable's Keys property being an ICollection while ArrayList's AddRange method works with an ICollection as well, they are like a match made in heaven, et voila, now we can iterate through a HashTable's keys, albeit in a roundabout way as an ArrayList.

I got this idea from what's mentioned here about using a Queue's Enqueue to add a range of values to an ArrayList. However, that method won't allow iterating using For Each in VBA but rather treats the added keys as a single object rather than an iterable object.

I just hope that somebody somewhere at some other later date would find this useful. Thank you all.