The Subtle Dictionary Key Bug

What's in a key?  That which we call a row
By any other name should work a treat;
 - "Romeo and Juliet Write VBA"
    (from the lost works of William Shakespeare)

In a couple of recent articles, I wrote about collections and dictionaries in VBA.  I wrote about the fundamental differences between the two data structures; collections are linked lists and dictionaries are hash tables.

What I failed to mention was the subtle difference in keys between the two.

In collections, the key is a string.  In dictionaries, the key can be anything...or Nothing at all.

This difference can lead to subtle bugs, as I detailed in stackoverflow several years ago.  I've reproduced the relevant portion of my answer below the link, lightly edited for clarity.

What about .Value?

The default property for Field objects and most "data-bindable"¹ control objects is .Value. Since this is the default property, it's generally considered unnecessarily verbose to always include it explicitly VBA will implicitly return it when the context warrants it.  Thus, it's standard common practice to do this:

Dim EmployeeLastName As String
EmployeeLastName = Me.tbLastName

Instead of:

EmployeeLastName = Me.tbLastName.Value

Beware the subtle .Value bug when keying dictionaries

There are some cases where this convention can cause subtle bugs.  The most notable--and, if memory serves, only--one I've actually run into in practice is when using the value of a Field/Control as a Dictionary key.

Set EmployeePhoneNums = CreateObject("Scripting.Dictionary")
Me.tbLastName.Value = "Jones"
EmployeePhoneNums.Add Key:=Me.tbLastName, Item:="555-1234"
Me.tbLastName.Value = "Smith"
EmployeePhoneNums.Add Key:=Me.tbLastName, Item:="555-6789"

One would likely expect that the above code creates two entries in the EmployeePhoneNums dictionary.  Instead, it throws an error on the last line because we are trying to add a duplicate key.  That is, the tbLastName Control object itself is the key, not the value of the control.  In this context, the control's value does not even matter.

In fact, I expect that the object's memory address (ObjPtr(Me.tbLastName)) is likely what's being used behind the scenes to index the dictionary.  I did a quick test that seems to bear this out.

'Standard module:
Public testDict As New Scripting.Dictionary
Sub QuickTest()
    Dim key As Variant
    For Each key In testDict.Keys
        Debug.Print ObjPtr(key), testDict.Item(key)
    Next key
End Sub

'Form module:
Private Sub Form_Current()
    testDict(Me.tbLastName) = Me.tbLastName.Value
    Debug.Print ObjPtr(Me.tbLastName); "..."; Me.tbLastName
End Sub

When running the above code, exactly one dictionary item is added each time the form is closed and re-opened.  Moving from record to record (and thus causing multiple calls to the Form_Current routine) does not add new dictionary items, because it is the Control object itself indexing the dictionary, and not the Control's value.

¹ What's a "data-bindable" control?
Basically, a control with a ControlSource property, such as a TextBox or ComboBox.  A non-bindable control would be something like a Label or CommandButton.  The default property of both a TextBox and ComboBox is .Value; Labels and CommandButtons have no default property.

UPDATE: I should clarify that the "bug" I referred to above is NOT a bug within the Dictionary object's key implementation.  Rather, the bug is one that a developer introduces into their own code when they fail to appreciate the difference between how keys are implemented in collections versus dictionaries.  The bug is also a side effect of relying on the implicit evaluation of the default .Value property of fields and controls.  Calling .Value explicitly when that is the property you intend to use avoids this bug and potentially others.  Read more about default values in VBA here: VBA Trap: Default Members.

Image by Susanne Jutzeler, suju-foto from Pixabay