CODEWITHSUNDEEP
vbaexceldictionarykey-value
Trimax
Trimax

Reputation: 2473

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?

Upvotes: 2

Views: 430

Answers (1)

user4039065
user4039065

Reputation:

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.

Upvotes: 2

Related Questions