Hash Table/Associative Array in VBA

vba hash hashtable associative-array
Tyler picture Tyler · Aug 21, 2009 · Viewed 125.9k times · Source

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?

Answer

jtolle picture jtolle · Aug 21, 2009

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

Related questions

Password hash function for Excel VBA

I need a function written in Excel VBA that will hash passwords using a standard algorithm such as SHA-1. Something with a simple interface like: Public Function CreateHash(Value As String) As String ... End Function The function needs to work …

Read More
VBA hash string

How do I get a short hash of a long string using Excel VBA Whats given Input string is not longer than 80 characters Valid input characters are: [0..9] [A_Z] . _ / Valid output characters are [0..9] [A_Z] [a_z] (lower and upper …

Read More
How to get the MD5 hex hash for a file using VBA?

How can I get the MD5 hex hash for a file using VBA? I need a version that works for a file. Something as simple as this Python code: import hashlib def md5_for_file(fileLocation, block_size=2**20): f = open(…

Read More