vbaexceldictionarykey-value

Strange behaviour of .item() method of Scripting.Dictionary class in VBA


As I have programmed before in Python language I'm accustomed to use the Dictionary structure of data. Now, I need to program in VBA, and I want to use the same structure, if it is possible.

In order to learn the methods in VBA I've wrote this procedure. First .count method throws "4", but second .count method throws "5". When I've debugged I'm surprised of a new key "COSLADA" is in dic with an empty value. So, when I intent to retrieve an inexistent key item, instead of it throws me an error, the .item() method creates a new item with empty value.

Sub Diccionarios()
    Dim dic As Scripting.Dictionary
    Set dic = New Scripting.Dictionary
    With dic
        .Add "ALCORCON", "MADRID"
        .Add "COLLADO VILLALBA", "MADRID"
        .Add "FUENLABRADA", "MADRID"
        .Add "TORRREJON DE ARDOZ", "MADRID"
    End With
    MsgBox dic.Count
    MsgBox dic.Item("COSLADA")
    MsgBox dic.Count
End Sub

Is there any other Dictionary method to retrieve a value of item that don't creates an item with empty value when the key don't exists?


Solution

  • This is known behavior and Microsoft regularly gets wrist-slapped in blogs and other online diatribes about it.

    The 'workaround' (if you can call it that for a known method of dealing with a known behavior) is to use the Scripting.Dictionary's Exists method to test for the key's existence before requesting the item.

    Sub Diccionarios()
        Dim dic As Scripting.Dictionary
        Set dic = New Scripting.Dictionary
        With dic
            .Add "ALCORCON", "MADRID"
            .Add "COLLADO VILLALBA", "MADRID"
            .Add "FUENLABRADA", "MADRID"
            .Add "TORRREJON DE ARDOZ", "MADRID"
        End With
        MsgBox dic.Count
        If dic.Exists("COSLADA") Then _
            MsgBox dic.Item("COSLADA")
        If dic.Exists("FUENLABRADA") Then _
            MsgBox dic.Item("FUENLABRADA")
        MsgBox dic.Count
    End Sub
    

    Simply put, the backend of the Microsoft Scripting.Dictionary is different from Python's dictionary and they behave differently because they are not the same thing. I've never really figured out why so many people cannot grasp that.