Excel VBA Dictionary: How to use the Dictionary (1/4)

hello and welcome to this video on the BBA dictionary so the VDA dictionary is very similar to the collection in VBA but there's one major difference and the major difference is that when you add an item to the dictionary it must have a key so you always add a key and a value so in this example here you can see the key and value pairs so the key in this example will be the customer ID and the thing to keep in mind is that the customer ID is unique and this is very useful because it allows us to identify very easily and retrieve the value for each customer now the reason it's called the dictionary is if you think about the real world dictionary we want to access a word we basically go straight to that word and the word is the key and then the word has a definition and that is the value so we don't read through the dictionary sequentially we go through the word and the second thing is the word is unique so now let's look at some code on how we use the dictionary so alt f11 as normal to go into the VBA editor now to use the dictionary we use dim and then we use the variable as normal can we say it has new and dictionary now dictionary appears here because I've already added a reference so dictionary is actually a library outside our fee ba so that means we have to use a reference so you can see I've already added it here it's Microsoft scripting runtime it's normally down a bit further if you're adding it so you have to check this now each time you use the dictionary in a different project you have to add it and once you add a 10 the dictionary is available so when we have our dictionary what we can do is we can add items like this so we need to have a key and we need to add a value so we can say for example let's add Apple and then we'll add how many apples we've sold and then we'll add orange and then we'll add how many oranges result now the thing to keep in mind here is that if we try to add one of these keys again we can in error so let's try not Apple again or try and change the value and we'll run the code you see we get the runtime error this key is already associated with an element of this collection so you can see we can't add the item twice now wanting to keep in mind about adding an item to the dictionary is that VBA actually has a very nice way of doing it so imagine we want to update we want to say Apple now equals 40 so what we can do is we can say if dictionary exists Apple then we just want to assign what's ever at that value to be equal to 40 otherwise we want to add Apple and the new value of 40 and then in the end let's print it out so value well Apple is and whatever at that position so let's step through the code and let's view our watch window I'm going to put it up here so if we step down and we add the dictionary so we've already got Apple so you can just see that our dictionary is one item and it's Apple now if we want to see what's in that position we have to basically add a watch like this you can see in position Apple is 60 now it's added orange and if we used orange here we'd see that orange is 78 now it checks it says those Apple exists it does so let's set Apple to 40 and then it goes out now imagine doing this again an apple doesn't exist and we step to the cold again I would say does Apple exist it doesn't it goes down to the Apple window and adds the Apple and then prints out the value so let's go to control G and you can say the value of Apple is 40 so it's printed out three times so let's run it again and you can see the value of Apple is 40 now the one thing to keep in mind here is that this court seems a bit confusing and the beauty of discord is that we actually don't need all we need is this line we can get rid of this this dis dis so if Apple doesn't exist in this case VBA will actually add the Apple for us so let's step through the code Apple doesn't exist we assign 42 but VBA will add the Apple if it doesn't exist and you can see value of Apple is 40 now another beautiful thing we can do here is if we want to add a value so imagine Apple has a value 60 and we want to add 40 tools we can just use this one line of code it's busy saying whatever is in Apple had 40 tools and that will give us some result and then we want to place the result in the current position in Apple so we stepped down to this Apple s60 then we do this assign and now Apple should be a hundred and we'll print it out and you can see Apple is now a hundred so this line is so useful if we hi if we hide this so we were basically the Apple doesn't exist already this means that we run down true and then it will actually create Apple put zero in it and then add 40 to it and now you can see that Apple equals 40 so the beauty of this line is that we don't have to worry if something is already in the dictionary this one line of code will take care of it for us so this is the basics on how to use the dictionary so we'll be looking at more in this series of the dictionary we'll be looking at different things like the dictionary versus collections we'll be looking at creating reports reading from a worksheet and we're looking at just the different things that we can do with the dictionary now if you enjoyed this video don't forget to click on the subscribe button so you'll get notified of upcoming videos and if you've got any questions about the dictionary just add them to the comments below this video

Channel: Excel Macro Mastery

Views: 37,676

Rating: 4.9786949 out of 5

Keywords: VBA Dictionary, Excel VBA Dictionary, vba, learn excel vba free, learn excel fast, visual basic application, advanced excal online, microsoft excel software, dictionaries, dictionary

Id: PrGchDgF3m4

Length: 7min 6sec (426 seconds)

Published: Thu Aug 01 2019