Excel VBA Dictionary vs Collection (2/4)

[@Excelmacromastery]

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.

[@Kralnor]

This channel is excellent and deserves far more views

[@wayneedmondson1065]

Hi Paul.. great video.. arrays, collections, dictionaries.. getting the hang of it. Just so I understand, if you want to create and use a dictionary with early binding, you add the Scripting Runtime Object Library and then you get Intellisense as you write your code. If you don't early bind, you can still use a dictionary by late binding with Dim dict as Object and then Set dict = CreateObject("Scripting.Dictionary"), but then you don't get Intellisense as you type. So, the takeaway is to early bind while you develop the code and then (likely) change to late binding when you distribute, so that your code does not break if run on a machine that does not have the the Scripting Runtime Library enabled.. which is likely on most end user systems? Also, you can't change a value in a collection.. so if you want to change a value is the procedure to delete the current collection item and either insert in the same place (coll.add before/after) or add (coll.add) an item with the new value back to the collection? This is all great stuff. I already created a handy little UDF by creating a collection and looping through the worksheet names, adding each to the collection. Then I can return them to the face of the worksheet with the UDF pointing to an incrementing index number in an adjacent cell or using ROWS() with an expanding range. I also did the same, but using an array instead of a collection. For the array, it will even SPILL vertically if using new/Insider EXCEL and using Application.Transpose(arr). Thanks again for all the useful and valuable resources you make available. There is gold in your web site and KZbin channel. All you have to do is prospect for it. Thumbs up!!

[@xn3ko]

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!

[@Excelmacromastery]

Thanks for the feedback. Plenty more on the way.

[@rrrprogram8667]

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

[@Excelmacromastery]

Plenty more on the way

[@aduenamz2569]

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

[@Excelmacromastery]

You're very welcome!

[@eduardom3016]

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

[@js-cx8wk]

Thank you so much! Very good and understandable Videos!

[@MrWarlls]

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

[@Excelmacromastery]

Unfortunately that is only available for Early Binding. It's not available with Late Binding.

[@mosesmoise9897]

Useful knowledge..thank you sir

[@Excelmacromastery]

You're welcome.

[@vaol8503]

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!

[@hamidoudiarra9172]

Hi Master, Great Video as always

[@Excelmacromastery]

You're welcome.

[@WynSee]

Great video as always

[@Excelmacromastery]

Thanks Wyn

[@chrish281]

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)

[@Excelmacromastery]

Glad you are enjoying the videos. That's true about the Mac. There are some alternative Dictionary classes available for Mac users. I haven't used them myself.

[@rayhanrana6773]

Just awesome man!!!

[@Excelmacromastery]

Thanks Rayhan

[@vijaysahal4556]

Hii sir it is super duper 👍

[@Excelmacromastery]

Thanks Vijay

[@logic3686]

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.

[@Dyxacm]

dict.items(0)

[@logic3686]

@@Dyxacm It worked! I thought I tried that and it errored. I guess there must have been something else wrong with my code. Thank you!

[@Олег-п5и8щ]

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

[@Excelmacromastery]

You're welcome

[@slebans]

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.

[@MrWarlls]

The problem has been fixed. If you test the presence of an item in a dictionary, it is not added.

[@alexhoch1264]

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 :-)

[@Excelmacromastery]

Thanks for your comment. You need a loop for the Collection and the Dictionary. There is no other way. The reason is that they are linked lists in memory. An array is a block of memory so it is easy to do a bulk copy.

[@PrincePedia]

Awesome !!!

[@Excelmacromastery]

Thanks!

[@s1ngularityxd64]

on point, thanks a lot :)

[@amarnadhg6896]

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

[@Excelmacromastery]

sorry but I'm don't understand.

[@robin810104]

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?

[@Excelmacromastery]

Hi Robin. I did a webinar on that topic. It is in the membership section of my website. Go to excelmacromastery.com/live-webinars/ and press ctrl+F and enter "Access" and you will see the link. You need to be a member to view the video.

[@houstonvanhoy7767]

This is part 2 of a 4-part series. Part 1 is here: kzbin.info/www/bejne/hqOqlJt6nKtmo5Y

[@innerthreatcircus5651]

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

[@kumarprabhu]

Hi, you can store the multiple values as an array variant, or collection or as on object i mean as a single entity and store either of this itself as an item in dictionary.

[@krzysztof6333]

Which VBA data structured are hash table?

[@philipdru4782]

how much data can a dictionary hold?

[@Excelmacromastery]

Like all the Data Structure it has to do with how much memory is available.

[@AI-ec2qb]

How many languages do you speak?

[@albertbatfinder5240]

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!

[@Excelmacromastery]

I've never had a problem with it and I use it a lot. You can still use "Exists" if you prefer to check first.

[@albertbatfinder5240]

But isn’t it a bit like NOT using Option Explicit? You misspell a variable (or in this case a key) and instead of the program halting, it just roars on. You are not likely to use Exists, because, frankly, misspellings are not part of logic.

[@hamidoudiarra9172]

Hi Master, Great Video as always

[@Excelmacromastery]

Glad you like it.