Hash and Stack VBA modules Posted on 2006-12-13 by waldo In order to add some nice error handling and single location easily modified strings in Access (within VBA) I’ve decided to utilise a hash** and a stack. ** – As pointed out by Anon there isn’t really any benefit to rolling your own Hash – use the Scripting.Dictionary instead. Unless you need to avoid including extra libraries (Microsoft Scripting Runtime) or are working with something less than Office 2000 (apparently the runtime is also within windows 2000) in which case my sincere sympathy is with you. These structures can be created in three classes in any VBA project (only tested in Access and Excel under WinXP). StackItem ~ Similar to a node in a linked list just contains the element and a reference to another StackItem (pointer substitute) Stack ~ consists of a number of simple functions Push ~ add your item to the top Pop ~ get and remove item from top StackTop ~ returns the topmost StackItem StackEmpty ~ true if no items on the stack (a fact shown when top equals nothing) Class_Initialize ~ set top (the default node), to nothing StackDump ~ a string representation of the entire stack HashTable ~ the original strain of this hash table Now we’ve done the plumbing the next article will describe the niceties. Code StackItem view source print?1 Option Explicit 2 3 Public Value As Variant 4 Public NextItem As StackItem Stack view source print?01 Option Explicit 02 03 Private Top As StackItem 04 05 Public Property Get StackEmpty() As Boolean 06 On Error Resume Next 07 08 StackEmpty = (Top Is Nothing) 09 End Property 10 11 Public Property Get StackTop() As Variant 12 On Error Resume Next 13 14 If StackEmpty Then 15 StackTop = Null 16 Else 17 StackTop = Top.Value 18 End If 19 End Property

20 21 Private Sub Class_Initialize() 22 On Error Resume Next 23 24 Set Top = Nothing 25 End Sub 26 27 Public Sub Push(ByVal var As Variant) 28 On Error Resume Next 29 30 Dim newTop As New StackItem 31 32 newTop.Value = var 33 Set newTop.NextItem = Top 34 Set Top = newTop 35 36 End Sub 37 38 Public Function Pop() As Variant 39 On Error Resume Next 40 41 If Not StackEmpty Then 42 Pop = Top.Value 43 Set Top = Top.NextItem 44 End If 45 End Function 46 47 Public Function StackDump(Optional ByVal delim As String) As String 48 On Error Resume Next 49 50 Dim dump As String 51 Dim item As New StackItem 52 53 Set item = Top 54 55 dump = delim & item.Value 56 57 Do While Not item.NextItem Is Nothing 58 Set item = item.NextItem 59 dump = dump & delim & item.Value 60 Loop 61 62 StackDump = Right(dump, Len(dump) - 2) 63 End Function HashTable view source print?001 Option Explicit 002 003 Private Const DefaultHashSize = 1024 004 Private Const DefaultListSize = 2048 005 Private Const DefaultChunkSize = 1024 006 007 Private Const Self = "HashTable" 008

009 Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (dest As Any, src As Any, ByVal bytes As Long) 010 011 Private Type SlotType 012 Key As String 013 Value As Variant 014 NextItem As Long 015 End Type 016 017 Private HashFirstElement() As Long 018 Private SlotTable() As SlotType 019 020 Private SlotFirstFree As Long 021 Private HashTableSize As Long 022 Private SlotTableSize As Long 023 Private LocalChunkSize As Long 024 Private SlotTableCount As Long 025 026 Private CaseInsensitive As Boolean 027 028 Public Property Get IgnoreCase() As Boolean 029 On Error GoTo ErrorGoTo 030 031 Util.PushStack "Get IgnoreCase (" & Self & ")" 032 IgnoreCase = CaseInsensitive 033 ExitGoTo: 034 Util.PopStack 035 Exit Property 036 ErrorGoTo: 037 Util.ErrorHandler 038 Resume ExitGoTo 039 End Property 040 041 'Can be assigned only when the hash table is empty 042 Public Property Let IgnoreCase(ByVal newValue As Boolean) 043 On Error GoTo ErrorGoTo 044 045 Util.PushStack "Let IgnoreCase (" & Self & ")" 046 047 If SlotTableCount Then 048 Err.Raise 65000, "IgnoreCase", "Error message" 049 Util.GetVariable ("HashTableNotEmpty") 050 End If 051 052 CaseInsensitive = newValue 053 ExitGoTo: 054 Util.PopStack 055 Exit Property 056 ErrorGoTo: 057 Util.ErrorHandler 058 Resume ExitGoTo 059 End Property 060 061 Public Sub SetSize(ByVal HashSize As Long, Optional ByVal ListSize As Long, Optional ByVal ChunkSize As Long) 062 On Error GoTo ErrorGoTo

