Search code examples
excelvbadictionary

Checking for empty dictionary item (not key)

Every now and then an empty item is added to a dictionary when I don't check it the key exists. I know why it's happening, but I don't know how to check if it has. For instance, in the code segment below;

dictVar("Happy") = 23
dictVar("Lucky") = 666
if (dictVar("Oopsie") = 23) then
   {Do Something}
end if

I mistakenly check for "Oopsie" entry. The dictionary.keys watch list will show the 3 keys, the dictionary.items list shows 23 and 66 respectively for "Happy" and "Lucky". "Oopsie" shows "Empty" as the value (no quotes) as the key didn't exist when I referenced it. No big surprise.
enter image description here

I'd like to code an routine to handle this kind of issue, but I can't find anything on how to check the item of a dictionary key for "Empty". I have tried "is empty" and "is nothing" - both fail as the item value appears to be inaccessible.

Everything I find online refers to an empty Key, and that it adds if not checking EXISTS first, not nothing on checking the item value itself.

Is there a way to check for this?

Solution

  • I am not sure if this is what you want, but give it a try...

    If you want to check if it is empty or not, use the alternative IF statement.

    Sub Test()
    Dim dictVar As Object, Key As Variant
    Dim myCheck As Boolean
    
    myCheck = False
    
    Set dictVar = CreateObject("Scripting.Dictionary")
    
    dictVar("Happy") = 23
    dictVar("Oopsie") = 9978
    dictVar("Lucky") = 666
    
    For Each Key In dictVar.keys
        If Key = "Oopsie" And dictVar(Key) = 99 Then
'        If Key = "Oopsie" And dictVar(Key) <> "" Then
           myCheck = True
           Exit For
        End If
    Next
    
    MsgBox IIf(myCheck, "OK", "Not OK")
End Sub