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

User · Accepted Answer

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

User · Answer

Here we go    just copy the code to a module  it s ready to use  Private Type hashtable     key As Variant     value As Variant End Type  Private GetErrMsg As String  Private Function CreateHashTable htable   As hashtable  As Boolean     GetErrMsg          On Error GoTo CreateErr         ReDim htable 0          CreateHashTable   True     Exit Function  CreateErr      CreateHashTable   False     GetErrMsg   Err Description End Function  Private Function AddValue htable   As hashtable  key As Variant  value As Variant  As Long     GetErrMsg          On Error GoTo AddErr         Dim idx As Long         idx   UBound htable    1          Dim htVal As hashtable         htVal key   key         htVal value   value          Dim i As Long         For i   1 To UBound htable              If htable i  key   key Then Err Raise 9999     Key     amp  CStr key   amp     is not unique          Next i          ReDim Preserve htable idx           htable idx    htVal         AddValue   idx     Exit Function  AddErr      AddValue   0     GetErrMsg   Err Description End Function  Private Function RemoveValue htable   As hashtable  key As Variant  As Boolean     GetErrMsg          On Error GoTo RemoveErr          Dim i As Long  idx As Long         Dim htTemp   As hashtable         idx   0          For i   1 To UBound htable              If htable i  key  lt  gt  key And IsEmpty htable i  key    False Then                 ReDim Preserve htTemp idx                  AddValue htTemp  htable i  key  htable i  value                 idx   idx   1             End If         Next i          If UBound htable    UBound htTemp  Then Err Raise 9998     Key     amp  CStr key   amp     not found           htable   htTemp         RemoveValue   True     Exit Function  RemoveErr      RemoveValue   False     GetErrMsg   Err Description End Function  Private Function GetValue htable   As hashtable  key As Variant  As Variant     GetErrMsg          On Error GoTo GetValueErr         Dim found As Boolean         found   False          For i   1 To UBound htable              If htable i  key   key And IsEmpty htable i  key    False Then                 GetValue   htable i  value                 Exit Function             End If         Next i         Err Raise 9997     Key     amp  CStr key   amp     not found       Exit Function  GetValueErr      GetValue          GetErrMsg   Err Description End Function  Private Function GetValueCount htable   As hashtable  As Long     GetErrMsg          On Error GoTo GetValueCountErr         GetValueCount   UBound htable      Exit Function  GetValueCountErr      GetValueCount   0     GetErrMsg   Err Description End Function   To use in your VB A  App   Public Sub Test       Dim hashtbl   As hashtable     Debug Print  Create Hashtable     amp  CreateHashTable hashtbl      Debug Print        Debug Print  ID Test   Add V1     amp  AddValue hashtbl   Hallo 0    Testwert 0       Debug Print  ID Test   Add V2     amp  AddValue hashtbl   Hallo 0    Testwert 0       Debug Print  ID Test 1 Add V1     amp  AddValue hashtbl   Hallo 1    Testwert 1       Debug Print  ID Test 2 Add V1     amp  AddValue hashtbl   Hallo-2    Testwert 2       Debug Print  ID Test 3 Add V1     amp  AddValue hashtbl   Hallo 3    Testwert 3       Debug Print        Debug Print  Test 1 Removed V1     amp  RemoveValue hashtbl   Hallo 1       Debug Print  Test 1 Removed V2     amp  RemoveValue hashtbl   Hallo 1       Debug Print  Test 2 Removed V1     amp  RemoveValue hashtbl   Hallo-2       Debug Print        Debug Print  Value Test 3     amp  CStr GetValue hashtbl   Hallo 3        Debug Print  Value Test 1     amp  CStr GetValue hashtbl   Hallo 1        Debug Print        Debug Print  Hashtable Content        For i   1 To UBound hashtbl          Debug Print CStr i   amp        amp  CStr hashtbl i  key   amp    -    amp  CStr hashtbl i  value      Next i      Debug Print        Debug Print  Count     amp  CStr GetValueCount hashtbl   End Sub

User · Answer

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

User · Answer

Try using the Dictionary Object or the Collection Object   http   visualbasic ittoolbox com documents dictionary-object-vs-collection-object-12196

[vba] Hash Table/Associative Array in VBA

Examples related to vba

Examples related to hash

Examples related to hashtable

Examples related to associative-array