Simple Hash Table Excel VBA

Question

I'm a long user of arrays in VBA but I recently learned a bit about hashing and I was wondering if I could use that to build more efficient searches in my arrays. To keep it specific, what I did was to turn a two dimensional array into a dictionary of rows where the keys is a string (which off course is unique) found in a 'cell' and turned into a double via asc.

I guess the code below explains what I mean:

Private pHook As Object

Sub test()
Set pHook = CreateObject("Scripting.Dictionary")

key = StoAsc("SomeStringOneWantstoFind")

If Not pHook.Exists(key) Then pHook.Add key, "TEST"

d = pHook(key)

End Sub

Public Function StoAsc(stg As String) As Double:

    Dim key As String

    key = ""

   For ii = 1 To Len(stg)

        S = Asc(Mid(stg, ii, 1))

        key = key & S

    Next ii

    StoAsc = CDbl(key)
End Function

It looks like it works and it did the job of avoiding a the loop when I just want to find something in the data.

But I can't get out of my mind the idea that there should be a easier and more logical path than building the hashing myself. Am I in a good path? Are there easier ways to 'hash an array' so don't have to loop around every time I need something?

Dictionary object lookup is very fast compared to (eg) using `Find()` in a loop over a range: it's a great approach and I use it a lot myself. What's the purpose behind the `StoAsc` conversion though? Why not just use the string directly as a key? — Tim Williams, Jun 16 '13 at 22:41
+1 on what @TimWilliams said. If you are working with arrays as worksheet range, then maybe you could simplify by just using the `Application.Match` function against the 1st column in the range. Dictionary lookup is probably faster, still, but `Match` is certainly more efficient than looping the range. — David Zemens, Jun 17 '13 at 02:17
@TimWilliams. Indeed! I was under the impression that Dictionaries always needed numeric keys (isn't it this way in Python?). Does using a string instead of a integer affects performance? I guess not right? — , Jun 17 '13 at 05:54
Performance seems to be pretty fast whatever type of key you use. I guess internally there's some hashing going on. — Tim Williams, Jun 17 '13 at 14:49
In Python dictionary keys can be of any immutable type, including strings. — John Coleman, Oct 20 '16 at 22:43

score 0 · Answer 1 · edited May 23 '17 at 10:32

Dictionaries allow strings (or any data type except arrays) to be used as key values (and as item values). So as you suspected, you have no need to do any hashing yourself, all you need to do so store "SomeStringOneWantstoFind" in both the key and the value.

There is an exists method on the dictionary object that lets you find out whether a key value exists which can be used to do this.

Collections can be set up with just a key value, so you could use a collection instead of a dictionary but collections do not have the exists method.

I'm quite new to collections/dictionaries and arrays, so I created a useful crib sheet which I have shared here

I'd welcome your input, as I still feel I don't quote get it, and I'm sure you have moved on since you wrote this question.

Here's my understanding of your question and what you are doing.

In your code you convert "SomeStringOneWantstoFind" to a unique number (using Asc) and store this as hey key and "TEST" as the text. I suspect in reality you would store "SomeStringOneWantstoFind" as the value.

So why are you doing this is the question!

You mention hashing. So you want to look up a text value to see if it is in the dictionary. ie find out whether "MyTextToFind" exists.

So I assume you are converting "MyTextToFind" using Asc in a similar way then using the dictionary exists to see if it is there.

This is all a bit unnecessary - I think.

Be aware that Dictionaries always need a key and a Item (ie a value)

Simple Hash Table Excel VBA

1 Answers1