063 064 Util.PushStack "SetSize (" & Self & ")" 065 'defaults 066 If ListSize <= 0 Then 067 ListSize = SlotTableSize 068 End If 069 If ChunkSize <= 0 Then 070 ChunkSize = LocalChunkSize 071 End If 072 073 HashTableSize = HashSize 074 SlotTableSize = ListSize 075 LocalChunkSize = ChunkSize 076 SlotTableCount = 0 077 'rebuild tables 078 SlotFirstFree = 0 079 080 ReDim HashFirstElement(0 To HashSize - 1) As Long 081 ReDim SlotTable(0) As SlotType 082 083 ExpandSlotTable SlotTableSize 084 ExitGoTo: 085 Util.PopStack 086 Exit Sub 087 ErrorGoTo: 088 Util.ErrorHandler 089 Resume ExitGoTo 090 End Sub 091 092 Public Function Exists(Key As String) As Boolean 093 On Error GoTo ErrorGoTo 094 095 Util.PushStack "Exists (" & Self & ")" 096 Exists = GetSlotIndex(Key) <> 0 097 ExitGoTo: 098 Util.PopStack 099 Exit Function 100 ErrorGoTo: 101 Util.ErrorHandler 102 Resume ExitGoTo 103 End Function 104 105 Public Sub Add(Key As String, Value As Variant) 106 On Error GoTo ErrorGoTo 107 108 Util.PushStack "Add (" & Self & ")" 109 110 Dim ndx As Long, Create As Boolean 111 ' get the index to the slot where the value is (allocate a new slot if necessary) 112 Create = True 113 ndx = GetSlotIndex(Key, Create) 114 115 If Create Then 116 ' the item was actually added 117 If IsObject(Value) Then 118 Set SlotTable(ndx).Value = Value

119 Else 120 SlotTable(ndx).Value = Value 121 End If 122 Else 123 Err.Raise 65000, "Add", "Error message" 124 Util.GetVariable ("HashTableKeyAlreadyUsed") 125 End If 126 127 ExitGoTo: 128 Util.PopStack 129 Exit Sub 130 ErrorGoTo: 131 Util.ErrorHandler 132 Resume ExitGoTo 133 End Sub 134 135 Public Property Get item(Key As String) As Variant 136 On Error GoTo ErrorGoTo 137 138 Util.PushStack "Get item (" & Self & ")" 139 140 Dim ndx As Long 141 ndx = GetSlotIndex(Key) 142 143 If ndx = 0 Then 144 'return Empty if not found 145 ElseIf IsObject(SlotTable(ndx).Value) Then 146 Set item = SlotTable(ndx).Value 147 Else 148 item = SlotTable(ndx).Value 149 End If 150 151 ExitGoTo: 152 Util.PopStack 153 Exit Property 154 ErrorGoTo: 155 Util.ErrorHandler 156 Resume ExitGoTo 157 End Property 158 159 Public Property Let item(Key As String, Value As Variant) 160 On Error GoTo ErrorGoTo 161 162 Util.PushStack "Let item (" & Self & ")" 163 164 Dim ndx As Long 165 'get the index to the slot where the value is (allocate a new slot if necessary) 166 ndx = GetSlotIndex(Key, True) 167 'store the value 168 SlotTable(ndx).Value = Value 169 170 ExitGoTo: 171 Util.PopStack 172 Exit Property 173 ErrorGoTo: 174 Util.ErrorHandler

175 Resume ExitGoTo 176 End Property 177 178 Public Property Set item(Key As String, Value As Object) 179 On Error GoTo ErrorGoTo 180 181 Util.PushStack "Set item (" & Self & ")" 182 183 Dim ndx As Long 184 'Get the index to the slot where the value is (allocate a new slot if necessary) 185 ndx = GetSlotIndex(Key, True) 186 'Store the value 187 Set SlotTable(ndx).Value = Value 188 ExitGoTo: 189 Util.PopStack 190 Exit Property 191 ErrorGoTo: 192 Util.ErrorHandler 193 Resume ExitGoTo 194 End Property 195 196 Public Sub Remove(Key As String) 197 On Error GoTo ErrorGoTo 198 199 Util.PushStack "Remove (" & Self & ")" 200 201 Dim ndx As Long, HCode As Long, LastNdx As Long 202 ndx = GetSlotIndex(Key, False, HCode, LastNdx) 203 204 If ndx = 0 Then 205 Err.Raise 65000, "Remove", "Error message" 206 Util.GetVariable ("HashTableRemoveNonExistingKey") 207 End If 208 209 If LastNdx Then 210 'this isn't the first item in the SlotTable() array 211 SlotTable(LastNdx).NextItem = SlotTable(ndx).NextItem 212 ElseIf SlotTable(ndx).NextItem Then 213 'this is the first item in the SlotTable() array and is followed by one or more items 214 HashFirstElement(HCode) = SlotTable(ndx).NextItem 215 Else 216 'this is the only item in the SlotTable() array for this hash code 217 HashFirstElement(HCode) = 0 218 End If 219 220 ' put the element back in the free list 221 SlotTable(ndx).NextItem = SlotFirstFree 222 SlotFirstFree = ndx 223 ' we have deleted an item 224 SlotTableCount = SlotTableCount - 1 225 ExitGoTo: 226 Util.PopStack 227 Exit Sub 228 ErrorGoTo: 229 Util.ErrorHandler 230 Resume ExitGoTo

