Dictionary object adding items before .add () name

Question

I use the dictionary object from the MS Scripting Runtime library to save a series of arrays and perform operations on array cells as needed. There is a for loop to go through the process of creating all of these entries. My problem is that when using the .exists property .exists it returns True even before the item was added.

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

I tried several 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 row / unique identifier. In the If statement, the code returns false, even at the first iteration.

Answer 1

The official documentation for the script runtime says: "If the key is not found when trying to return an existing element, the new key and its corresponding element remains empty."

... and when you're debugging in a loop, it seems like it appears right from the sky before the ".exists" function is even called. Things are good...

Instead of adding the added item, as in:

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

... just set the empty object at the moment of your key to a 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 re-open this “feature” with every revision of VBA. You may also notice the consequences of this if you experience a memory leak caused by the addition of new keys that you are not going to store.

Answer 2

I had this problem during debugging, when I had a watch that tried to return a "missing" key element. Actually, further disappointed debugging had the same problem when I literally looked at [scriptingdictonaryObject] .exists () condtional); I suggest that the “missing” key be added due to the hours. When I deleted the clock and instead created a temporary sheet for copying the array at run time, unnecessary keys were no longer added.