Excel VBA Collections: Collections vs Arrays (4/5)

so hello and welcome to this video so a question I often get is what is the difference between collections and arrays in Excel VBA so in this video we're going to look at the differences between them and compare them in different situations so the first thing we're going to do is we're going to compare reading data from a worksheet to both the collection and to the array and see what the difference is you've got some sample data that you can see here just has first name surname country and items bought so let's have a look at the code so we do alt F 11 as normal to get into the code window you can see some code I've already written to read the tutor collection so we step through the code one line at a time we wish to use current region to get us all the data have said it before if you press ctrl asterisks while you're on an e spreadsheet it will get all the data that's adjacent and this is called current region so when we've nice-nice to kind of form data here we base e use current region and it brings all the data back and then once we have the data back we basically read through each line and we add the value now this is the one drawback of the collection over the array the problem is with the collection that we can only add one item so if we want to add say the first name surname country and item board it's not possible so to do that we need to use a class module or we need to do we need to store lots of arrays or collections within the collection so we keep reading and let's look at the watch window just to see what we have and you can see we've got the first item is country and let's continue reading second item is Columbia the next item is Greece and the next item is Austria so you can see how we read the data using a collection now let's compare that to reading the data using an array so we can copy this again so we have basically the same starting code here that gets us the data but actually what we need to do is we just need to declare a variable call it a variant and then we basically assign this to this range now I could assign it to ranged off value put it there's no point in having the range variable here because we're not going to use it again and just like that we can pop everything into the array we don't need a loop and we don't need to dimension the array so let's test out this code not much testing this is just one line so we step over the line and then we drop the array into a watch window and have a look and it's full now let's look at each part we look at array and let me just try the watch window across so we can see it just and you can see that the values that we have we've got first name surname country and item spot so that's the header the second place we've got lynn garcia colombia 16 the next we've got a key myers greece 32 and so on and you can see how powerful that using the array is now the other thing we can do with the array is that if we want to write the values back to the worksheet we can do so in the same way we basically just get the arrange and in this case our range is going to be let's make it f so we'll do range and we'll say f1 that will go to i am i 101 and we just say the value like this and the value equals array so let's move across here and see exactly what happens we run the code and you can see it copied everything out you can see that's super fast as well copied everything into the array then copy everything from the array back to the worksheet so you can see when it comes to reading all the data from a worksheet that the array is much better than the collection so now we're going to look at the second difference between the collection and the array now the big difference this time is that we're just going to read selected records so we're only going records where the country is United States so let's just have a quick look at that filtered data and you can see this is for records so we're going to read the first name of habit Miriam he D and Griffin so how do we do this let's have a look at the code you can see the code that we have before and this is how a collection reads from the worksheet so nothing too complicated there the only thing we have to change here is we have to say if the value in column three equals two United States then so that's just I just watch window for a moment then we want to add and that's all we have to do if we want to filter the data so let's run the code and see exactly what's happening I'm going to put a breakpoint here by clicking in the left margin and that means the code will pause here so we press f5 to run the code and you can see that the code has paused here so let's have a look at our collection so we drop the collection in and then we step past it and then you can see that the first name is Abbot now we run the code again until it's adding another item and you can see the next item is Miriam and so on until we finish so that's how we do it with a collection if you want to filter the data and you can see it's not much different than how we filter all the data from the worksheet so now let's look at using an array for exactly the same task so one like the last time where we could just take all the data in basically one line of code we basically have to read you the code this time and the reason we have to do is that we basically have to check the record now that's fine because we can do the same thing in the collection but you can see that we've had to add different lines of code here to deal with Rhys the array so these lines of code just make it a bit more complicated so let's run through the code in the same way that we run through the code with the collection and let's add our array here now we start off by saying row equals 1 and this is because what we're basically storing the next size of the array so we run the code and we stop when we find the first one because we've put the breakpoint within the if statement so we do a read them to resize so the preserve means that keeps existing data if there's an e so this is gonna create array 1 to 1 and if we look you can see we've got an array with one item now we put the value in that new position and we've got a habit now then we add rule I'd want to roll so that we know what the next sizes now again we could run the code until we come in again now we resize you can see that the array is now 1 2 with the 2 as the blank space we add a new value there and we have merging and and continue with this so on until we have the array full so you can see when we want to use the array for this it's actually a bit messy because we need to be precise E and when we want to use the collection we don't have to worry about resizing we just add the item and the collection takes care of it for us so you can see that the collection is much better in this scenario so onto the third major difference between the collection and between the array so the third major difference comes where we have to insert items so it's very easy to insert items in a collection but not so easy with an array so let's look at a collection first so if we want to insert items here we basically use before or after so let's just step to this code we add Apple so we've just added Apple so that we'll just add it in the last position then we come along and we say we want to insert orange before Apple that goes in position 1 1 insert pair before position 1 her goal now was to position one and then we're saying we want to insert banana after position 1 so banana goes in position 2 so you can see that it's very very easy to insert items into a collection now let's look at doing the same thing with an array so here is our array code now we don't know the size in advance of our array we don't know how many things we're going to be inserting and when this is the case we basically have to resize each time we want to add a new item now if we knew in advance is going to be 10 then it's not a problem but if we don't know then it gets more complicated so we set our rates we want we add the Apple so it looks pretty much like the collection did when we start at the previous one so you can see we have Apple now when we want to insert orange in position one we have to use this function that I wrote so let's just close this watch window for a moment we do insert and what the insert has to do basically it has to increase the size of the array so it increases the size of the array by one now we look at our array you can see we have a blank space our array is now size 2 and we then basically have to copy all the data from the current position were inserting to the end of the array we're moving everything on by 1 basically and we do it with our for loop and then we set the value at the position we want to set us and then we just return so you can see when we use the collection all this has been done for us but home or dinner with an array it's much more complicated because we have to do the work yourself so let's look at it one more time so we have two items orange and apple and we want to insert pair in position one we first of all have to resize our array so it's the current size plus one and you can see that we've done this and then we basically read true so we push all the items down one we copy Apple to the last position we copy orange to the second position and then when we finish doing that we basically place the item in the position that we want like this and then we return and so on so you can see in this case the collection is clearly better for inserting items now of course you can write the code yourself to do it but as the collection already does it for you it means that the collection is clearly better when it comes to inserting items so in the fourth and final difference between arrays and collections we're going to look at updating items now in a collection we add an apple here and then we want to update the value in position one and we want to set it to a pear so what happens well what happens is we basically get an error object required because we cannot update values in a collection we can we can they're only read-only if you want to update a value we have to remove it and add a new value so you can see this is a major drawback now there's one case that we can get away with this and that's if there is we're adding a class object so if we add a class object we can actually change the content of the class object in the collection but for basic data types like strings Long's dates etc we can add up data so as you can imagine this is a pretty serious drawback in a lot of situations now let's look at the array so here is the array and you can see with the array that actually it's no problem to update values so let's step through the code and let's have a look at our watch window and let's drop the array in here and you can see that we don't have anything in it at the moment and now after this we've got Apple so now we're going to attempt to actually replace Apple with pear and you can see we can do that without any problems at all so that's the in this round we could say that the array wins because we can't update items with the collection but we can in with the array and that can have a drawback for our collections in a lot of situations okay so we're going to just recap on everything that we've done and so let's have a look at arrays first and then we look at collections so array is basically if you're reading all data from a range it's very easy to do so in an array takes very little code and it's very fast if you want to update a single value like a basic data type then this is possible in an array so you can replace any date value with another value and it's quite simple to do the problem with array is that you need to know the size in advance now this is outside of weeding the data from the range in a little case you need to know the size in advance and if you don't know the size in advance then you have to resize all the time and this takes extra code which is a bit messy now the other problem at URI is that it's not easy to insert or remove an item and this is because if you insert to remove you basically have to resize and then you have to copy all the values down now the collection on the other hand reading data from a rain is slow because it requires a lot of code you have to read through each item and obtaining a single value is not possible and this is the except with objects where you can update objects now the advantages of the collection are you never have to worry about setting the size you just add an item it can be inserted somewhere or you remove an item and you don't have to worry about the size VBA takes care of that for you and as I said it's easy to insert or remove an item and even just inserting in position one in position five then VBA will take care of doing that in the collection for you whereas with an array it's very messy and you have to do it all yourself so these are the main differences between the collection and the array now I hope you enjoyed this video today on the differences between arrays and collections in Excel VBA and make sure to click on the subscribe button to subscribe so that you'll get notified about more of my videos and don't forget as well to check out the rest of the videos in this playlist on collections

Channel: Excel Macro Mastery

Views: 34,135

Rating: 4.9799747 out of 5

Keywords: vba, excel, excel vba, visual basic, visual basic for applications, vba collections, vba arrays, vba excel, vba collections vs arrays, microsoft excel

Id: QyXGyEVQl1I

Length: 15min 13sec (913 seconds)

Published: Thu Jul 11 2019