231 End Sub 232 233 Public Sub RemoveAll() 234 On Error GoTo ErrorGoTo 235 236 Util.PushStack "RemoveAll (" & Self & ")" 237 238 SetSize HashTableSize, SlotTableSize, LocalChunkSize 239 ExitGoTo: 240 Util.PopStack 241 Exit Sub 242 ErrorGoTo: 243 Util.ErrorHandler 244 Resume ExitGoTo 245 End Sub 246 247 Public Property Get Count() As Long 248 On Error GoTo ErrorGoTo 249 250 Util.PushStack "Get Count (" & Self & ")" 251 252 Count = SlotTableCount 253 ExitGoTo: 254 Util.PopStack 255 Exit Property 256 ErrorGoTo: 257 Util.ErrorHandler 258 Resume ExitGoTo 259 End Property 260 261 Public Property Get Keys() As Variant() 262 On Error GoTo ErrorGoTo 263 264 Util.PushStack "Get Keys (" & Self & ")" 265 266 Dim i As Long, ndx As Long 267 Dim n As Long 268 ReDim res(0 To SlotTableCount - 1) As Variant 269 270 For i = 0 To HashTableSize - 1 271 ' take the pointer from the hash table 272 ndx = HashFirstElement(i) 273 ' walk the SlotTable() array 274 Do While ndx 275 res(n) = SlotTable(ndx).Key 276 n = n + 1 277 ndx = SlotTable(ndx).NextItem 278 Loop 279 Next 280 281 Keys = res() 282 ExitGoTo: 283 Util.PopStack 284 Exit Property 285 ErrorGoTo: 286 Util.ErrorHandler

287 Resume ExitGoTo 288 End Property 289 290 Public Property Get Values() As Variant() 291 On Error GoTo ErrorGoTo 292 293 Util.PushStack "Get Values (" & Self & ")" 294 295 Dim i As Long, ndx As Long 296 Dim n As Long 297 ReDim res(0 To SlotTableCount - 1) As Variant 298 299 For i = 0 To HashTableSize - 1 300 ' take the pointer from the hash table 301 ndx = HashFirstElement(i) 302 ' walk the SlotTable() array 303 Do While ndx 304 res(n) = SlotTable(ndx).Value 305 n = n + 1 306 ndx = SlotTable(ndx).NextItem 307 Loop 308 Next 309 310 Values = res() 311 312 ExitGoTo: 313 Util.PopStack 314 Exit Property 315 ErrorGoTo: 316 Util.ErrorHandler 317 Resume ExitGoTo 318 End Property 319 320 Private Sub Class_Initialize() 321 On Error GoTo ErrorGoTo 322 323 Util.PushStack "Class_Initialize (" & Self & ")" 324 SetSize DefaultHashSize, DefaultListSize, DefaultChunkSize 325 326 ExitGoTo: 327 Util.PopStack 328 Exit Sub 329 ErrorGoTo: 330 Util.ErrorHandler 331 Resume ExitGoTo 332 End Sub 333 334 Private Sub ExpandSlotTable(ByVal numEls As Long) 335 On Error GoTo ErrorGoTo 336 337 Util.PushStack "ExpandSlotTable (" & Self & ")" 338 339 Dim newSlotFirstFree As Long, i As Long 340 newSlotFirstFree = UBound(SlotTable) + 1 341 ReDim Preserve SlotTable(0 To UBound(SlotTable) + numEls) As SlotType 342 ' create the linked list of free items

