VBA for-loop adding blank dictionary entries

Question

I'm trying to replace some values in an Excel worksheet by using a VBA dictionary. When I did this last year, it worked perfectly. Upon trying it again it is adding blank values to duplicate keys, and not writing anything to the cells as a result.

I've tried both early binding and late binding with no success.

Here is the code with late binding.

Sub someDict()

Dim myDict As Object
Set myDict = CreateObject("Scripting.Dictionary")
Dim n As Long
Dim i As Long

myDict.Add "A", "Amaretto Sour"
myDict.Add "B", "Bourbon"
myDict.Add "C", "Cosmopolitan"
myDict.Add "D", "Daiquiri"
myDict.Add "E", "Electric Lemonade"
myDict.Add "F", "Four Horsemen"
myDict.Add "G", "Gin and Tonic"
myDict.Add "H", "Hurricane"
myDict.Add "I", "Irish Coffee"
myDict.Add "J", "John Collins"

Debug.Print "Total: " & myDict.Count()

n = Cells(Rows.Count, 1).End(xlUp).Row
For i = n To 1 Step -1
    If Cells(i, 3) = "TEST" Then
        Debug.Print myDict.Count()
        Cells(i, 5) = myDict(Cells(i, 4))
        Debug.Print myDict.Count()
    End If
Next i

Debug.Print "Total after loop: " & myDict.Count()

Dim k As Variant
For Each k In myDict.Keys
    ' Print key and value
    Debug.Print k, myDict(k)
Next

End Sub

Here is the table structure:

The current output in the immediate is:

Total: 10
 10 
 11 
 11 
 12 
 12 
 13 
 13 
 14 
 14 
 15 
 15 
 16 
 16 
 17 
Total after loop: 17
A             Amaretto Sour
B             Bourbon
C             Cosmopolitan
D             Daiquiri
E             Electric Lemonade
F             Four Horsemen
G             Gin and Tonic
H             Hurricane
I             Irish Coffee
J             John Collins
J             
H             
G             
F             
E             
C             
A     

I would've expected it to be:

Total: 10
 10 
 10 
 10 
 10 
 10 
 10 
 10 
 10 
 10 
 10 
 10 
 10 
 10 
 10 
Total after loop: 10
A             Amaretto Sour
B             Bourbon
C             Cosmopolitan
D             Daiquiri
E             Electric Lemonade
F             Four Horsemen
G             Gin and Tonic
H             Hurricane
I             Irish Coffee
J             John Collins

Can anyone tell me what I am doing wrong (aside from using a VBA dictionary), and further to that, why it has previously worked for me?

Could it be due to an update to scrobj.dll?

Thanks.

Answer 1

Dictionaries actually use variants for keys rather than strictly strings or numbers, which is why when looping through keys you have to define k as a variant. A key can actually be anything.

Also, dictionaries add new keys spontaneously if you refer to a key that isn't already defined, which is why you have to use the safe function Exists to test whether a key has been defined.

VBA, being the undisciplined beast that it is, tries to be helpful in determining what you want when you refer to a Range() object in code. If a range object can be used, then it will use the range object. If a string/number is expected, then it will use the value of the Range() object instead (being the default property).

Because dictionaries can take any object as a key, rather than using the value in Cells(i, 4), it uses the actual range object of Cells(i, 4) and creates a new key with no value. You can see this in the Locals windows, which shows the sub-types of the variant dictionary keys:

When you debug.print keys at the end, the print function expects a string/number. So when it reaches the "duplicate" keys, which are actually range objects, it instead prints the value of the range object.

In stepping through the code, you can actually get any range properties of those keys in the immediate window instead! For example: ?k.Address

To fix your code, you just need to explicitly refer to the value of the cell:

    Cells(i, 5) = myDict(Cells(i, 4).Value)

This is why the best coding practice is to always explicitly refer to properties of objects, rather than relying on default properties.