(8/30) Excel VBA Absolute Beginner Course (30 For 30)

hello everybody welcome back to 30 for 30 we are now on part eight so it's a great job getting this far and what work also the way through the program I think we've covered a lot of ground hopefully you feel you've learned something and I'm just looking in the chat now people talking about how that managed to do some applications themselves get some stuffs done that's what I'd like to hear so I hope you'll feel you're making progress right as always please let me know in the chat can you see me can you hear me Paul says he's got a bear in his hand fantastic stuff got to make it fun let's kick off with a question as usual and we've dealt with loops with that word loops but we've learned one particular type of loop what kind of loop is that can you go back a couple of streams and we've learned a for next loop of course and hopefully you're familiar with the set up where we have dim counter as integer for counter equals one to ten that's a classic setup that I'm using every day of my work so definitely internalized that one my question for you is what other looping techniques do we have available to us in Excel VBA so maybe a more advanced question but I can see we have members here in the chat we've got beyond with us we've got Li with us and we got Paul with us as well Paul's been with us for a while so what other looping techniques do we have in Excel VBA let me know in the chat if you watch it on replay have a little think about that before we get started let's get into the chat that say hello it's some people welcome Rick from Iowa Jurgen said the general checked in early so welcome guys welcome Adrian from Wales our welcome least an eland welcome adrian welcome who else have we got here trying to control this scrolling welcome ahmed from bahrain welcome Ian from Edinburgh welcome Frank welcome Justin welcome Paul goose' Christian odd and welcome and Lee Williams welcome Jana welcome Paul welcome Pete be welcome Henrik Yuliya Freddy yang from Canada welcome Freddy good to see you Elena's show is here as well welcome decease to nyan good CDC Daniels with us welcome Tania from Holland representing fantastic and I think that's everybody we've got today if I missed you know down the bottom Pritam his hair and casts two tests just checked checking the bottom and Deepak so welcome I don't teach Python Deepak I'm a bit of a VBA specialist so welcome everybody and then let me know in the chat yet do we have any ideas about other looping techniques I can't see any answers yep so let me give you a bit of a clue and let's think about the way Excel is structured have you ever thought about that how does the computer actually compile Excel and actually put it together so it displays properly and it works well Excel is organized into objects and collections specifically collections of objects now this word object we've dealt with before on the program so anything you can see in Excel is an object so the worksheets is an object and you can see in this file I've got three worksheets it's just a new file have just opened up a cell is an object the workbook is an object and then if we have charts in a sheets they're all objects if we have buttons in a sheet they're all objects but the new piece of information we're kind of introducing today is this concept of collections got some good ideas in the chat now keep when those in about loops this concept of collections so fokin if we can understand this concept we can harness it to get so much done because we can say to excel you know that collection of objects so you know all those worksheets you've got organized in a little collection in your memory rather than me repeating instruction could you just do the same thing to all of them and you know all of the all of the shapes we have on this worksheets could you do something to all of them since they're all organized in your directory in your collection in the same place this is the idea in excel objects are organizing in sync to collections we can take advantage of the to get lots and lots done this particular type of loop is called and nobody's got it in the chat yet but this type of loop is called for each for each object in a collection this is the basic piece of syntax we're gonna experiment today you are gonna feel the power we do have some good ideas in the chat chat though yeah welcome Gary and welcome Alexander Callum Gary says while or all web I hope your honor says do well yes this is our third type of loop so a do-while or a do into loop we're gonna come to that later in the program so good contributions there so we've got three types of loops you know this isn't important for today but just have it in mind three types of loops we've got our for next loop which is what we've been doing so far dim counters integer for counts or equals once ten we've got our for each loop which we're dealing with today you're gonna love it so for each object in a collection then we have our do and tell loop which we're gonna get to later in the program I'll be on a dig get it okay lately says be already to get it I might have missed it okay good stuff in the chat so let's get started with this how can we kind of access this idea as always at the bottom here and then let's suppose we want to do something to all of the sheets in the file and this is a typical thing that somebody might want to do we don't want to have to go through all of the sheets I'm hitting control function and page up control function and page down on the Windows PC we don't want to have to go through all of the sheets and then you know have to click through them we're trying to avoid all of that clicking hi I'm getting a notice saying the stream is weak so just refresh the stream if it's not looking too good for you we don't want to have to do that so how do we do that and what is the syntax so let's get into the VBA editor let's put a module in here and how do we start so option explicit to get started one additional thing at this point a few people have asked me to demonstrate how we get X to automatically add option explicit how do we do that how do we do it I'm sure I remember myself maybe somebody can tell me in the chat but we go to tools I believe in the Excel VBA editor you can go to tools and then options there's nice options here for setting up the VBA editor exactly how you want it you can change other fonts and the size of the font things like that now if you go ahead and hit require variable declaration that means that Excel will put option explicit in when you create a new module if you hit this now I'm going to leave it unchecked just because most people don't have their Excel setup like that so it's good practice to keep typing in option explicit there and David DC typical DC is always on the money he is giving us the syntax in the chat hello mom oh and welcome thanks Rick Rick says it's in tools that so let's go ahead and say let's say do something do something do something to all sheets so do something to all the worksheets in The Fault now when we're working with a loop offer not always often we need to declare a variable a variable is just a place to store some information it helps us manage control the loop when to start the loop when swender loop things like that in this case we're going to have an object variable and it's going to be a worksheet so dim Chris she has worksheets so this is another variable type so you'll remember what are our variable types we've got things like integer long string boolean as well we'll get to that later in the program we've also got our object variables so things like worksheets sell those objects we can always we can also use those as variables to get stuff done don't worry too much about this technical stuff it doesn't really matter we're gonna keep it practical just follow along with the example then this is the magic bit really and DC has already put this in the chat but this is the magic bit we want to say to excel for all the objects in this collection so for all of the worksheets in your worksheets collection so let's go ahead and it's going to be a for each loop so let's say for each Chris sheets in so that's our object there for each Chris sheet and you can see we've used the name of the variable that in and then we're going to say I think it's worksheets is the name of collection although we might be able to get away with sheets I think it's worksheets so this is how we open up a for each loop what do we need to do when we open a loop we need to close it straight away or we might forget to do it and that can cause problems so this is our basic syntax we've got the loop open opening line for the loop the closing line for the loop and this of course is a for each loop for each object in a collection we're doing worksheets you could do cells you could do any number of things well do at least one more in this stream DC says this is a very useful lesson thanks DC but you have it hasn't happened yet the lesson let's hope is useful Cheers DC you know I'm only kidding and DC says I use this all the time in code and Paul says yes the good old chris sheets absolutely why do I say Chris sheet well if you say Chris sheets or Chris workbook or Chris cell you can guarantee you can use your name to have to use my name you can guarantee that Excel won't have reserved that particular piece of syntax won't have reserved that word for its own use if you say for if you say dim sheets as worksheet or dim worksheet as worksheet that could get problematic certain words like worksheet row column cell Excel reserves for its own use so if you say Chris she is making things distinctive so let's go ahead and just see if we can get this working so I'm going to have the variable name and this should just flash up well what do you think it's going to do if you watch it on the replay stop the stream and try to assess what this might do so we're going to loop through all the sheets and hopefully flush up the name of each sheets controller save the file I'm gonna hit f5 on the Windows PC you can just hit the play button at the top hit f5 and we can see she won how that makes sense because that is the name of the first sheet in the file then confusingly the second sheet in the file is called sheet for not sure what happened that and then sheet 5 is the third sheet in the file and then Shih Tzu and then sheets three there so it's going through the sheets in the order they're positioned in the file and just giving us just giving us here are a piece of information is giving us one of the properties of the object can you remember that properties word as well one of the properties of the worksheet object is the name we're just externalizing that property there so let me know in the chat are you following along yes Paul is on the money the name of each sheet 43 viewers 11 likes very happy with that can we beat the record yesterday I think we've got 247 one I give it a quick share with you mates and let's try to get a few more people onto this just while we're here if you're enjoying this stuff and got lots of positive feedback check out our membership options we have a weekly live stream which is called members Monday you get access to a weekly live stream with with me we go into much more depth and detail than we do here you also get access to a Facebook group and a content navigator to allow you to navigate the content of all the previous streams which I'll get instant access to if you take out a channel membership to members Monday you can see people in the chat that people have a little membership sign next to their names it's like a little s in a circle and they are some of our excellent channel members got a really good little community here so let me lay down another challenge here going back to the first sheet in the file suppose I want to list the names of all the sheets in the file whoa that sounds difficult so we've got five sheets in the file I'd like those five sheet names listed here so as usual we're starting with one technique just getting it working just doing something simple and then integrating one of our other techniques you're gonna have to think which technique we're going to need and then creating that synergy between techniques it's the synergy between the techniques that really creates the magic so what do you think what do you think then we've got some syntax we can work with hair and I'm just going to make a slight adjustment to this syntax so the name of the sheets is sheet one and you can of course change the name of the sheet if you want and let's start in b3 here okay how about this I think I think this is a good starting point as always with code you know we don't try to complete the task in one step we take an intermediary step I know this isn't gonna work but it's gonna get us part of the way we don't have to do all the work at once best to do a little bit of work test it a little bit more work test it that's the way to keep the stress levels down keep your programming sessions long adrian on the money here Adrian's talking about using range and offset so that's good from Adrian so let's give this a go I'm gonna hit the f8 key now on the Windows PC you can go to debug and step in sue and just step through the code you can see we've got how usual setup here the VBA editor along side of the worksheet so I can see both and see what's going on okay so let's keep an eye on cell b3 we can see the sheet name of the first sheet in the file as the sheets are ordered has gone into b3 there and then sheet 4 has gone in so keep an eye on cell b3 up here sheet 5 hitting the f8 key sheet 2 and sheet 3 so the specific text that appears will depend on how many sheets you've got in the file and the names of the sheets I've got five sheets in my file let me just go back to the whole view I've got five sheets and they're called sheet one sheet full sheet five sheets you XI 3 so that's why we were seeing that as we did that so we get in there we're getting there with this task but what do we need to do now Adrienne has suggested offset yet we need better control of position don't we because we're just over writing we're overwriting the text in cell b3 we won't do better than that we want to work down the cell so the second she goes here third she goes here for she goes here and the fifth she goes head how are we going to do that to be on it another one of our members beyond I made his debut it members Monday already making some fantastic contributions to our community and another fantastic contribution here so offsets working with a variable working with a cancer variable so let's say offset here and I'm just going to put some kind of placeholder some kind of silly numbers in head and then we also need a variable and the variable is going to allow us to control position by working with offsets so what kind of variable do we want I'm gonna say a different variable now just to show we don't have to always call it cancer I like using the name positioner positioner because it's literally doing positioning and it's going to be an integer variable so a whole number variable here that's going to do the job and then let's pop the variable name in here positioner and then let's just see what happens now so again I'm not expecting anything else to happen yet but it's just gonna get us a step closer here so again hitting the f8 key and keep keep your eye on cell b3 keep your eye there on cell b3 it's in the f8 key okay there we go let's change this to zero come of these cells again and reset the code hit the stop button if you want to reset the code hitting the f8 here we can see sheet 1 sheet 4 sheet 5 sheets 2 and sheet 3 so I'm just hitting the f8 key that's going through the code line by line okay so we're getting there so we've got to control this variable more effectively hair and we want to increase the value of the variable by one each time we go through the loop that's going to have the effects of okay the first time we go through the loop will be here the second time we go through the loop increments up by one that's going to get us to here once we start pairing the variable with offset the third time will go here fourth time and fifth time so we'll have that effects of working down our little range of five cells here so what's the syntax we need our basic VBA built building blocks there we go Dave DC's having a go at me in the chat control space to autofill variable names here we go so one of our building blocks here so a equals a plus one so can you buy that building block to get this task done yet DS if you hit control space when you're typing variables X out will also fill the name of the variable rather than having to do all of the typing as I was there so positioner a equals a plus what position R equals positioner plus one here there we go be honor on the money very good control there save the so I'm going to do this with the f8 key again so what's the value of positioner because we haven't initialized I would say initialize the variable we haven't allocated about you two hits it's going to have a value of zero the first time we go through so sheet one's gone in there now we're going to increment the value up by one we can see positioner is now one I'm just hovering the cursor over the variable that and then she four goes in so position is working with offset here the value of positioner goes up by one it's working with offset again there we go value goes up by one working with offsets and then finally in cell b7 we can see that was what we wanted to do and just to prove this I'm going to play it for you so control shift down and the Delete key reset this and then f5 to play it but we can see our sheets names appearing there so what do you think guys have you got this one working for you so only a simple example but these mechanisms that we're building are super powerful we've got our second loop type here for each objects in a collection we've got position control here are using offset we've got a variable that we're incrementing up all of these techniques synergizing together to do super whole stuff yeah DC in the trap is absolutely right if we don't ideally we would initialize the variable properly by it's just saying at the top hair positioner equals zero if you don't assign a value to the variable like that against your variable we'll assume value of zero a string variable that is text will assume a value of nothing nothing as in speech marks you can see what DC is saw him out in the chat here Dona says great to know that's good stuff Daniel sounds like you got it working Paul's got it working protons go working Lee's got it working fantastic Pete has got it working Lee has got it working - and Beyond has giving me the thumbs up so thank bianna has got it working as well very good so this is a typical object a typical collection of objects that you might work with worksheets in a file what else might we work with what about um it could be each cell in a range but we're gonna come to that later let's do something a little bit more fun let's put some shapes in here and you can put any shapes you like I'm just gonna go for some what should I go for I like these cloud shapes so I'm gonna put some cloud shapes in here and you can do whatever you like hold down the control key and then make as many shapes as you like doesn't that look pretty let me know in the chat do you like that put some shapes in you can put any shapes you like so let's see if we can do the same thing here shape so you might be like whoa I've got no idea how to do that but we just go back to our building blocks one of our building blocks is for each object in a collection and remember I said at the beginning shapes in a sheet is one of the collections one of excels collections so the shape objects in in a collection let's see if we can harness that now again just to list the names of the shape so we've probably got some here cloud one cloud to cloud three so let's see if we can list the shapes here and let's do this by yeah I'm just going to adapt this code I'm going to adapt this you can copy paste this down if you want I'm just going to adapt this code let's see if we can get this working so let's say dim Chris shape I think its shape I think that's the object variable we want but we'll see live coding who knows what's going to happen and then we need to change this of course and then we're gonna say this this worksheet no active worksheet who will be active worksheets dot shapes so we say into Excel for all of the shapes on this worksheet can you do something then got to make sure we change this to Chris shape of course so being very deliberate about the syntax here any syntax errors you know even a single letter it's not gonna work and you might think it's a disaster they might feel like a failure but it's actually just one spelling mistake so try to be as precise as you can with the Spelling's that so let's say Chris shape don't name here let's see if this is going to work just sense checking the code now do something to all shapes there we go okay control let's save the file so can work through it with the f8 key as it works active worksheets dot shapes hmm doesn't like that is it this worksheet hmm let's see let's say I do is it gonna like that let's have a look okay so let's try this let's put the actual sheet reference in sheets sheet one thought shapes okay let's give this a go hitting the f8 key okay that seemed to work so just tweaking the syntax there that's you know as I say to you all the time you know I don't memorize the syntax quite often I get it a bit wrong like that you know I've got a few options but what would you do if you weren't able to kind of work through a few options like that you could just go online if you google Excel VBA all the shapes on a sheet you'll be able to find some code that you could work with so hitting the f8 key now and then what's going to happen let's keep our eye on b3 and we can see our shape names being listed there so another cool collection that we can work with all of the shapes okay let's see if we do something else call with these shapes so I'm gonna quickly show you another property here our DC says active sheet yep I think you're right there DC so is this gonna work with that if she let's have a look controller save the file f5 key yeah so the syntax should be active sheet as opposed to active worksheet I still make mistakes try not to make a big deal about it we just fix it we keep going it's all part of coding DC is still talked about control space I will integrate that into my practice DC I promise we've got 50 concurrent viewers this is a record this is a record for 30 for 30 thank you so much for watching I hope you enjoying it I'm loving delivering these sessions and let's see if we can go to 60 viewers 16 lights as well fantastic stuff let's go ahead and just just for a bit of fun now Chris shape dots hop let's try this okay so what's this dots hot property well we can see it's a property because it's it connects to the object and it's after the dots so that usually means we're talking about property I'm just going to go ahead hit the f8 key here okay Chris shape that's hot okay right quickly let's try this if this doesn't work I'm going to bought this so I'm going to come back to it later in the stream oh I'm not sure about this let's let's try this in the FA key here okay address maybe some let's try this okay hit the f8 key okay we're to come back to that later we're going to come back to that later there's a nice example where we can move shapes around I'm gonna have to go and revise my syntax a bit and so be honest we're running out of time anyway but we're going to come back to that later and I've lost my codes I've lost my code so I have to close the file open the file here to get it working so hopefully you've still got your code in the file there good stuff so let me know any further questions in the chat we'll come back to that example later don't worry about it hopefully you've got your clouts and you've got something pretty in the worksheet but more importantly you've felt the power of objects and collections so excel consists of objects we're dealing with shapes here could be worksheets could be open workbooks and we can say to excel for all of these objects in those collections that you organize them into can we do something to all of them and in this case we've just been listing names things like that B you might be doing something much more complicated you but might be moving data to each of those sheets you might be taking each of those sheets and copying it to a separate file like any number of things you could be doing using this technique Jasmine says they look beautiful can you make them that can you make them pink you can make them any color of course good good Jorgenson's guys mojo King excellent good see plenty of people work plenty of people got this working today P NR 8 UK says are we doing press ups today I'll do press ups when we get to 60 concurrent viewers so we did have a record today we did have a record because we hit 50 but we can do better now guys we almost hit 50 yesterday so for 60 concurrent viewers I will get the press ups out again any other questions in the chat Alexander's absolutely right this is a great comment Alexander says it's good to make mistakes as long as we understand how we can solve it and don't worry the example I did at the end there well sort that out later in the course Farah door hello Fyodor good to see you Saints fan seven fantastic course so far only just catching up with a live one great to have you in the live Saints fan so see you tomorrow hopefully and Rick says hope I can make it daily Goods not sure well working from home thank you very informative you saw cheers Rick nice to have you with us today if you can't make it day if you can't make it live guys obviously I'd love to see you in the life if you can't make it live you can always catch up on YouTube I'm gonna leave these videos up on YouTube I see no reason state them down so they're always there for your reference yeah Justin says pull ups at 6 therefore 60 concurrent viewers I'm very happy to break out the pull-up so guys that's all that's all for today make sure you do your practice do your experimentation do your prey play play take this example work with it that's right you're really going to do your learning I'm loving delivering this course hopefully it's delivering some value of you we're just scratching the surface guys ready we're just getting into the basics now as we go further and further you're really going to start feeling the power of Excel VBA I'll see you tomorrow right here 4 o clock UK time and take care

Channel: Tiger Spreadsheet Solutions

Views: 16,554

Keywords: Excel VBA Beginner Tutorial

Id: oL_08gbMpZg

Length: 30min 45sec (1845 seconds)

Published: Wed Mar 25 2020