Excel VBA Collections: How to Read between Collections and Worksheets (3/5)

so hello and welcome to video tree in this series on VBA collections and in this video what we're going to do is we're going to look at reading from a worksheet into a collection and then reading from a collection to a worksheet this is what you typically do when you're when you have like a collection and you're using it in Excel VBA you're typically gonna read data from a worksheet so it's important to understand how to do it so you can see on the screen we've got a worksheet here and a worksheet has just a simple list of students under Macs and what we're going to do is we want to read all the students into our collection and these are students that have a mark that's greater than 70 so the first thing you want to do when reading to true some data on a worksheet is to get the range so we do get the range so we declare a range variable like this and then we simply set the range variable to some range on the worksheet now the worksheet we're using is sheet marks B so we're using what we call the code name of the worksheet which gives us back a worksheet and then we're using the range and then we say a 1 and we say just give us back all the records that are adjacent to a 1 and we use the current region for this so how the current region works very simple if you were in Excel itself and you click on any kind unintel I kiss and if you press ctrl + asterisks are what you can do is control Shift + 8 which is the same thing you'll see that as highlights all adjacent data so again let me just click on say number 8 here and control shift 8 and highlights everything so this basic highlights all adjacent data now it splits the data by rows and by columns so if there's a blank row on a blank column for example it won't highlight that data so go again control shift 8 so you can see that it didn't highlight the data because it's separated by blank columns and long blank rows now if for example we just put something here so you can see that b12 is no longer separated it's actually adjacent data click anywhere and do control shift it and you can see that it includes that data so that's what current region is and when we use it with well-formed data in VBA it's very very useful for giving us back a range of data now once we get a range of data we can always read it like this we declare our variable and then we basically say for I equals two and the reason we use 2 is because we don't to read the header so 2 to ranged up rose duck count and then next I at the end so we want to check the number in the number of max we want to check if the maps is greater than 70 so what we want is to get the actual cell so we get two cell like this we use range cells it's obviously ie because it's the current number because we're reading through each row so it's the current whatever I is the current one and we look to this in a moment when I'm stepping through it and we want to use column 2 of the range because that is where the marks are and I want once we have that we want to say we base say if the value in this cell is greater than 72 then and we want to add it to our collection so we do collection add and what we want to add is the name of the student now one thing to keep in mind here is that in many cases we don't have to use value like this we can just use range cells like this and it gives us back the value and in a lot of cases it works fine but in this case it won't if we add like this it will actually add a range object of this cell it won't actually add the value so it's always good to use value or value to which is the more modern version if you want to read the value from the range now what we want to do then at the end we have a break point here we're going to run the code and we'll stop on the break point and we'll check if the collection has what we think it should so let's do a debug compile just to make sure we've no errors and then let's run our code by pressing f5 and we went all the way to the end soap so let's double click here I'm gonna take our collection and drag it into the watch window and let's click on the plus see what we have and you can see that it has all the values that we required so for example it has ray now if we just look in our data here you can see that Ray is 88 the next one Kirstin is only 60 so she shouldn't be included and then the metria should be included as 98 you can see the mitri is included so all the way down we've included basically people that have a mark greater than 70 so a good way of testing our data often to make sure that we've got the right stuff is to print it out somewhere and so what we'll do is we rather than having the same Col kind of thrown in to all our different soaps we create one soap that will do this task for us so in this one you'll see how we pass a collection to a soap so we have a soap and we call a print collection and then the pasta value we simply say a call has collection and that's how we pass it and then we do the normal time we're gonna just do a standard far loop or a for each loop I should say it standard for each loop and we declare this as a variant and we say for each item in the collection debug print which prints A to D media window and then we do so now that we have the print collection how we use it is by using coal so we do call print like this and we pass call like this as an argument now we always do debug compile make sure everything is okay and let's bring up the immediate window that's control G our view media window going to double click on the header here just to pop it out so that we can look at it right beside what we're doing and I'm gonna get rid of this breakpoint so let's run the code and you can see that it printed everything out to the immediate window so very very useful the immediate window for testing or data because normally with data what we're doing is reading the data from somewhere normally a worksheet we're writing it out in a different format somewhere else and in between it's good to write what we have to an immediate window because the problem could be from a writing out that we've written it around the world sheet or so on so by writing a 2d media window we can basically say the first part of our code is working correctly so now that we have the data and everything grew we kind of know that it's writing out correctly what we want to do is we want to write it out to our worksheet so we're going to write it out and what we can do is we can create a second soul so rather than putting all the data here we could have a second so that says write the data so let me just go down and just get this off to the top of the screen and this will write the data and the same as below it will take a collection as the parameter now we want to write to e1 so this is the position that we want to write to so how we do it is again we just created we create a loop we can do it in two ways but we can just do a loop like this and then instead of the debug print that we were using them it just do line these are properly so instead of debug print we want to write it out to a cell and so again we're gonna use the tin WA that we did before so she marks B and through with new cells and it's going to be roll which ever is the current role and it's going to be one and the value and we assign the value like this and that will place the value here now to start off we want to say a roll roll equals one now this confuses a lot of people we want to we want to say we basically need to track the roll as we write out so if you write 2 to the first so let me show you if you write to e1 then the next time we want to write to e2 and then we want to write to e3 so we've got every time you write out we've got to move our counter down one so we set our counter equal to one and then after we write the value out we basically say Rho equals Rho plus 1 so Rho equals Rho plus 1 if you're not familiar with this is basically just adding one two roll so the equals here is actually an assignment it's basically saying Rho now equals Rho plus 1 our row will be assigned to whatever the outcome of the right-hand side of the equals so in other words the equals is assignment and we're gonna have five here because 5 is the column that we're writing out to and this should write out everything so all we've got to do is call write data and give it a heart collection now just comment out this one because we don't need it so let's just go here and see if this works okay and just bring this across and let's just run this code you can see it wrought everything out correctly now if we wanted to write out the header as well for example what we can do is we can actually read from the first one and we can just change this so that it says or I equals one so in other words if I equals one we want to add that to our collection as well so let's go back here and let's run the code again and you can see that it printed students at the very top and that it printed the rest of the data so that's busy how you read and write from a collection to a worksheet now one question people often have and you probably have it as well as what happens if I wanted like to read more than one column so if you want to read more than one column what we need to do is use a class and we'll be seeing that in a later video and so with the class we basically can store data are lots of different fields about our current students are whatever we're tracking the data on and we'll see that later in this series of videos on the collection in Excel VBA

Channel: Excel Macro Mastery

Views: 26,455

Rating: 4.99297 out of 5

Keywords: excel vba, visual basic tutorial, visual basic for applications, vba collections, vba, vba worksheet, microsoft excel

Id: qzrrcs4164c

Length: 11min 27sec (687 seconds)

Published: Fri Jun 21 2019