vba - Dictionary object adding items before .add() is called

Question

I am using a dictionary object from the MS Scripting Runtime library to store a series of arrays and perform operations on the array cells as necessary. There is a for loop to go through the process of creating all of these entries. My issue is that when using the .exists property, it is returning True even before the item has been added.

Closer debugging indicates that the key is being added to the dictionary at the beginning of the for loop, even though no .add command is used and will not be used until the end of the loop.

I have tried a few different configurations, but here is a simple example that fails:

Dim dTotals As Dictionary
Set dTotals = New Dictionary

dTotals.CompareMode = BinaryCompare

For Each cell In rAppID
    If Not dTotals.Exists(cell) Then
    Set rAppIDCells = Find_Range(cell, rAppID)
    Set rAppIDValues = rAppIDCells.Offset(0, 6)
    dAppIDTotal = WorksheetFunction.Sum(rAppIDValues)
    dTotals.Add Key:=cell.Value, Item:=dAppIDTotal
    End If
Next cell

Where each cell contains a string / unique id. At the If statement, the code is returning false, even on the first iteration.

Answer 1

In the official documentation?? for the scripting runtime it says "If key is not found when attempting to return an existing item, a new key is created and its corresponding item is left empty."

...and yea, when you're debugging in a loop, it appears to pop right out of the sky before the '.exists' function is even called. All is well...

Instead of attempting to add the item that just got added, as in:

dTotals.Add Key:=cell.Value, Item:=dAppIDTotal

...just set the empty object currently at your key to your new one:

dTotals(cell.Value) = dAppIDTotal

So your code block becomes:

If Not dTotals.Exists(cell) Then
    Set rAppIDCells = Find_Range(cell, rAppID)
    Set rAppIDValues = rAppIDCells.Offset(0, 6)
    dAppIDTotal = WorksheetFunction.Sum(rAppIDValues)
    dTotals(cell.Value) = dAppIDTotal
End If

Voila. I tend to rediscover this "feature" on every revisit to VBA. You may also notice the effects of it if you are having a memory leak caused by adding new keys that you do not intend to store.