Best excel advice and vids. Perfectly solves a problem trying to count duplicates of a list where I don’t want to sort the list, it has to process in order. I have a SKU name I pull from JSON data and a file i end up downloading where the download is named SKU name for first then SKU name-1 for the second SKU name -2 so on. This allows me to add the SKU as the key, value is duplicateCount starting at 0. If the SKU name hasn’t shown up yet add it to dictionary, Otherwise increment up duplicateCount If duplicateCount is 0 then keep SKU name as SKU, otherwise SKU-duplicateCount
Wow you have a different approach of using and explaining Dictionary. I have been using this for quite sometime but explaining Item like a Value makes it more understandable. I want to learn how to use API and i though your guidance will bring me there. Thank you so much.
I am very grateful to your videos and am learning a lot. I opened the Watch Window and added dict("Apple") to it as shown in video. The Code, the Watch Window, and the Immediate Window all work fine as I Step through (F8) the Code. However; when I then add dict("Orange") to the Watch Window and step through with F8, I get the Error Message that "This Key is already associated with an element of this Collection". But... if I use F5 to simply run the code, everything runs and the Immediate Window prints out all the correct Values. What am I missing?
This is actually a bug with the Dictionary. If you add an item in the watch window it gets added to the Dictionary. It caught me out a few times before I discovered it.
Very useful video ! I was surprised by following the intellisense dict.Add(“Apple”,60) does not work but if I write dict.Add “Apple”,60 then it's ok The intellisense misled me. What is the difference between the 2 writings? Thanks ...
Hi Paul.. great intro to using a Dictionary.. looking forward to more. I see below you've mentioned about early and late binding. So, if you are going to distribute your code to users who might not have a reference to the Scripting Runtime, then you early bind while you write the code (for Intellisense) and then modify the code to late bind (Dim dict and Set dict=CreateObject("Scripting.Dictionary")) before distribution? That seems like the logical approach..yes? Thanks for the inspiration to think and create. Thumbs up!
@@Excelmacromastery Thanks Paul.. loving your videos and UA-cam channel. BTW, on your channel, take a look at the View Full Playlist for Excel VBA Collections. It looks like someone inserted a 6th video into the list.. not related to VBA.. maybe some kind of a hack.. don't want to click on it.. but it definitely looks like it does not belong there. Thought you would want to know and delete it or report it to UA-cam. Thanks again!
Hi, great video. I ran into a problem I'm hoping you can help with. I added the Microsoft Scripting Runtime reference and created my dictionary variable the same way you did. However, the "Add" method is not available for my dictionary, why would that be?
Hi Paul - you need to edit your above video description... "In this Excel VBA video, we see how to use Class modules objects with Collections. One of the problems with collections is that it can only store one item from a row of data. So how to we store data from multiple columns? We use class module objects. "
I thank you for explaining this so well as I've wondered for years what it did or was. But, I'm puzzled as to where I might use it. For example, I have a price list with 3,000+ items on it that I use repeatedly. I know the item number (key) so I've just been inputting that in the Excel Find option. Is there maybe some better way to use the dictionary to do this?
Good to hear from you Bob. If you have a low frequency of finds e.g. manually finding the Excel Find is fine. For a high frequency though, the Excel Find is slow. For example, imagine you had a second list of 1000 items and you had to look up these in the first list. Then the dictionary would be very useful. You can see some examples at the bottom of this post: Excel VBA Dictionary(excelmacromastery.com/vba-dictionary) Show less
One major benefit of using a Dictionary is that lookups take constant time. That is, it doesn't matter how large the Dictionary is, looking up a certain key will always be practically instant. This is unlike arrays where you need to scan through the array.
Is there a method to apply a Like comparison to the value in the dictionary i.e. Dict item is "Shower" but like comparison would be Shower1 or Shower 2 etc - I have tried with no success adding wildcard to the dict item
My dictionary object always has one empty item in it. Also, I cannot remove that "Item 1" using : If Dict.Exists(" ") Then Dict.Remove " " ....... What is the solution?
@@Excelmacromastery no worries. Look forward. Hope you stick to the vids. Been enjoying them. Easier to watch then some of the other drawn out vids on here.👍
Your dictionary value probably ends up being a variant array or another collection or dictionary depending on use case. That kinda how my nested Jsons end up working
How can I write a formula in an excel cell to extract 1 item from the dictionary by using the key? I would think you you write something like this in a cell =(DD.name,key,position) then Apple would be in one cell.
Hi Paul, I used late binding but I am getting object can't be created error message in office 365. Is there any solution? I can't use early binding bcz I need to distribute the Xlam file to my team.
Hello Sir, If I have this kind of data how can I do it using Dictionary: Column Headers P1 with phone numbers P1 Type : Cell or Landline or Voip P1 Old: Yes or No Phone1, P1 Type, P1 Old, Phone2, P1 Type, P2 Old up to Phone10 Output Sheet1 Delete rows with P type: Voip Delete values with Type: Cell and Old: Yes Output Sheet2 From the original sheet Retain only Type: Cell and Old: Yes Then: if column Phone1, type and old has blank records cut and paste from Phone2, type2, old2 and so on to fill in blanks Can this be achieve using dictionary Sir?
![Lp. Сердце Вселенной #60 РОЖДЕНИЕ ЛОЛОЛОШКИ [Финал] • Майнкрафт](http://i.ytimg.com/vi/YoR0pAV9FVQ/mqdefault.jpg)
Hi everyone. Please enjoy the first video about VBA Dictionary. If you have any comments, please add them below.
Your did a very good job, thank you so much for the clear explanations.
Best excel advice and vids.
Perfectly solves a problem trying to count duplicates of a list where I don’t want to sort the list, it has to process in order.
I have a SKU name I pull from JSON data and a file i end up downloading where the download is named SKU name for first then SKU name-1 for the second SKU name -2 so on.
This allows me to add the SKU as the key, value is duplicateCount starting at 0.
If the SKU name hasn’t shown up yet add it to dictionary, Otherwise increment up duplicateCount
If duplicateCount is 0 then keep SKU name as SKU, otherwise SKU-duplicateCount
Please do an example on mapping data using dictionary and arrays
So simple but have so many possibilities.
Thanks ☺️
Your explanation is trule comprehensive. Thank you Paul!
Thanks Trinh
muchas gracias por la clase! Muy claro todo!!
Very well explained...and I can see there will be plenty of uses so looking forward to the rest of the series
Thank u so much Paul.. I am getting your emails every day.. your channel is very useful .. very nice wonderful great video.. I really loved it sir
Wow you have a different approach of using and explaining Dictionary. I have been using this for quite sometime but explaining Item like a Value makes it more understandable. I want to learn how to use API and i though your guidance will bring me there. Thank you so much.
Oh, man... this is pure gold. Thank you, sir.
Well explained thank you👍👍👍
Thanks Paul! It has been 2 weeks since your last video. Looking forward to more videos soon 😊
Hi Mi. I will be uploading more frequently in the future.
Can’t wait 👍🏾
Awesome lecture
I am very grateful to your videos and am learning a lot.
I opened the Watch Window and added dict("Apple") to it as shown in video. The Code, the Watch Window, and the Immediate Window all work fine as I Step through (F8) the Code.
However; when I then add dict("Orange") to the Watch Window and step through with F8, I get the Error Message that "This Key is already associated with an element of this Collection".
But... if I use F5 to simply run the code, everything runs and the Immediate Window prints out all the correct Values.
What am I missing?
This is actually a bug with the Dictionary. If you add an item in the watch window it gets added to the Dictionary. It caught me out a few times before I discovered it.
@@Excelmacromastery ~ thanks... thought I had written the Code incorrectly.
Yes, I write all of the Code as you present it "for the practice".
Very useful video !
I was surprised by following the intellisense
dict.Add(“Apple”,60)
does not work
but if I write
dict.Add “Apple”,60
then it's ok
The intellisense misled me.
What is the difference between the 2 writings?
Thanks ...
Really wonderful and i have made a real dictionary in excel userform ..
Nice, never used the watch window this way!
Thanks. It's a nice way of demonstrating the values in the variables.
Hi Paul.. great intro to using a Dictionary.. looking forward to more. I see below you've mentioned about early and late binding. So, if you are going to distribute your code to users who might not have a reference to the Scripting Runtime, then you early bind while you write the code (for Intellisense) and then modify the code to late bind (Dim dict and Set dict=CreateObject("Scripting.Dictionary")) before distribution? That seems like the logical approach..yes? Thanks for the inspiration to think and create. Thumbs up!
That's exactly right Wayne. For distribution it is always better to use late binding.
@@Excelmacromastery Thanks Paul.. loving your videos and UA-cam channel. BTW, on your channel, take a look at the View Full Playlist for Excel VBA Collections. It looks like someone inserted a 6th video into the list.. not related to VBA.. maybe some kind of a hack.. don't want to click on it.. but it definitely looks like it does not belong there. Thought you would want to know and delete it or report it to UA-cam. Thanks again!
Thanks Wayne. I removed that video. All is good now.
Hi you shown how to add elements to the dictionary, can you please explain the purpose /use of the added elements.
Hi, great video. I ran into a problem I'm hoping you can help with. I added the Microsoft Scripting Runtime reference and created my dictionary variable the same way you did. However, the "Add" method is not available for my dictionary, why would that be?
Hi! can you teach us how yo create a dictionary of collections of class collections?
Hi Paul - you need to edit your above video description...
"In this Excel VBA video, we see how to use Class modules objects with Collections. One of the problems with collections is that it can only store one item from a row of data.
So how to we store data from multiple columns? We use class module objects.
"
Thanks for pointing that out. Updated it now.
Is there a spot where we can see what the end-product looks like?
I thank you for explaining this so well as I've wondered for years what it did or was. But, I'm puzzled as to where I might use it. For example, I have a price list with 3,000+ items on it that I use repeatedly. I know the item number (key) so I've just been inputting that in the Excel Find option. Is there maybe some better way to use the dictionary to do this?
Good to hear from you Bob.
If you have a low frequency of finds e.g. manually finding the Excel Find is fine.
For a high frequency though, the Excel Find is slow. For example, imagine you had a second list of 1000 items and you had to look up these in the first list. Then the dictionary would be very useful.
You can see some examples at the bottom of this post:
Excel VBA Dictionary(excelmacromastery.com/vba-dictionary)
Show less
One major benefit of using a Dictionary is that lookups take constant time. That is, it doesn't matter how large the Dictionary is, looking up a certain key will always be practically instant.
This is unlike arrays where you need to scan through the array.
Is there a method to apply a Like comparison to the value in the dictionary i.e. Dict item is "Shower" but like comparison would be Shower1 or Shower 2 etc - I have tried with no success adding wildcard to the dict item
Hi, I saw you added the dictionary from the reference library. Do all users then have to add it to make use of the workbook?
In practise is better to use Late Binding(i.e. CreateObject) instead of the reference when distributing to other users.
How add to dictionary other dictionaries?
My dictionary object always has one empty item in it. Also, I cannot remove that "Item 1" using : If Dict.Exists(" ") Then Dict.Remove " " ....... What is the solution?
Hi thanks for the video.i see It is 1 of 5. Will the other be far away as I noticed others are being released.
Hi next Dictionary video will be released in the next 2 or 3 weeks.
@@Excelmacromastery no worries. Look forward. Hope you stick to the vids. Been enjoying them. Easier to watch then some of the other drawn out vids on here.👍
top !!
what if I want to add multiple values to a key in a dictionary? Like in python, I use key.append(xyz). How to do that in vba dictionary?
Your dictionary value probably ends up being a variant array or another collection or dictionary depending on use case. That kinda how my nested Jsons end up working
I think the Peter Norton look went out with the 1980s dude
How can I write a formula in an excel cell to extract 1 item from the dictionary by using the key? I would think you you write something like this in a cell =(DD.name,key,position) then Apple would be in one cell.
Hi Paul,
I used late binding but I am getting object can't be created error message in office 365. Is there any solution? I can't use early binding bcz I need to distribute the Xlam file to my team.
Make sure you write
set dict = CreateObject("Scripting.Dictionary")
and not just
set dict = CreateObject("Dictionary")
Keep calm and learn vba
:-)
Hello Sir,
If I have this kind of data how can I do it using Dictionary:
Column Headers
P1 with phone numbers
P1 Type : Cell or Landline or Voip
P1 Old: Yes or No
Phone1, P1 Type, P1 Old, Phone2,
P1 Type, P2 Old up to Phone10
Output Sheet1
Delete rows with P type: Voip
Delete values with Type: Cell and Old: Yes
Output Sheet2
From the original sheet
Retain only Type: Cell and Old: Yes
Then: if column Phone1, type and old has blank records cut and paste from Phone2, type2, old2 and so on to fill in blanks
Can this be achieve using dictionary Sir?
The audio level is on the edge of distortion. Remux and take it down by 20% please.
very confusing