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

https://learn.microsoft.com/en-us/dotnet/api/system.collections.hashtable?view=netframework-4.8 This one? Maybe try casting it to a Scripting.Dictionary... Another way to do this in VBA is to create a custom type that has exactly the same "model" as the object cast it. — L8n, Jun 20 '19 at 18:23
https://stackoverflow.com/a/30312051/10223558 Seems to be the solution, as HashTable Implements IDictionary which you can use. — L8n, Jun 20 '19 at 18:25
How would you access the data in VB.NET? Have you tried the same in VBA? FWIW iterating a `HashTable` (or a `Dictionary` for that matter) to get the data, is the single most inefficient thing to do with a `HashTable`. Make the VB.NET code return an `ArrayList` if you need the client code to iterate the values. — Mathieu Guindon, Jun 20 '19 at 18:38
@l8n I am trying to cast the return value which is now ```IDictionary``` to ```Scripting.Dictionary``` but I am facing problem. Maybe I am not doing it in the right way: ```Dim hashData As Object Set hashData = obj.getHashData Dim dict As Scripting.Dictionary Set dict = CType(hashData, Scripting.Dictionary) ``` But I get a compile time error in the case that ```Dictionary``` data or member not found. I think I am doing it the wrong way with the cast I will appreciate if I can be directed. Thanks. — Daniel Oppong, Jun 20 '19 at 19:44
Try `set dict = hashData` `CType()` afaik does not exist in vba... Anyway, listen to @MathieuGuindon , he is the one I learned most of my VBA stuff from and is usually right on the point with his advice^^ — L8n, Jun 20 '19 at 20:03
@l8n I get ```Type Mismatch``` error with ```Set dict = hashData```. Any other ways? — Daniel Oppong, Jun 20 '19 at 20:15
You can't cast a .net type to a VBScript interface, the VBScript type predates .net by a decade, the interfaces aren't compatible - hence the type mismatch error. Either use `Object` and perform late-bound member calls by looking up the .net documentation for `HashTable`, or reference `mscorlib.tlb` and see if there's a usable interface exposed. If there isn't, you will have to recompile the .net code after modifying it to return a type that VBA/COM can consume, like `ArrayList`, or try to marshal a .net array as a COM `SAFEARRAY`. .net and COM are very different technologies... — Mathieu Guindon, Jun 20 '19 at 23:25
@MathieuGuindon According to the docs, `HashTable` does have `ComVisible` set, so I would expect it to be usable from VBA. — Craig, Jun 21 '19 at 14:02
@Craig see answer below. To be fair, if OP's VB.NET code is designed to be consumed by COM/VBA then it should be exposing COM types, not .NET types - be it just to make the consuming code easier to write. — Mathieu Guindon, Jun 21 '19 at 14:34

Mathieu Guindon · Answer 1 · 2019-06-21T14:57:26.447

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:

object browser showing no members for Hashtable class

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:

members of IDictionary

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.

Nay Lynn · Answer 2 · 2023-08-06T09:30:36.090

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.

Using .NET HashTable Return Type in VBA

2 Answers2

Linked