VBA learning how to use Dictionary. Help please.

[dreid1011]

Good morning,

As the title implies, I am learning how to use the dictionary object. And in doing so, my end goal is to count the number of unique entries in a 2d array with multiple criteria so that I can redim an output array to the appropriate size.

With that being said, please do not just give me the code to solve the whole problem, as that won't really help me learn.

Okay, the current issue at hand is the code below, and I am receiving an error stating that the key I am trying to add to the dictionary is already associated with it. I am watching the value of 'i' and it is at 1 when this happens. So, being that the dictionary is empty at this time, how can it already have a key associated with it? Debug highlights the line in red.

arrIn(i, 1) holds an account number. arrIn(i, 3) holds square footage.

Sub QDelRows()
Dim dict As New Scripting.Dictionary
Dim arrIn() As Variant, arrOut() As Variant
Dim i As Long, j As Long, k As Long
arrIn = Range("A2:E11")
For i = LBound(arrIn, 1) To UBound(arrIn, 1)
    If dict.Exists(arrIn(i, 1)) And dict(arrIn(i, 1)) <> arrIn(i, 3) Then
        dict.Add arrIn(i, 1) & "a", arrIn(i, 3)
    Else
        dict.Add arrIn(i, 1), arrIn(i, 3)
    End If
Next i
End Sub

 

[Eric W]

Dictionary objects have some neat features, but they can do some unexpected things. Here's your issue:

If [COLOR=#0000ff]dict.Exists(arrIn(i, 1))[/COLOR] And [COLOR=#ff0000]dict(arrIn(i, 1)) <> arrIn(i, 3)[/COLOR] Then

The part in blue is OK, it checks for whether the key exists. The problem is in red. When you reference a key like this:

dict(key)

it checks for the item associated with the key, but IF THE KEY DOES NOT EXIST, it creates it! Lots of ways to use that to your advantage, but not in this case. You need to rewrite your If like this:

If dict.Exists(arrIn(i, 1)) Then
    If dict(arrIn(i, 1)) <> arrIn(i, 3) Then

because another quirk of VBA is that if you have multiple conditions in an IF, ALL of them are evaluated. VBA doesn't quit evaluating the IF when it finds something that doesn't match.

Hope this helps!

 

[dreid1011]

it checks for the item associated with the key, but IF THE KEY DOES NOT EXIST, it creates it!

That does help! Thank you. I even read about that and conveniently forgot it.

 

[Fennek]

Hello,

here is a tutorial: www.snb-vba.eu

regrads

 

[dreid1011]

Hello,

here is a tutorial: www.snb-vba.eu

regrads

Thank you. I've been referencing this: https://excelmacromastery.com/vba-dictionary/.

 

[dreid1011]

@Eric W After I made the suggested change, the code seems to run ok with no errors. However, I've tried multiple methods of checking the values in the dictionary, but it doesn't feel like anything is actually being added.

Edit: Using MsgBox dict.Count after the loop reveals there is nothing in the dictionary.

 

[Eric W]

What does your current code look like?

 

[Rick Rothstein]

Hello,

here is a tutorial: www.snb-vba.eu

This is a better link about the Dictionary object on snb's website...

http://www.snb-vba.eu/VBA_Dictionary_en.html

There is also this link to the companion ArrayList object there...

http://www.snb-vba.eu/VBA_Arraylist_en.html

And let's round things up with a link for Collections...

http://www.snb-vba.eu/VBA_Collection_en.html

 

[dreid1011]

What does your current code look like?

I just realized why too. Here is the code at the moment:

Sub QDelRows()
Dim dict As New Scripting.Dictionary
Dim arrIn() As Variant, arrOut() As Variant
Dim i As Long, j As Long, k As Long
Dim m As Variant
arrIn = Range("A2:E11")
For i = LBound(arrIn, 1) To UBound(arrIn, 1)
    If dict.Exists(arrIn(i, 1)) Then
        If dict(arrIn(i, 1)) <> arrIn(i, 3) Then
            MsgBox "a"
            dict.Add arrIn(i, 1) & "a", arrIn(i, 3)
        Else
            MsgBox "b"
            dict.Add Key:=arrIn(i, 1), Item:=arrIn(i, 3)
        End If
    End If
Next I
End Sub

I need to move the Else portion. It's hitting the first If's False result (which doesn't exist) and moving on to the end. Doh...

This is a better link about the Dictionary object on snb's website...

http://www.snb-vba.eu/VBA_Dictionary_en.html

There is also this link to the companion ArrayList object there...

http://www.snb-vba.eu/VBA_Arraylist_en.html

And let's round things up with a link for Collections...

http://www.snb-vba.eu/VBA_Collection_en.html

Thank you, I will look at these.

 

[dreid1011]

Adjusted For loop, and dict.Count is now returning the expected results.

For i = LBound(arrIn, 1) To UBound(arrIn, 1)
    If dict.Exists(arrIn(i, 1)) Then
        If dict(arrIn(i, 1)) <> arrIn(i, 3) Then
            MsgBox "a"
            dict.Add arrIn(i, 1) & "a", arrIn(i, 3)
        End If
    Else
        MsgBox "b"
        dict.Add Key:=arrIn(i, 1), Item:=arrIn(i, 3)
    End If
Next I