VBA error 457 key when adding to dictionary

Question

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 ?

Error 457 is absolutely normal. You should wrap adding a key with On Error Resume Next. It means key already exists. That is why one uses a dictionary. Why are you using one. — user18521918
– user18521918, Commented Jun 15, 2022 at 8:39
Use the Exists method to check if a key exists before adding it. Returns a boolean. — Kostas K.
– Kostas K., Commented Jun 15, 2022 at 9:02
Be careful when you split your values: Right(cle, 7) will give you the rightmost 7 characters, no matter how long the string in cle is. I recommend to use mid(cle, 8), that will give you the rest of your string in all cases — FunThomas
– FunThomas, Commented Jun 15, 2022 at 9:07
How did you load keys? It would be good to show that part, too. Then, Find needs more parameters to be sure that previously it has not been used with something unappropriated. Are the keys you search for unique in that specific column? In fact, its first 7 digits... Anyhow, Find will always return the first occurrence. Does cle all the time has 14 digits? — FaneDuru
– FaneDuru, Commented Jun 15, 2022 at 9:07
A string as "abcdefg1234567" will try creating the same "abcdefg" for "abcdefg2222222", and it may exist... — FaneDuru
– FaneDuru, Commented Jun 15, 2022 at 9:13

Vityata · Accepted Answer · 2022-06-15 09:21:31Z

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().

user18521918 · Accepted Answer · 2022-06-16 06:38:50Z

0

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.

edited Jun 16, 2022 at 6:38

answered Jun 16, 2022 at 5:39

user18521918

1121 gold badge1 silver badge3 bronze badges

1 Comment

Vityata Over a year ago

Writing in the most nicely way (no irony here) - the difference between 2 and 4 function calls, multiplied by the number of items is close to nothing in the long term - take a look at these articles - google.com/search?q=big+o+notation

Collectives™ on Stack Overflow

VBA error 457 key when adding to dictionary

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related