Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Hash Table/Associative Array in VBA

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?

like image 936
Tyler Avatar asked Aug 21 '09 01:08

Tyler


2 Answers

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.

like image 36
Mark Nold Avatar answered Sep 26 '22 22:09

Mark Nold


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 any simple value that can fit in a variant (Keys can't be arrays, and trying to make them objects doesn't make much sense. See comment from @Nile below.):

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.)

like image 181
jtolle Avatar answered Sep 24 '22 22:09

jtolle