Search code examples
vbaexceldictionaryruntime-error

Scripting.Dictionary adding items without using Dictionary.Add - BUG?

Scripting.Dictionary likes to add values for no reason, when you look a value up! Demonstrated with a 30 second example:

Create a new worksheet, and fill in A1:A4 = {1,2,3,4}

Insert a new vba module and add this code

Public Sub test()

    Dim rowIndex As Integer
    '
    Dim dict As Scripting.Dictionary
    Set dict = New Scripting.Dictionary

    For rowIndex = 1 To 4

        dict.Add Trim(Sheet1.Cells(rowIndex, 1).Value), rowIndex

        Dim notEvenAddingSoWhyAreYouAdding As Variant
        notEvenAddingSoWhyAreYouAdding = dict(Sheet1.Cells(rowIndex, 1).Value)

    Next rowIndex

End Sub

Put a breakpoint on Next rowIndex

Run the sub and inspect the value of dict. It now has two values, "1" and 1, as you can see in the image below:

enter image description here

What. The. Hell?!

I realise I have the Trim(...) function in the dict.Add() line, which means there are two distinct keys in use, but why is it adding an extra value when it does a lookup?! That makes no sense whatsoever - now dict.Count would not give the value I would expect.

Solution

  • You creating one key as a string representing 1 (e.g. "1") and the key's item as the number 1 using the conventional dict.Add <key>,<item>.

    Immediately afterwards, you shortcut add another with the number 1 as the key.personally, I shortcut the add/overwrite with dict.item(1) = "abc" but your method works as well.

    tbh, I'm not even sure if .CompareMode = vbTextCompare would resolve a string 1 to equal a numeric 1. In any event, you are currently on a vbBinaryCompare so there is no way it will match.