CODEWITHSUNDEEP
vbaexceldictionary
CodingBeagle
CodingBeagle

Reputation: 1950

VBA - Is it safe to remove items from a dictionary within a for each?

So, I'm completely inexperienced with VBA (Visual Basic For Applications).

Basically, I have code that runs through dictionaries and removes certain elements from them. Here's a quick examples (that is NOT in the production code):

Dim testDict As New Scripting.Dictionary

testDict.Add 1, 5
testDict.Add 2, 10
testDict.Add 3, 15
testDict.Add 4, 20
testDict.Add 5, 25
testDict.Add 6, 30

Dim Key As Variant

For Each Key In testDict.Keys()
    If testDict.Item(Key) = 5 Then
        testDict.Remove (Key)
    End If

    If testDict.Item(Key) = 20 Then
        testDict.Remove (Key)
    End If
Next Key

I know that in some languages, this will throw off the underlying iterator. However I have no experience in Visual Basic, so I don't know how it works.

The reason I'm asking is that the above code works perfectly fine, and the algorithm which uses this approach works just fine too. I just need to have verified that this is a safe practice in VBA, and if not, how are cases like these usually dealt with in VBA?

Upvotes: 1

Views: 2133

Answers (2)

L ogic
L ogic

Reputation: 1

In the first IF check of key value, he doesn't need to check if it exists because the key variable is being filled in through the loop. It has no other choice but to exist. However in the second IF check, he would need to check if exists because the key could have been removed from the dictionary in the first IF check. Besides this, as far as I can tell the code works fine.

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166351

The last line illustrates the problem with this approach.

Just referring to testDict.Item(Key) will create a key if it doesn't exist, so you should always use testDict.Exists(Key) before trying to check the value of a key which may not be present.

As for making deletions while looping over the keys, if you want to be sure then start by grabbing a copy of the keys and loop over that...

Added: if you add an item inside the loop it will not show up as a value of Key variable.

Sub TT()

    Dim testDict As New Scripting.Dictionary

    testDict.Add 1, 5
    testDict.Add 2, 10
    testDict.Add 3, 15
    testDict.Add 4, 20
    testDict.Add 5, 25
    testDict.Add 6, 30

    Dim Key As Variant

    For Each Key In testDict.Keys()
        If testDict.Item(Key) = 5 Then
            testDict.Remove (Key)
            Debug.Print "Removed ", Key
        End If

        If testDict.Item(Key) = 20 Then
            testDict.Remove (Key)
            Debug.Print "Removed ", Key
        End If
    Next Key
    '>>> Removed        1 
    '>>> Removed        4 


    Debug.Print Join(testDict.Keys, ",")
     '>>>  2,3,5,6,1

End Sub

Upvotes: 2

Related Questions