Excel VBA Dictionary vs Collection (2/4)

Another great video!


If I remember correctly, the Exists method of the Dictionary object adds the item if it does not exist. This was several years ago and I am not sure if this behavior is still true today.

So in a Dictionary, if you reference a key that does not exist, the code does NOT fall over? Do not like that behaviour at all!

how much data can a dictionary hold?

You can accessed to an item by its position in a dictionary with "dic.items()".

Nice.... This channel is picking up the pace... Hope to see more videos

Awesome !!!

Just awesome man!!!

Очень полезно.Благодарю!!!

Another thing to note is that VBA for the Mac doesn't support dictionary's, so if you need it to be compatible steer clear...enjoying your vids by the way, learning a few things I didn't know (especially the advanced filter stuff)

Useful knowledge..thank you sir

I like your videos. Now we know arrays, collections and dictionaries.

But now I'm interested in if it is possible two write a whole range into a collection or dictionary like we can do it via arrays without using any loops!

Maybe you can show us the options that we have to solve this problem. Because I think with dictionaries and collections it is not possible without using a loop :-).

Yeah and maybe an idea for a next video because you show us how circuitous it was to use dictionaries (references and activate Microsoft script). But there is more elegant way to do this (early binding vs. late binding). Then anyone can use your code even he isn't familiar with vba :-).

PS: sorry for my bad english and for done mistakes. But I'm from germany and english is not my native language :-)

Hi Paul, a couple of years ago you spoke of making a video about building an app that reads from access database (as seen in your book). I could not find the video, did you or are you intending of still making this video?

Hi Paul, thank you for all the information you share here and on your website. Very useful indeed. I've already learned so much from you and I'm excited to see what's next. Keep them coming!

Hii sir it is super duper 👍

Great video as always

Hi Master, Great Video as always

Hi Master, Great Video as always

Hi Sir, Im learned macros, i will write them using web resources but not my own at all, so for VBA devoloper job, is it necessary to write macro by their own, plz answer, thanks

Hi how can I contact you. Can you give me your mail ID please

Hi, I'm looking for a way to add multiple values to the same key. How is that possible? thanks

great job, the website is very clear and the videos very nice, I am learning a lot and there are also a great reference if I need to review anything in the future

Man you teach it very well! Thank you so much!

Thank you so much! Very good and understandable Videos!

Great video series! I wish to add a side note about a possibility I've found to actually access a Dictionary using the position instead of the key. It was useful in a test of mine when I was trying, unsuccessfully, to copy a Dictionary into a Range in a Sheet with a direct assignment. I was learning direct assignment from another video of you about assigning a Range into an Array, and assign back the Array into a Range, so I've thought that I could do the same by splitting the Dictionary into the 2 Arrays of Items and Keys. The assignment of the 2 Arrays back into 2 Ranges is not working (the first value of both Arrays is repeated in each Range Cell), but I could then experiment successfully that a loop through the Dictionary using a counter is possible. Here is the code that I've used to replace the direct assignment of a Dictionary into a Range:

Set rng = Sheet3.Range(Cells(1, 1), Cells(dict.Count, 2))
For i = 0 To dict.Count - 1
rng.Cells(i + 1, 1).Value = dict.Keys(i)
rng.Cells(i + 1, 2).Value = dict.Items(i)
Next

Maybe you would like to explore more this possibility and add it to another video about Dictionary. Thanks a lot for your clear explanations!

How many languages do you speak?

You can access the dictionary items by an index. Dict.keys(0) gives you the key of index position 0. Dict(Dict.keys(0)) gives you the value of index 0. If anyone has a shortened way to get the dictionary value by an index number please share. TIA.

This channel is excellent and deserves far more views

on point, thanks a lot :)

Which VBA data structured are hash table?

Hi everyone,


In this video, I show the differences between the collection and the dictionary. These are very similar but there are some subtle differences that it is vital to know.
Please enjoy and add any comments below.


Thank you.