Hash Table/Associative Array in VBA

Question

I can't seem to find the documentation explaining how to create a hash table or associative array in VBA. Is it even possible?

Can you link to an article or better yet post the code?

Thanks!!!

Answer 1

Try using the Dictionary Object or the Collection Object.

http://visualbasic.ittoolbox.com/documents/dictionary-object-vs-collection-object-12196

Answer 2

I think you are looking for the Dictionary object, found in the Microsoft Scripting Runtime library. (Add a reference to your project from the Tools...References menu in the VBE.)

It pretty much works with anything that can fit in a variant (EDIT: actually not true - keys can't be arrays, but they can be objects):

Dim d As dictionary
Set d = New dictionary

d("x") = 42
d(42) = "forty-two"
d(CVErr(xlErrValue)) = "Excel #VALUE!"
Set d(101) = New Collection

You can also use the VBA Collection object if your needs are simpler and you just want string keys.

I don't know if either actually hashes on anything, so you might want to dig further if you need hashtable-like performance. (EDIT: Scripting.Dictionary does use a hash table internally.)

Answer 3

I've used Francesco Balena's HashTable class several times in the past when a Collection or Dictionary wasn't a perfect fit and i just needed a HashTable.