Test if Item of a VBA dictionary is equal to a given value without adding key and item to the dictionary

Question

I need to test if the Item of a VBA Dictionary is equal to a given value, without adding a new key and value to the dictionary.

dict.RemoveAll
MsgBox dict.Exists(key)

MsgBox dict.Exists(key) returns false, the dict is empty.

var = "Hello"
MsgBox var = dict(key)

The MsgBox returns false, because var and the item returned by dict(key) is not equal. But when I again check if the key exists in the dictionary, dict.Exists(key) now returns true.

MsgBox dict.Exists(keys)

Seems to me the equal-Operator not only compares var with the item, but also assigns a new key and item pair to the dict, but i need the dictionary still to be empty. How can i achieve this?

Well, that's pretty interesting. Lots of good information here: https://stackoverflow.com/questions/6910996/scripting-dictionary-lookup-add-if-not-present-with-only-one-key-search — Cameron Critchlow, Jul 08 '22 at 18:16
@BigBen Sub ScriptingDictionary() Dim Dict As Object Dim Key Dim Var Key = 1 Var = "Hello" Set Dict = CreateObject("scripting.dictionary") Dict.RemoveAll Debug.Print Dict.Exists(Key) Debug.Print "CNT=" & Dict.Count Debug.Print Var = Dict(Key) Debug.Print "CNT=" & Dict.Count Debug.Print Dict.Exists(Key) Debug.Print "CNT=" & Dict.Count Debug.Print Dict(Key) Debug.Print "CNT=" & Dict.Count End Sub — Cameron Critchlow, Jul 08 '22 at 18:18
If you assign a variable to be a non existent key's item pair it creates that key with an empty item pair, or in this case just comparing the values. — Warcupine, Jul 08 '22 at 18:21
`Scripting.Dictionary`s are [wonky](https://stackoverflow.com/questions/48437731/dictionary-is-populated-with-an-empty-item-after-checking-dictionary-item-in-wat). — BigBen, Jul 08 '22 at 18:35
Yes. There might be some weird technical reason for this behavior, or maybe it was just a strange choice by the designers of the language. Anyway, I have used dictionaries for many years without problems and frankly forgot about this behavior, nor did it ever prove to be a problem (yet). This is a weird example where you are checking the value of a key in a dictionary known to be empty -- just don't do that :) — topsail, Jul 08 '22 at 18:40

topsail · Accepted Answer · 2022-07-08T18:32:38.670

2

Use exists() to check if a key exists:

Sub foo()

Dim dict As Scripting.Dictionary
Dim key As String
key = "key1"

Set dict = New Dictionary
Debug.Print dict(key) ' -- create a value of Empty for the key
Debug.Print dict.Count ' -- 1 (yikes!)

Set dict = New Dictionary
Debug.Print dict.Exists(key) ' -- no side effects
Debug.Print dict.Count '-- 0 (great!)

End Sub

See also (thanks to Cameron Critchlow):

scripting-dictionary-lookup-add-if-not-present-with-only-one-key-search

Note in your problem you are creating a variable then checking if it is equal to the value of some key (if that key exists!) - so by extension using the method described above, first check if the key exists, then check if it is the same as your variable:

Dim other_key As String
Dim result As Boolean

other_key = "key2"
If dict.Exists(key) Then
    If other_key = dict(key) Then
        result = True
    End If
End If
Debug.Print result

edited Jul 08 '22 at 18:32

answered Jul 08 '22 at 18:24

topsail

2,186
3
17
17

I don't need to check if a key exists, i need to check if the item to a key is qual to a given value and this seems to create a key with an empty item. – morebus Jul 08 '22 at 18:29
1

But ... you have to know if the key exists before you try to access the key's value, otherwise you are heading for trouble. – topsail Jul 08 '22 at 18:35
You are right. And checking if the key exists before comparing will work for me. – morebus Jul 08 '22 at 19:43

score 0 · Answer 2 · answered Jul 08 '22 at 18:30

Something along these lines will test all keys and items for a match, while not adding any extra keys.

    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")
    Dict.Add "test", "this"
    Dim Var As String
    Dim var2 As String
    Var = "test"
    var2 = "this"
    Dim Key As Variant
    
    For Each Key In Dict
        Debug.Print "var - item", Var = Dict(Key)
        Debug.Print "var - key", Var = Key
        Debug.Print "var2 - item", var2 = Dict(Key)
        Debug.Print "var2 - key", var2 = Key
    Next Key

Test if Item of a VBA dictionary is equal to a given value without adding key and item to the dictionary

2 Answers2