0

I am encountering what I think is bizarre behavior in Excel VBA trying to access a dictionary with a non-existent key. Specifically, by requesting the value at that key, the key is automatically added. I have not found this in any of the documentation I could find. Easy to try it:

Sub Test()
       Dim d As Scripting.Dictionary
       Set d = New Scripting.Dictionary
       Debug.Print d.Exists("foo")
       Debug.Print d("foo")
       Debug.Print d.Exists("foo")
End Sub

False
True

Is this really the expected behavior?

RonR
  • 298
  • 1
  • 13
  • In a word, yes, if you add the pair that way. Only with `AddItem` will it error if you add duplicate. – SJR Dec 17 '20 at 18:03
  • Yes: https://excelmacromastery.com/vba-dictionary/ Look under *Assigning a Value*. – Zwenn Dec 17 '20 at 18:04
  • 1
    This is a known (but maybe not well known) "feature" of the dictionary object - eg. if you simply have a Watch on `d("foo")` then that will by itself create that key. https://stackoverflow.com/questions/41820608/excel-strange-behavior-of-a-dictionary-object/41858402#41858402 and also https://stackoverflow.com/questions/11400898/dictionary-object-adding-items-before-add-is-called – Tim Williams Dec 17 '20 at 18:12
  • @SJR (and Zwenn) I am not (knowingly) "adding" the key via the assign method - I am simply attempting to retrieve its value. There's no assignment statement in the code. That's why it seems wrong to me. – RonR Dec 17 '20 at 18:13
  • @TimWilliams That's what I was looking for - confirmation that it's expected. If you'll post this as an answer I'll accept it. – RonR Dec 17 '20 at 18:14
  • 1
    More appropriate to close as a duplicate I think - the linked duplicate points to the official documentation which has this behavior outlined - "If key is not found when attempting to return an existing item, a new key is created and its corresponding item is left empty." – Tim Williams Dec 17 '20 at 18:18

0 Answers0