343 344 For i = newSlotFirstFree To UBound(SlotTable) 345 SlotTable(i).NextItem = i + 1 346 Next 347 348 ' overwrite the last (wrong) value 349 SlotTable(UBound(SlotTable)).NextItem = SlotFirstFree 350 ' we now know where to pick the first free item 351 SlotFirstFree = newSlotFirstFree 352 353 ExitGoTo: 354 Util.PopStack 355 Exit Sub 356 ErrorGoTo: 357 Util.ErrorHandler 358 Resume ExitGoTo 359 End Sub 360 361 Private Function HashCode(Key As String) As Long 362 On Error GoTo ErrorGoTo 363 364 Util.PushStack "HashCode (" & Self & ")" 365 366 Dim lastEl As Long, i As Long 367 ' copy ansi codes into an array of long 368 lastEl = (Len(Key) - 1) \ 4 369 ReDim codes(lastEl) As Long 370 ' this also converts from Unicode to ANSI 371 CopyMemory codes(0), ByVal Key, Len(Key) 372 ' XOR the ANSI codes of all characters 373 374 For i = 0 To lastEl 375 HashCode = HashCode Xor codes(i) 376 Next 377 378 ExitGoTo: 379 Util.PopStack 380 Exit Function 381 ErrorGoTo: 382 Util.ErrorHandler 383 Resume ExitGoTo 384 End Function 385 386 Private Function GetSlotIndex(ByVal Key As String, Optional Create As Boolean, Optional HCode As Long, Optional LastNdx As Long) As Long 387 'Get the index where an item is stored or 0 if not found if Create = True the item is created 388 'On exit Create=True only if a slot has been actually created 389 On Error GoTo ErrorGoTo 390 391 Util.PushStack "GetSlotIndex (" & Self & ")" 392 393 Dim ndx As Long 394 395 If Len(Key) = 0 Then 396 Err.Raise 65000, "GetSlotIndex", "Error message" 397 Util.GetVariable ("HashTableGetSlotIndexInvalidKey")

398 End If 399 400 'take case-sensitivity into account 401 If CaseInsensitive Then 402 Key = UCase$(Key) 403 End If 404 405 ' get the index in the HashFirstElement() array 406 HCode = HashCode(Key) Mod HashTableSize 407 ' get the pointer to the SlotTable() array 408 ndx = HashFirstElement(HCode) 409 410 ' exit if there is no item with that hash code 411 Do While ndx 412 ' compare key with actual value 413 If SlotTable(ndx).Key = Key Then 414 Exit Do 415 End If 416 ' remember last pointer 417 LastNdx = ndx 418 ' check the next item 419 ndx = SlotTable(ndx).NextItem 420 Loop 421 422 ' create a new item if not there 423 If ndx = 0 And Create Then 424 ndx = GetFreeSlot() 425 PrepareSlot ndx, Key, HCode, LastNdx 426 Else 427 ' signal that no item has been created 428 Create = False 429 End If 430 431 ' this is the return value 432 GetSlotIndex = ndx 433 434 ExitGoTo: 435 Util.PopStack 436 Exit Function 437 ErrorGoTo: 438 Util.ErrorHandler 439 Resume ExitGoTo 440 End Function 441 442 Private Function GetFreeSlot() As Long 443 On Error GoTo ErrorGoTo 444 445 Util.PushStack "GetFreeSlot (" & Self & ")" 446 447 ' allocate new memory if necessary 448 If SlotFirstFree = 0 Then 449 ExpandSlotTable LocalChunkSize 450 End If 451 452 ' use the first slot 453 GetFreeSlot = SlotFirstFree

454 ' update the pointer to the first slot 455 SlotFirstFree = SlotTable(GetFreeSlot).NextItem 456 ' signal this as the end of the linked list 457 SlotTable(GetFreeSlot).NextItem = 0 458 ' we have one more item 459 SlotTableCount = SlotTableCount + 1 460 461 ExitGoTo: 462 Util.PopStack 463 Exit Function 464 ErrorGoTo: 465 Util.ErrorHandler 466 Resume ExitGoTo 467 End Function 468 469 Private Sub PrepareSlot(ByVal Index As Long, ByVal Key As String, ByVal HCode As Long, ByVal LastNdx As Long) 470 On Error GoTo ErrorGoTo 471 472 Util.PushStack "PrepareSlot (" & Self & ")" 473 474 'assign the key, take case-sensitivity into account 475 If CaseInsensitive Then 476 Key = UCase$(Key) 477 End If 478 479 SlotTable(Index).Key = Key 480 481 If LastNdx Then 482 ' this is the successor of another slot 483 SlotTable(LastNdx).NextItem = Index 484 Else 485 ' this is the first slot for a given hash code 486 HashFirstElement(HCode) = Index 487 End If 488 489 ExitGoTo: 490 Util.PopStack 491 Exit Sub 492 ErrorGoTo: 493 Util.ErrorHandler 4994 Resume ExitGoTo 495 End Sub