Question posted 2014 · +5 upvotes
I am working on vba macros. I was trying to use a dictionary. But it is giving error 457 with debugger pointing to toprow.Add ActiveCell.value, val. Can anyone please tell the issue? I even used Cstr(activecell.value), Cstr(val) as mentioned in one of the answer on similar issue.
Dim toprow As New Dictionary, Dictkey As Variant
Dim val As String
Range("A1").Activate
i = 0
Do Until i = ColLen
val = Chr(65 + i)
toprow.Add ActiveCell.value, val
i = i + 1
ActiveCell.Offset(0, 1).Activate
Loop
Accepted answer +12 upvotes
Adding keys with dictionaries is only possible when a key does not already exist. Accidentally you could entered the key before, or you are watching the key with the debug watcher, creating the key instanteneously. (= If you watch a certain key in a dictionary it gets created if it doesn’t already exist).
You have to
- make sure you are not watching the key with the debugger
- create unique entries by testing on
d.Exists(keyname)and then use thed.Add keyname, valuemethod - alternatively you can default to overwrite existing keys by using
d.Item(keyname) = value
Top excel-vba Q&A (6)
- How to clear the entire array? +58 (2010)
- How to change Format of a Cell to Text using VBA +55 (2011)
- Download attachment from Outlook and Open in Excel +43 (2012)
- Can a VBA function in Excel return a range? +36 (2009)
- 2 Dimensional array from range +34 (2013)
- Hiding an Excel worksheet with VBA +33 (2009)
excel-vba solutions on this site
.