VBA Scripting.Dictionary Code Late Binding Error

Hi,

I am learning how to use Scripting.Dictionary currently.

This is the request I was trying to solve (File Attached)

http://www.excelforum.com/excel-prog...-mail-ids.html

And I came up with this code.

Please Login or Register  to view this content.

The Code works fine when early bound, however when I made it Late Bound, it fails on the For Counter = 1 To Dict.Count line.

Dict.Count somehow shows no value.

Can any one tell me what I am doing wrong, and also would like some feedback on the code and improvement suggestions.

Hi Kyle..

I was trying to loop using the keys method only, however I could not achieve what I was trying to do..

Probably the way I'v designed the code, maybe using Index was required, not sure.

@Jindon - That works.. But I do not understand why would this line Cells(Counter + 1, "C").Value = Dict.Items(Counter - 1) work when early bound and not when late bound.. Any specific reason?

@Kyle - So isn't the code I'v used a good approach to using dictionaries? If no, could you suggest the right way?

Here's a site I like for dictionaries.
Maybe you are already familiar with it.

Thank you skywriter.

I will check it out..

@ Jindon, Just 1 last question for you..

In the original post, what does If E <> "" Then .Item(E) = Empty really do?

Also, I noticed the use of #1, would like to know its significance aswell..

I could probably help you with this and jindon will correct me if I'm wrong.

If you go to that website I linked for you, then you will find out one of the interesting things about dictionaries is that depending on the way you enter items into it you can use it to create a unique list.
With e he's looping through the list of email addresses, and he's checking whether they are empty or not <>"".
By using the .item(e) which is this (.item(key)=actual item), he's adding an empty item to the dictionary with the key being (e), e is the email address. If you use this method and a key already exists the dictionary just ignores it instead of giving you an error. He is using the keys (e) to build a unique list of email addresses and since he's not really going to use the items just the keys he adds nothing as an item. This also is allowed by a dictionary, it causes no error. Then with the join and .keys he's dumping those email addresses into the file he opened. Pretty sure on that part, but I've never done it, it's just a deduction from the Microsoft site I sent you a link to below and the OP request on that post.
See 6.2.2 method .Item()=, on the page I linked you to in an earlier post.

I hope I explained that clearly.

The number #1 is part of the syntax required with the open statement.
You can find some info on that here.

Good Luck.

Thank you skywriter for a detailed explanation. It was fantastic..

You clarified many things which were as doubts in my head..

However, this still confuses me - If e <> "" Then .Item(e) = Empty

I mean, why does the code set the value of .Item(e) to empty when e indeed has a value (<> "")

Should it not be the other way round? It is very counter intuitive..

You're already using e as the key so there's no need to store it as the item too - you can retrieve all the email addresses by iterating through the keys.

Originally Posted by romperstomper

You're already using e as the key so there's no need to store it as the item too - you can retrieve all the email addresses by iterating through the keys.

Now I get it..

So basically, In .Item(e) 'e' is the Key, and if that key is not blank, we are setting the Value Property of that particular Key to Empty, which really does not matter as we aren't using the value anywhere..

Am I right?

So a dictionary has items and keys and the way to access the item is through the key. I went back and read up a little more on dictionaries to refresh myself. I misspoke in my earlier post when I said the dictionary ignores the duplicate key, this is actually a method to replace the item associated with the key.
When you have an existing item in the dictionary if you want to replace it you access it through the key.

So If I add an item using this method .item(key)= "Test" and I want to access the item which is the word "Test" I have to access it through the key. Now if I want to replace the item I use the same method .item(key)="New Test".

This particular method for adding an item to a dictionary will not cause an error if the key already exists, it will just replace the item with the new item.

We aren't interested in what the items are because we only want to make sure we have a unique set of keys, in the post jindon was involved in he had a list of duplicate email addresses and he wanted to create a unique list. He does that by adding items, yes I know the item is empty, but we can add an empty item because when we are done we are only interested in using the keys, which are the email addresses.

So we have a list of email addresses.
joe@yahoo
bill@yahoo
fred@yahoo
joe@yahoo
bill@yahoo

and we only want a list of unique email addresses
So we loop the email addresses and use them as keys while adding items to the dictionary. We aren't interested in the items, they don't matter, we want the keys.
So we loop through the list of email addresses and add
.item(bill@yahoo)=empty
bill@yahoo is the key the item is empty.
when we get to bill@yahoo again then what we are doing is replacing the item associated with the key(bill@yahoo) with a new item, but it's also empty, but what we really did is keep from ending up with a duplicate email address because keys in a dictionary are unique. It's a trick that allows you to loop through a list of names, numbers, dates, whatever and only end up with a unique list.
If you go to that site that I sent you then you will see that if you use a different method to add a new key and item to a dictionary and end up trying to add another item with a key that already exists you get an error. With the method jindon used you get no error and end up with a list of unique keys. Then you can extract the keys and there you go, a unique list.

Perfect!

I was actually referring to the same site previously, so I could understand what exactly you meant..

Thank you for the clarification, I was a BIG help to me..

Marking the thread as solved..

Haha! No problem..

Sorry guys, just one more question..

So, are these 2 lines identical?

If E <> "" Then .Item(E) = Empty

If E <> "" Then .Item(E) = ""

Without any Advantages/Dis-Advantages?

Alright great!

Thank you for the clarification Kyle..