2

I've been using MS Scripting Runtime Library dictionaries as my goto data structure for the VBA project I'm currently working on, and I just ran into a frustrating issue that is making me think that they might not really be as good as I thought they were. The .Exists method seems like a very useful method, but I get some weird behavior when I try to use it inside a For loop:

Sub TestExists()
Dim i As Long
Dim dRowsInEachCol As Dictionary
Set dRowsInEachCol = New Dictionary

Dim lColLen As Long, lColLenFreq As Long

For i = 0 To 100
    dRowsInEachCol.Add i, i
Next

Dim dColLenFreqs As Dictionary
For i = 0 To dRowsInEachCol.Count - 1
        
    lColLen = dRowsInEachCol.Items()(i)
    
    If i = 0 Then
        Set dColLenFreqs = New Dictionary
    End If
    Debug.Print dColLenFreqs.Exists(0), _
                dColLenFreqs.Exists(1), _
                dColLenFreqs.Exists(lColLen)
                
    If dColLenFreqs.Exists(lColLen) Then
         lColLenFreq = dColLenFreqs.Item(lColLen) + 1
    Else
        lColLenFreq = 1
    End If
    
'        'This will add a new item to dUniqColLengths _
'            w/ key = column length, or overwrite item w/ _
'            new column number if that key exists already _
'            (INTENDED result is unique col lengths only in dict):
    dColLenFreqs.Item(lColLen) = lColLenFreq
Next
End Sub

First, there seems to be a key without an item that is added implicitly when I create the dictionary (similar to the issue in this question). But I can't just accept that issue for what it is and work around it, because I actually get different result for the Exists method when I check lColLen compared to a constant with the same value as lColLen. This is the state of the watch window right after the line Set dColLenFreqs = New Dictionary has been executed: How could this watch window be possible ever?

I have tried switching to late binding and fully qualifying my dictionary variable declarations and this did not fix the issue.

In case it's relevant, the purpose of the function is to take a dictionary input (dRowsInEachCol is the intended input, but I modified to create the dictionary within the function to eliminate this as the issue and shorten the code), and return a dictionary with the unique items in dRowsInEachCol as the keys, and the frequency of each unique item in dRowsInEachCol as the items.

I would like to know:

  1. Is this just a bug in the Scripting.Dictionary object, or am I missing something?
  2. If it is a bug in the Scripting.Dictionary object, what workarounds are available? I have read about mac-compatible dictionary classes like this one and this one, but I'm worried these would cause other glitches. Are these fears founded? Another idea would be to just switch to temporary excel ranges or collections and arrays, possibly wrapped in a class. I'm open to other suggestions as well.

This previous question also seems somewhat related to my issue and might be useful for reference.

Edit wrt Duplicate: This was marked as duplicate because of this question, and after reading the answer by Tim Williams, I can see why these questions are related, although the other question is different because it assumes Tim Williams's answer, and then asks why. I don't really have anything to add to my question to differentiate it further, and I agree that having a link to the other question is a helpful, but if someone is having the same issue as me and doesn't realize that it was caused by the watch window, they will be much more likely to find this question in their search results.

nateAtwork
  • 178
  • 1
  • 4
  • 14

1 Answers1

2

Can't replicate here - on the first pass though the loop all three Exists watch values are false.

What other watches do you have set though? It's possible another watch is altering the Exists result: you need to be careful with the watch window when using a Dictionary object.

E.g. see:

Dictionary is populated with an empty item after checking dictionary item in watch window

EDIT: as suspected, after adding a watch on 

dColLenFreqs.Item(lColLen)

the watch on 

dColLenFreqs.Exists(lColLen)

now outputs True.

If you delete all your watches and just look at the Debug output you'll also see it's as expected.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125