CODEWITHSUNDEEP
excelvba
Jia Hannah
Jia Hannah

Reputation: 95

VBA error 457 key when adding to dictionary

I have an array of combinations called "Keys". I want to check if each combination exists in the column I, and if so, split the existing combination in 2 strings and add them as a pair in my dictionary "combiExist". My code throws a 457 error

Dim combiExist As Object
Set combiExist = CreateObject("Scripting.Dictionary")

For Each cle In keys
'If combination exists in my range
    If Not .Range("I:I").Find(cle) Is Nothing Then
    
        'Split string from 7th position, left part is the key, right part is the value
        combiExist.Add Left(cle, 7), Right(cle, 7)
        
    End If
Next

How can I solve this ?

Upvotes: 1

Views: 578

Answers (2)

user18521918
user18521918

Reputation: 112

Beware of voodoo programming

Do
    If Inp.AtEndOfStream = True then exit do
        Line=Inp.readline
            On Error Resume Next
            Dict.Add(Line, "")
            If err.number = 457 then err.clear
            On Error Goto 0
    Loop

This is the way one programs. One does and handles it.

The answers given using .exists generates needless function calls. All methods and properties are indirect function calls under the hood. That means stack setup and tear down. There will be a minimum of one function per item, if it's duplicated. But there will be two function calls for unique items.

Testing return values it is only one function per item.

Also remember every . is also a function call.

Remember COM methods/properties look like this

Err.Number = MethodName(Param1, ..., ReturnValue)

Err.Number, called an HResult in COM, returns information about your call.

Embrace errors.

What is on the stack.

The return address, the return value, any parameters (in or out), and all local variables.

In the other answer there is a minimum of 3 function calls or 4 if it does not exists.

My Code is 2 if it exists or not. That is multiplied by the number of items.

Upvotes: 0

Vityata
Vityata

Reputation: 43595

Error 457 says that the key is already associated with an element of the collection. So, before assigning it to the dictionary make sure it is not there, with .Exists. This one works rather ok, at the end it prints the dictionary:

Sub Main()
    
    Dim combiExist As Object
    Set combiExist = CreateObject("Scripting.Dictionary")
    Dim combinations As Variant
    combinations = Array("joeC12345678910", "C12345678910", "foooooo123")
    Dim cle As Variant
    
    For Each cle In combinations
        If Not Worksheets(1).Range("I:I").Find(cle) Is Nothing Then
            
            If combiExist.exists(Left(cle, 7)) Then
                Debug.Print "Do nothing this one " & (Left(cle, 7)) & " exists!"
            Else
                combiExist.Add Left(cle, 7), Right(cle, 7)
            End If
        End If
    Next

    PrintDictionary combiExist
    
End Sub

Public Sub PrintDictionary(myDict As Object)
    
    Dim key     As Variant
    For Each key In myDict.keys
        Debug.Print key; "-->"; myDict(key)
    Next key
    
End Sub

In general, do not use words like Keys for a variable name, because this one means something in VBA - usually a collection of the keys of a given dictionary. You can see the implementation of myDict.keys in PrintDictionary().

Upvotes: 2

Related Questions