VBA Dictionary duplicating entries when using CStr for keys

Question

While trying to fix a broken code with arrays and dictionaries, I came upon something weird when filling the dictionary (I have since used a different workaround) but was wondering what's causing the "duplication" of keys in the dictionary:

Current Workaround:

    Dim lvbData()
    lvbData = wsHeadH.Range("A1:C" & lRowHeadH).Value 'to build the keys
    lRow = wsData.Range("B" & Rows.Count).End(xlUp).Row
    
    Dim jLev()
    jLev = wsData.Range("B2:D" & lRow).Value 'B & D again create same keys from different table
    Dim dep()
    ReDim dep(1 To UBound(jLev, 1))
    
    Dim keys()
    keys = wsHeadH.Range("D1:D" & lRowHeadH).Value2 'these have the same values as the A & C together 
    'which definitely are unique together (how our system is built)
    'Dim dict As Object
    Dim dict As Dictionary
    Set dict = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(lvbData, 1)
        dict.Add keys(i, 1), lvbData(i, 3) 'adding keys with the desired value
        Debug.Print dict(keys(i, 1)) 'shows the correct values
    Next i
    Dim tDbl As Double
    For i = 1 To UBound(jLev, 1) 
        tDbl = jLev(i, 1) & jLev(i, 3) 'this is the workaround
        dep(i) = dict(tDbl) 'gives the desired result
        Debug.Print dict(jLev(i, 1) & jLev(i, 3)) 'shows up empty, both jLev's have Variant/Double  
   'just like the keys do
    Next i

(I'm aware this is a bit convoluted but I wanted to train working with arrays, could've just done a simple XLOOKUP) I have no idea why after the second loop (of filling dep) the dict.Count gives 533 as I have 267 keys/values in there (before filling dep, the count does state 267)

Old workaround:
Now with the first attempted fix (I switched to the one above since I noticed the weird "visual" error in VBE without checking the results of the dep which were actually correct):

    For i = 1 To UBound(lvbData, 1)
        dict.Add CStr(keys(i, 1)), lvbData(i, 3)
        Debug.Print dict(keys(i, 1))
    Next i
    Dim tDbl As Double
    For i = 1 To UBound(jLev, 1)
        'tDbl = jLev(i, 1) & jLev(i, 3)
        dep(i) = dict(CStr(jLev(i, 1) & jLev(i, 3)))
        Debug.Print dict(jLev(i, 1) & jLev(i, 3))
    Next i

and the properties screen:

Which shows one more count for whatever reason, and this is after only the first loop (filling of the dict. I don't quite understand why the count is so fluid for something that according to the documentation says it should give the count for the items in the collection which should be only the keys? and if it is supposedly the keys+values, then why would it only show 267 in the first loop with the workaround I went with?

Second part of the question, why does the VBE show the key as string and then as double?

Edit for sourcecodes:
source code where jLev is based on (imagine column A not being filled in yet):

source code where lvbData is based on (for the keys)

FYI it's pretty difficult (for me anyway) to review code where multiple statements are stacked in a single line using `:` - I typically gloss over lines beginning with `Dim` but if you put an assignment on that same line then I have to double-check every line for "extra" pieces of code... — Tim Williams, Mar 30 '23 at 15:22
I'll keep that in mind for future references, didn't feel it needed to separate them since it's just the value of a range being put in an array @TimWilliams (edited it) For ss of the source data you'll have to wait a few, I'm doing a full testrun and the refresh of the SQL is a wee bit slow) — Notus_Panda, Mar 30 '23 at 15:26
FYI just using a watch on a dictionary can "auto create" keys directly, without you needing to add them via code. It's best to avoid putting your dictionary in a watch, or you're never sure of what you see... Eg see https://stackoverflow.com/questions/48437731/dictionary-is-populated-with-an-empty-item-after-checking-dictionary-item-in-wat or https://stackoverflow.com/a/41858402/478884 — Tim Williams, Mar 30 '23 at 15:27
No - possibly it's because you have put a Watch on `dict`. EDIT: yes - maybe also the debug.print... — Tim Williams, Mar 30 '23 at 15:30
I may have heard about Watch once before but that's buried somewhere so unless you can't accidentally put a watch on it, how do I check if that's a thing? — Notus_Panda, Mar 30 '23 at 15:32
Check the key exists before debug.print `k = jLev(i, 1) & jLev(i, 3): If dict.exists(k) Then Debug.Print dict(k)` — CDP1802, Mar 30 '23 at 15:41

score 5 · Accepted Answer · answered Mar 30 '23 at 15:39

You have to be careful with dictionaries when using either Watch or Debug.Print. Both can create keys automatically. Eg for Debug.Print:

Sub Tester()
    Dim dict As Object
    Set dict = CreateObject("scripting.dictionary")
    
    dict.Add "10", "B"  'string key
    Debug.Print "10", dict("10")              '> 10       B
    Debug.Print "Count", dict.Count           '> Count    1
    
    Debug.Print 10, dict(10)                  '> 10
    Debug.Print "Count", dict.Count           '> Count    2
End Sub

A key of 10 is distinct from the existing key "10" so the debug.print actually adds a new key. Best to check using Exists() before trying to access the key's value.

So by trying to fix an issue (the `dep()` not getting filled in), I created a new one by using Debug.Print since I didn't feel the need to check for existing keys (with the keys being unique in the range/array). Thanks for the clarification on that. I'll be sure to put a check on existing regardless and be more thorough with my key assigning. — Notus_Panda, Mar 30 '23 at 15:46

VBA Dictionary duplicating entries when using CStr for keys

1 Answers1