Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

VBA Dictionary duplicating entries when using CStr for keys

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)
workaround

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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:
I hope your day goes well, traveler.

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):
Tim

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

>Solution :

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.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading