How to use Class Modules with the VBA Dictionary

today i'm going to show you how to use class modules with a dictionary in excel vba and then i'm going to show you something you won't have seen before how to make a vba collection behave exactly like a dictionary with a few simple code changes so let's go ahead and get started the first thing we need to understand is why we use class modules with a dictionary well imagine we're storing sales of fruit we store an apple and a number of sales like this so apple is our key and the number of sales is our item in the dictionary and then we could add a second item like this say for example orange and the sales are 100. but imagine now we have data that looks like this not only do we want to store sales but we also want to store the amount as well we can only store one value per entry in the dictionary so how do we store multiple values so the way we do it is we use class modules because class modules allow us to group a number of variables together and then we can store the class module in our dictionary to add a class module we simply right click and insert class module you'll see that the class module now appears in our project window we change the class module name to cls fruit in the properties window like this now we add variables to our class module we say public name as string public sales as long and public amount as currency now we have our class module set up let's look at how we can use it we start by declaring our cls fruit variable dim fruit as new cls fruit and then we can assign values like name equals apple and sales equals 100. let's step to the code and see exactly what's happening we look at the watch window and we can drop the fruit variable in there stepping through the code you'll see what we've got is fruit and we've now got name equals to apple now if we step over this line you can now see that sales equals 100. the key thing to understand with objects is when and how to use the keyword set the first line here is equivalent to declaring the variable and then setting the fruit to a new cls fruit this means the first line is equivalent to using the second and third line both do the same thing but the difference is that set gives us flexibility and what this means is that we can put it in a for loop or an if statement and so on whereas the dim line means that every time the code runs we've just got one of these objects let's look at putting set in a loop we can say this loop equals for i equals one to five and then we can create the object here so we set fruit equals new fruit now i'm going to put the fruit name to have i at the end so that we can see it as it runs through the loop so let's look in our watch window and this is very interesting to see what happens here let's remove the first line as we don't need it we step through the code for i equals one to five fruits you can see is notting at the moment now we can see fruit has variables which means it has been created so we place apple plus the value of i as the name of the first item and then we place i as the sales value now we go to the next one we set fruit equals new fruit it's empty so fruit name equals apple two and sales equals two so what happened to the first one and what exactly is going on well to understand what's happening here we need to have a little look under the hood and see exactly what happens when class modules are created in vba when we create a variable like this vba creates the equivalent of a cell in memory and then when we place a value in that variable vba stores the value in memory something like this and this works for all basic types we just have a space in memory and vba stores the values there now the way an object works is a little different when we declare an object like this vba declares the variable so creates the cell in memory and the cell is empty at the moment and then when we say set fruit equal new cls fruit what happens is the new cls troop part creates the new object in memory and then it takes the address and places the address in fruit now if we do dim fruit as a new cls fruit it's basically doing what we did in the previous two lines it does them in one line so why does vba work like this it might seem a little crazy at first but it actually makes perfect sense imagine you've got a database and a company now every time the user wanted to use a database imagine they have to create a copy of that database and copy it to their computer so that wouldn't make much sense to be very inefficient and not only that we'd have multiple versions of the database which would end up being a nightmare so this is exactly why vba treats objects like this we have the address of fruit and then if we decide that we want to add this object to a dictionary rather than creating a new copy which would have a bit of overhead and which would mean we have two versions vba simply copies the value into the dictionary so copies the address as the value it does this seamlessly so you don't actually know that it's going on what this means then is that if we say set fruit equals new fruit we actually create a new object and vba puts the memory address in the variable just like before and then if we come along and we add it to the dictionary then vba does the same thing it just adds the address to our dictionary so we're not creating two versions of the same object we're referencing the same one now if this doesn't make sense just think of it like this every time we use new so either with set or with dim we're creating a new object in memory that's when the new object gets created in all other circumstances vba is simply copying the address of the object okay let's see now how to add an object to the dictionary what we do is we create our dictionary first of all when we add an item to the dictionary we need to see if that item already exists if apple doesn't exist what we want to do is we want to create a new fruit object and we do this by sending through to equal new cls fruit if apple already exists in the dictionary we don't need to create a new object we want to use the existing one from the dictionary now when we create the fruit we add the name which is going to be apple we then add it to the dictionary so the key is apple and the item is fruit this is quite simply how we deal with adding an item to the dictionary now that we understand how to add to the dictionary let's do our full example and you can see how we can put everything together this is the data we have and what we want to do is get the sum of all the sales and all the amounts for each fruit and we're going to use a dictionary to do this the reason we use the dictionary is because every time we read the name of a fruit in our data we can easily check if it already exists in the dictionary and if it does we can easily access it whereas if we use something like an array we have to go through each item in the array to find the one we want so let's write the code to do this the first thing we want to do is get the range so we declare a range variable and we then set the range equal to b tree this is where the data is and then we use current region to give us back to all the adjacent data to this cell now we're going to read through the data we use i as our variable and we create a for loop we say for i equals 2 because we want to skip the header so we don't want to read row 1 and we loop through the number of rows in the range now i'm going to create another variable here and this will be the name of the fruit and just having it as a variable makes our code that bit more readable we use range cells to read the data from the worksheet the row is i which is the current row in our loop and the column for name is column one let's create our dictionary we create our dictionary at the start of the soap we want to check now does the name of fruit exist in the dictionary and if it doesn't exist what we want to do is create a new cls fruit object let's create our fruit variable fruit as cls fruit we set fruit equals new cls fruit and then we want to write fruit name equals name so the current name that we're reading and then what we want to do is to add this new object to the dictionary so we do dictionary add the key is the name and the item is the fruit object now if it's already in the dictionary we want to set fruit to equal that item in the dictionary in other words we are copying the address of the item from the dictionary to the fruit variable let's just look at this code again if the item does not exist in the dictionary we create a food object and then we set the name and then add it to the dictionary so we use width fruit to reduce the number of times we have to type the word fruit when we say amount equals amount plus the value at column four we can then say sales equal sales plus the value at column three we then say end width to end this wait fruit section now we want to check to see if we have read the data correctly i'm going to create a simple print dictionary soap to write out the contents of the dictionary we create that here like this print dictionary and this takes the dictionary as a parameter then we get the key as the variant and we say for each key in the dictionary and now we say flute a cls fruit so for each key in the dictionary we get the item and then we say set fruit equals the item at the dictionary key and now we want to print out the names so we use fruit and we print out the names sales and so on and then we finish by adding the next key and then we do a call to the print dictionary and pass the dictionary as the parameter if it all has worked as we expect then it should write out the results to the immediate window let's run the code and you can see that it rolled out the results as we expected so we should check the original data to make sure apple has 71 in sales as the total so it's always good to check this to make sure there isn't an error in our code for apple you can see that we have sales of 30 and again we have sales of 41 so the total that we have is correct now the final thing we're going to do is that we're going to see how we can exploit a little known feature of the collection and get it to behave exactly like the dictionary but first of all let's write out the results to the worksheet instead of the immediate window because this is what we do in a real world application to do this we don't have to change the print dictionary so very much at all we need to get the range first so we create our range variable and then we set the range equal to the sheet and to the range which is g3 so this has given us the cell we're going to start writing the data the first thing we're going to do though is clear any existing data which is always an important thing to do we do range current region to give us all the adjacent data in case there is any all the json data includes the header which we don't want to delete so we use offset to simply move it down one row so we set the parameter of the offset to one and then we simply clear the contents now it's important to always do this because we don't want to mix the existing data with data we may have with before from a previous run so now when we're writing it out we say arrange cells under row and we'll set this in a moment and we write out the value now we declare our variable as row so row equals 1 because it's the first row that we're going to write it out to the first row on our range and then every time we write it out we want to move to the next row so we simply add one to row after we write out the current rule and let's run the code and see if it gets the result we're expecting and you can see that the results came out exactly as we were expecting they match what's in the immediate window and number two the important thing as well is that they're actually written to the correct place in the worksheet now i'm going to show you how to replace the dictionary with the collection and by just using a few small code tweaks i will get it to behave exactly like the dictionary now many people don't know this but the vba collection also has the option to use a key as well for example here we're adding apple as the key and 99 is the item and when we run the code you can see that by using the key we can get back 99. now the one drawback with the collection is that if we try and update a value it doesn't allow it because the collection is read-only but as we saw already when we add an object variable to a collection it's not actually storing the object in the collection it's just storing the variable so what this means in very simple terms is that we can change the object when we add it to a collection and now we're going to replace this dictionary with a collection so why would you want to do this well there's two main reasons the first one as i mentioned already is that if you're using a mac then you don't have access to the dictionary and the second reason is that when you use a dictionary you need to use either early binding or late binding and both of these have their advantages and disadvantages but if you use a collection don't have to worry about any of that so now let's change the dictionary to a collection so the first major issue is that there's no exist function for the collection but we can get around this very easily we just create our own exist function like this and then we just pass the collection as the first item so the next thing that we want to do is want to change how we write it out and in fact it's easier to write out because we can use fruit in the forage loop we don't have to set it and before we run the code we're going to get rid of the existing data just to make sure that it worked correctly this time now when we run it you can see it gives us the exact same results as when we use the dictionary so now you can see how we can use the collection instead of the dictionary when we're dealing with objects if you'd like to know more about the class modules or the dictionary then check out one of the playlists on the screen if you enjoyed this video then please hit the like button and if you'd like to get notified of my upcoming videos then hit the subscribe button and the bell icon beside it

Channel: Excel Macro Mastery

Views: 21,542

Rating: 4.977716 out of 5

Keywords: #ClassModules #VBADictionary #VBACollection #ExcelVBA #VBA

Id: MF0nm5kk1vg

Length: 14min 29sec (869 seconds)

Published: Thu Apr 01 2021