Excel VBA Collections: How to use Collections (1/5)

hello and welcome to this video on using collections in Excel VBA so in this video I'm going to cover the basics of using collections in Excel VBA and these are the topics that we're going to cover and in the subsequent videos we're going to look at more topics like comparing collections to arrays and using collections with for loops and using collections with objects so the first thing is what is a collection why do we need it how to declare a collection there's two ways of doing this sort going to look at border laws how to add items how to insert items how to get the number of items in a collection and how to remove items so as usual I'll be showing you how to do this with actual code so when we are talking about a collection it's basically a collection of variables so the first thing we should understand is what exactly is a variable so we use a variable in VBA or in any programming language as a place a temporary storage for different values that we want to use while our application is running now the reason we store them is generally because we want to manipulate them in some way so for example we might have marks as long or long as a long integer and say we want to read a student's marks and we want to do some calculation with the marks and then we might like just read that from the worksheet like so we'd read it from range one for example like this and so that will place the value in marks and then we could do some kind of manipulation on this variable x five and so on and so forth so that's what we do with a variable we basically put a value in it manipulate it a bit and then we'd probably write the value somewhere or do something with it so the problem occurs is imagine we're dealing with lots of students so if we have say ten students for example then we'd have to create a variable for each one so mark says long then Mark's tree is long and so on and so what would happen then is that if we're the Towson students we'd need a thousand for Abel's and the whole thing will get very very messy and the second thing about that is that the cord isn't flexible when you'd have those thousand because sometimes you might be reading from a student list and there might be 10 students sometimes might be a hundred and so on so that's why we use collections collections means we can easily add items to the actual collection with the same bit of cold so we don't need to create a new variable each time we just create one variable and that variable is a collection so the easiest way to understand the collection is just to see how we actually use one so any variable that we use in VBA we must declare it first and this is how we declare it we do Tim call as new collection now new is what we use with certain objects in VBA so we don't use them with Excel objects like worksheet range because these are already these already exist so for example if we assign a variable to a worksheet the worksheet already exists in Excel so Excel basically points us to that existing object and VBA takes care of when worksheets are created or workbooks are created and so on but with a collection we're basically saying to VBA we want a new collection and this will create one now the second way of creating it you might have seen before is we just declare it we don't set it as new and in this point the collection is empty and then later in our code what we do is we create the new collection like this these two pieces of code do the same thing but the reason we use set is cuz it provides flexibility so we could do for I equals one to ten say that should be in equals and then each time set runs it creates a new collection so in this case we'd have ten collections now we could put the ten could be like the number of rows are the number of worksheets or whatever but the point is that it gives us flexibility and in our code we can decide when to create a new one so this is the reason why we use the keyword set so let's look at a collection then so just adding items and that should be a sub so for adding items to a collection we basically create our collection as I said so new creates exactly one collection and then we add items by simply using add so we add and then we put the item we want now can be a string it could be a date it could be even objects or something like that but we're going to keep it simple at the moment and just add a string and you can see in this one we're adding a non CH so if you want to understand collections or learn about them the easiest way to do it is like this just copy this code like I'm using here or write your own version of this just create a new workbook create a new module put code like this in and then step through the code and as you step through the code you can just view the variables so you press f8 to step to the cord and you can see that we have the code on appleton on the line that adds the Apple so what we're gonna do is view the locals window the locals window basically is our local variables so let's pop this guy over here and you can see call is nothing at the moment and as soon as we add something it becomes something so item one Apple you can see now let's add orange and you can see orange gets put into position two and them already so what happens with the collection is every time we add an item it gets put into the last position now if we wanted to add an item somewhere else we can use either before or after so if we add pear and we want to put it in position two we can say after and we assign after to position one and this will put it after whatever is in position one so let's step through the code again so we've got the plus we click on the plus you can see item one is Apple item two is orange and whereas pear gonna go it goes into position two because we said we wanted after position one now we can use before pretty much the same way it's just the behavior that's really different and we're going to add another and would say before tree so we want to put it in position tree so in other words it's the new tree I'm gonna make that mango so again just move this slightly over again we step through the code add the apple add the orange insert the pear into position one our after position one and then insert mango before tree we should put it in position tree and you can see that we have mango in position tree so this is how we add items to a collection and this is how we insert items to a collection now after we add items to a collection we often might want to say many items is there and the reason we often do this is because we were running through the items in the loop so we can easily get back the number of items in a collection by using the count so number of items is and what we use to get the number of items is call duck out so that the count attribute or account property is what we'd say so we just run this code straight through you can see it says the number of items is 4 now in this case we've looked at inserting an item now you might be wondering why to use a collection versus an array and we'll be looking at that in more detail in one of the later videos in this series but the main difference you can see is from the insert items or when we remove items with an array we have to define the size of the array and if you want to resize we have to basically say what the new size should be but the difference in a collection is we just add the item and vba takes care of resizing the same when we remove an item vba will just take care of removing now the biggest difference with the array is that if we want to insert an item it gets very messy because we have to basically resize the array and then we have to copy all the items down one or down two or whatever and then we have to insert into the correct space whereas as you can see it's very simple to do in a collection so that's kind of the main difference between using a collection and using an array now the final thing we want to cover in this video is removing an item and we remove an item very simply just like the way we say before and after to specify a position we basically say remove and we say whatever the index is so we say remove one so again we'll run through the code we'll have a look in the locals window to see what's happening you can see that we've got the four items apple pear mango orange and we're going to remove whatever is in position one and then when we run the next line it shows us that the number of items is true because we've removed one so that finishes the video on the introduction to using the collection so in the next video we're going to use the for loop and the for loop is very powerful because it allows us to run through every item in the collection even if there's thousands of them

Channel: Excel Macro Mastery

Views: 43,423

Rating: 4.9751554 out of 5

Keywords: excel vba, vba basics, collection, vba excel, collections how to use, collection vba, visual basic for applications, vba, microsoft excel (software), microsoft excel, basic collection in excel vba, excel vba macros, excel

Id: tg7Vrlxh_RQ

Length: 10min 2sec (602 seconds)

Published: Wed Jun 19 2019