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

welcome to part 6 of members Mun of Nam started again oh no we're live we're live welcome to part 6 of 3430 excel vba absolute beginner course what a terrible starts of the weekend how are you doing sir in excel vba we have five learning blocks these are five key areas you have to know in order to make progress with Excel VBA and the good news is today we are covering the Loess what so those five areas are working with ranges working with variables loops position control and what we're going to do today which is conditional statements conditional statements so my question to get you thinking about this topic is what on earth do we mean by a conditional statement and what is the little word that we use in Excel in Formula and in VBA together started with conditional statements let me know in the chat right let's say hello to some people let me know please can you hear me and can you see me let's get into the chat welcomes everybody today who is with us we've got a mode from Bahrain welcome Ahmed Ahmed says membership isn't supported in Bahrain Ovid I'm sorry about that there's nothing I can do about that YouTube hopefully will extend the membership to more countries as we go Lee was with us busy he says he's going to play in the garden so come on Lee letting the team down I'm only joking Lee is catch later jörgen is with us the general welcome Lee again is with us fantastic see with us lately another of our members Frank is with us jazz Finn they would welcome Callum Ian from Edinburgh welcome Adrian welcome Adrian just made it but for the stream water and Stannis nut from the you claim welcome beyond Efrain denmark welcome Yuliya is with us they welcome Gary also from Edinburgh welcome Steve is with us today in Berlin fantastic Callum is with us jelly's of Etna is with us again thank you for being with us Andrews coins is with us welcome again Andrew and I think that's everyone guest Costas is with us hi and haven't seen him before so welcome Sven is with us and thank spam for letting letting me know that you can see me and you can hear me thank you guys that's a big help if you can give me a little bit of feedback in the chat that really helps me so we have some correct answers in the chat so our fifth VB a building block conditional statements the key since us we're looking at is if is if have you ever seen an if formula in Excel we're gonna go ahead you can type a number in here and I've got this concept of student grades in mind so student grades in UK University butts Queens when see although that will be really bad submission between 20 and 90 so and forty is the pass mark okay so I'm going to save 40 is the pass mark here and I'm gonna say our grades are here and then did the grade pass and I'm going to go ahead and just put a number here so pass question mark you can see when I'm doing these inputs I'm not double clicking on the cell you see a lot of people double clicking around in Excel you don't need to do that you can just navigate to the cell using the arrows and then go ahead and type something in it so we've got the great and then we want to know is the grade above or below forty and we're gonna type that in here and we're going to do that first using a formula and then we're gonna do it using VBA too and that's going to be our introduction to simple conditional statements so can you put this formula together let's go ahead equals F and then first we're being asked for a logical test so Excel is saying to us what is the condition that's what Excel means biological test what do you want me to look at in order to understand what to do now if the logical test is true and we could have any kind of logical test but usually it's to do with the value in a Cell so we're saying to Excel does the value in this cell equal this is the value in this cell more than this or less than this whatever it might be that's what we mean by a logical test and we do the same in VBA and then if the logical test is true so if that value is what we specify Excel is going to do this and if that logical test is not true so it's not met so the value is not what we specify excels going to do this so this is how it works in a formula and this is the basic concept logical test a condition if the condition is met we're going one way if the condition is not met we're going another way super-powerful stuff we're going to see it working in in VBA in a second so to complete the formula we're going to click on this cell here and the logical test is remember what was that pass smart well we've written it there so if b6 is more than 40 then we want to put pass in here so you can see I'll put the speech marks there those speech marks are important we won that text to bear in the cell and then if it's not so if our logical test does not happen if the condition is not met then we're going to go to the second part of the formula so we've got the idea of Excel going one of two ways this is what conditional statement helps helps us do so we can now hit enter what are we expecting to happen okay we've got past that we were expecting that to happen how could we test this we always test we do our experimentation so let's go 35 here what are we expecting to happen now and we do have failed now what if we have 39 we still have failed and what if we have 40 and 41 it's going to take us to pass of course if we wanted to make this equal to or more than do we put the equal sign there I can never never remember the order does the equals come first or does the little arrow first okay and Excel is trying to correct this formula for me thank you Excel so we hit yes and now yeah it's a bit better to the students now so if they scrape through with 40 they are gonna get that pass now one comment quickly about this formula can anybody offer me a critique of this formula we have got some members in the chat today this is the kind of thing we're always talking about in members Monday well done Sally and Jasmine as well I some good contributions are there welcomes pita who's made the livestream good to have you with us and eat with us as well fantastic to see you can anybody offer some criticism of this formula my criticism would be we've got what we call values embedded in code or values embedded in formally this practice of having a number in a formula is generally about practice just as having a number in VBA is generally bad practice that's because it's difficult to change if the business circumstances were to change or in this case the educate circumstance it's weird wants a weird want to let's do it like this let's say pass mark and then let's say 40 here so rather than having the value in the formula let's have the value in a cell so I'm going to hit the f2 key now that's going to allow me to edit the formula then just delete the value the hard-coded value there and then just hit the cell that has 40 in we can now and we see we're now getting the same result but if I change this to 35 now what's gonna happen to our conditional statement or if formula that that's still passing if it goes to 45 that's now a fail of course so just a bit of a side note that try to avoid values embedded in formally embedded in VBA so this our basic idea and hopefully that gives you a sense hopefully that gives you a sense of the if the concept if you like the concepts of conditional statements now you also might be thinking well if I can do this with formulae why should I use VBA to do this if you're thinking about if you're thinking that you are thinking on the right lines and to be honest with you the example we're doing today is a little bit contrived because you would do it using formulae in real life but if you're already thinking when is it best to use formula when is it best to use VBA it's good that you're accessing that debate now in your head because when you move for you through your career it's good to be able to critically evaluate the best option is the best use VBA to do this is it best to use formally to do this VBA doesn't fix everything VBA is not a panacea v8 VBA workspace with the worksheet as you've felt already on this course and vbn informally working together is like fish and chips like poetry in motion so let's see in the chat hello mom Oh welcome hands in absolutely writes as a whole coat of variable and Steve absolutely good Steve Steve saying what if the pass Mart changest he's actually a teacher in real life so he's all over this good to see Steve and Janardan is with us welcome to Laden no worries that you haven't that you're joining Lake Callum says good point yeah all of these discussions to base these are all going to kind of take over in the background and we're not going to go into too much detail on this course but as you go through your programming career you've got to think about this so this functionality can we translate it insert VBA let's say parser alt H 0 H 30 hair and let's say F 2 I'm going to type in FX for formula and then here let's say pass VBA so here we've got the formula based solution here we're going to put in a VBA based solution exciting stuff so we're talking VBA what do we need to do get into the VBA editor so visual basic here and then let's get in some nice display her I'm just gonna pull this window across a little bit old HR double which program can use the shortcuts so quite alarmingly there I was going for an Excel shortcut and it went into my streaming software on an almost shortcut is something so look out for that certainly later in your career when you're live streaming excel development so let's go ahead and okay even more alarmingly supportassist is now running on my monitor laptop here so might lose that monitor but it's not the main that so let's keep pushing on let's keep going right insert module okay we've got option explicit have you been explaining to people at home what option explicit means and then let's say sup and conditional let's say inputs input pass here something like that okay so let's get started with this so what's the little piece of syntax in the formula it's it in VBA it's also if so our logic is going to be let's be clear about the concept first if the value in this cell is more than the value in this cell then we won't pass to appear here if the value in this cell is less than value in this cell then we won't fail to appear here so if you're watching on replay go ahead and see if you can put it together yourself it's not too it's not too bad this one so if so the first range is b6 being careful with the syntax there so the speech marks around the cell references if b6 is more than and let's say raged e3 okay then so we've got our basic syntax going in there if at the beginning and then at the end and let's set up the whole if statement now the if statements going to go into one of two directions just like the formula did we have our logical test then if the test is true this is happening if the test is false this is happening in the same way we're going to work in two different directions with if-else ands ends if so this is our logical test our condition at the top if this condition is met we're going to go into this part of the code here this part of the code hey can just see my cursor flushing and then if the condition is not met we're gonna go down to this part of the code here so it's a bit like the formula that's why I always demonstrate the formula first the way it works is very similar so what do we want to happen there so now we're working with ranges going back to our building blocks seeing these techniques combined together so this is d6 HD 6 equals 6 equals pass that if the condition is not met then you could say I just copy-paste it that so how do I do that with the keyboard hold down the shift key and the left arrow then ctrl C on the keyboard then going down repositioning the cursor control V to copy and then so this code looks like it's going to do the job just do my sense check now so d6 yes that's the right cell and then d3 yes that looks like the right cell and then b6 also looks like the right cell so I can go ahead and now I'm just gonna spread these outs you know the spacing doesn't matter for the execution but I think it just makes things a little bit clearer in terms of the display yeah people in the chat saying Peters saying more or equal to should we go ahead and put that in now and I am I going to put it on the wrong side yeah I did put it on the wrong side and exhale fixed it formally so thank you thank you there to excel so the equals goes after the arrow in the VBA editor I'm going to hit the f8 key now so stepping through the code you can go debug step in so step in sue or Steve you're now gonna write in the charts the shortcut for Mac the shortcut for Mac for stepping through the code so you can see let's start that again so exhales now checking the condition because the condition is true let's make this a bit more kind of explicit here and start again because the condition is true we're gonna go into the first part of the conditional statement and we should see pass appear now that it's what if the condition was false okay now expecting the opposite things happen hitting the f8 key and going back through the code what's going to happen here you can see we've skipped out we didn't go through the first part of the conditional statement into the second part of the conditional statement and keep your own cell d6 we can see fail appearing there and did you see how our formula updated as well at the same time that was interesting so because we imported a value into the spreadsheet Excel calculated everything and our formula updated soup pretty cool okay so basic conditional statements so what do you think are you following in the chat Steve saying so who can tell me the Mac shortcut for stepping through the code somebody yesterday had it I think one of our Spanish viewers had it yesterday so many posted it in the last rinse was the end so if you just head on to Google again another excel meta skill what what we've got a problem what are we going to do we're just going to give up no just head up head on to Google Excel VBA Mac step into shortcut you'll be able to find the keyboard shortcut that good 41 viewers today amazing over 40 viewers and 10 likes thank you very much for being with us so let me set you a challenge and again it'll be good idea to stop the video now and see if you can do this so say we had 10 students here I'm gonna go ahead and generate ten random grades can be tempting to actually mark papers in this way to generate grades randomly don't recommend it so run between let's use the run between formula here if you don't have the room between formula on your system for whatever reason you can just type these values in manually so run between we can choose a random number between two values so again that states when C it's a 90 here and you can say I've put the comma in there hit enter and then just you can just drag this down but I prefer to use the keyboard so as make sure the cell is selected hold down the shift key then we want to go for about ten rows and then ctrl D on the Windows PC control D on the Windows PC is going to autofill down we've got 11 there I can see at the bottom of Excel let me just point that out to you because that is useful at the bottom of Excel down here excels counting the number of cells that we have selected and I can see sorry the number of cells that have values in so I can say I've got 11 cells there just delete that so we've now got 10 cells so you you we've now got some random values in there if you now go to let's go back to the big view or they're all this live editing if you now go to formulas calculation options and calculate here you'll see those random values changing that's because every time Excel calculates it's going to change the random numbers which is not ideal for us actually you can just hit the f9 key to have the same effect hit f9 you can see those random numbers changes changing so let me know any teachers in the chat going to be using random numbers to create their papers next time it's only a joke of course I'm not recommending this so rather than having random values that we better to have normal fixed values so let's go ahead ctrl-c and ctrl-v on the windows pc holding down control and all and hitting V and let's just convert these two values so now we hit values at the moment these are formally we can sell because we've got a formula in the formula editing but so let's convert some values hits okay and now we can see we've got values there so when I hit the f9 key now to get Excel to calculate these values are not going to change so we've got a challenge now now we can see one of the benefits of using formally as opposed to VBA we can just double click hair and that formula is going to come down although there is going to be a problem with it can anybody in the chat see the problem with this formula nut formula now what kind of referencing are we missing here let me see in the chips anything I can help a a stanislovas but Senate service put the keyboard shocker in the chat thank you for that stunning stuff Henrik says how do we utilize method copy along with if-then mmm Henrik well we're going to see how we can scale up this approach we're going to incorporate something in a second to scale is something not quite sure if that's what you mean and Beyond it yes a slum Mellon for run between of course in Danish very good in Stanislav thank you for the contribution there in another language and good Eduardo saying I'm guessing we check out Eduardo's contribution in the chats good and Lee is on the money here yep we need an absolute reference here so as we can see when we copy the formula down I'm hitting the f2 the f2 key when we copy the formula down we can see this reference for the pass mark is coming down as well that's because it's currently a relative reference we want to make it an absolute reference so go to the first formula click in the bar and then what's the keyboard shortcut on windows the f4 key hit the f4 key we're going to get the dollar signs have you ever seen the dollar signs and spreadsheet and wondered what does that mean it's an absolute reference so wherever that formula goes that that reference is going to be fixed it's absolutely it's going to be fixed to that cell so again now control D to take this formula down and we can see hitting the f2 key that's we're referring to the correct cell and just doing a sense check I can see at 44 is the past 26 is a fail so this seems to be working well so we're doing that we've formally there we want to do that with VBA a bit conscious of time so I'm gonna have to have to push ahead with a little bit of pace here yeah Edoardo absolutely right so what do we need to add to our code to get this done so I want to populate these cells here with the right information so is the grade a pass or fail but we want to do that program programmatically we've done it formulaic ly here we want to do it programmatically here using Excel VBA the first step is always the concept yep the mistake people make they jump in let's start coding let's try to fix it try to take step back and think of my building blocks I've got a couple of building blocks hair what's going to help me to get this done and just talking about it out loud you know you'll often catch me in my office I'm not saying going to come and stand us on my office or something though would be strange but you'll see me a mouth is literally walking around my office speaking out loud how I want the code to work you've got to actually say it in your own language build that conceptual understanding first well we know one thing we want to repeat an instruction don't we this instruction we have for cell d6 we want to repeat that so we have a repeated instruction what does that mean that means we're going to need a loop and again let's use a for next loop like we did yesterday and you can even use some of the syntax that you have yesterday maybe you're in the same workbook or something you could even copy paste it in to get this working faster so with the for next loop we have for counter equals 1 to 10 we have 10 values that should be fine now we've opened the loop we need to make sure we close the loop straight away and I guarantee some of you gonna get into problems here when you have a conditional statement inside a loop this can be problematic so you've got to be on top of the syntax here you'll notice what I did there I just indented just indented the conditional statement so kind of emphasized all of this syntax it belongs together it's all working together that's called indentation that's good practice in VBA good practice in computer programming generally ok so now what do we have to do to get this all working so we've got our loop in there we're going to have a repeated instruction if I step through the code now it's not quite gonna do what I want it to do so what else will we need hair absolutely got some good contributions coming here beyond it says for each we could do this using a for each loop I'm using a for next loop today because we've previously done a for next loop we'll come in to come on so for each loops later ok so what's the other kind of ingredient the other building block we need hair because we're going through the loop but we need to achieve this kind of moving down moving down so it's to do with position of course which means which means offsets offset is going to help us around me and go back to session 3 or 4 was it there we go stanislav is on it in the chat fantastic stuff so arrange b6 I'm gonna start in b5 but this anchor point in b5 why do we start in b5 while counter equals 1 first so that's going to take us into p6 once we set this up arrange b5 dot offsets be honest on the money too as well well don't be honor offset counter so remember offset rows down first and a positive number takes us down columns across seconds and a and a positive number takes us to the right as we as we look at the spreadsheet so we don't want to go any columns across we just want to go rows down so where is it so if counter equals 1 we're gonna start in b5 start in b5 and then counter equals 1 that's gonna take us to b6 so that sounds good okay so what's going to happen now let's do a kind of intermediary test to see what's happened I don't think this is going to get it get it get it done but it's it's going to allow us to learn about what's going on and we should be able to fix it so I'm going to check the value of the variable counter is 1 b5 offset counter 0 yes that looks good to me so we're going to go yet the value is 20 in cell b6 so the condition is not met so we're going to go to the second part of the conditional statement we're going to see fail in there now counter equals 2 so now we're going to look at cell b7 so b5 two rows down cell b7 so where are we gonna go now ask yourself the question so the condition is met now so we're going to go into the first part of the conditional statement and we're going to see pass appear in cell d6 and that's how it's working so what do we need to do to finish this off got the mechanism working there nicely what do we need to do yet good skills Chuck steve says you know great contributions in the chat today and loving the little little sense of community that we're getting already in 30 for 30 getting the same people turning up every day a few a few new people turning up Freddy with a good contribution here Freddy is saying why not create a button to make the VBA testing faster great shot Freddie let's put a button in let's put it at the top here so putting a button in is a very routine job in VBA so we've got a practice set hold down the Alt key hair and import passes the macro that we want to run when we click this button right click to edit the text and then we're just gonna say grade so it's a bit like grading papers if any were this easy right how do we finish off this code very good from stanislav good yeah and just been as really israeli on the money hand somes of the detail let's make all of these anchor points begin on row 5 yeah just for consistency here row v rho v and row 5 and then we can sure be else just copy paste this syntax so holding down the shift-key using the arrows there control C control V here and control V so we can see yeah there is some repetition in this code that would be my critique and there might be a way to write this more concisely avoiding the repeated constructs here the dot offset counter constructs but this should do the job for us ctrl s save the file do my sense check now does it look reasonable does look reasonable enough so we've got a button in here we should be able to click the button and let's see what happens looks reasonable enough yeah we've got passed quite a fail here got a fail here pass pass pass and a fail at the top looks reasonable reasonable enough let's put some different values in change you to 50 change you to 0 and then let's hit this again and we can see yeah it seems to be working well there so that's your introduction to conditional statements guys that's pretty powerful stuff I mean we're just using 10 rows there a little data set with 10 rows you could have a hundred rows you could have a hundred thousand rows you'll be able to scale this up very quickly to get this done and bear in mind the discussion we've had yes you could do this with formulas vba presents some advantages this particular task you know if in some situations you might want to use VBA to do this but typically you'll probably actually use formula to do this kind of thing typically although you could argue VBS in some situations would work better it's kind of a contrived example it's a learning example for us to see the power here of conditional statements not just the power conditional statements but conditional statements working with our other VBA building blocks particularly loops and position control putting values into ranges to get this job done how about that okay any further questions please any further questions in the chat and yet just state your questions in the chat I'll answer them before the end of the stream but today special day today because we are going straight into members Monday pretty much straight into members Monday anyway at 5 o'clock we're going into members Monday so you'll see people in the chat with a little symbol by their names it's like a little S symbol by their names their channel members so and if you have members Monday level membership you'll be joining us for our members Monday live stream so this is where we take a real-world VBA task work through it together I'm gonna tell you exactly what I'm thinking the kind of decisions are making like today we've been talking about the merits of using formula as opposed to VBA taking a real-world practical perspective hopefully boosting your excel vba skill women do members money for about three months now had some really good feedback people loving it and we're building a nice little community and we've already had some people join from the 34:30 streams will be getting started in about twenty minutes five o'clock members Monday number 17 I love so you love to see that yeah we've got some good contributions in the chat Frank has put some syntax in there so a different way of doing the same job yeah I'm liking what you're saying Frank so give give Frank solutions Frank solution ago if you want a little extension to this task be honest says I work with robots and it's preferred to execute a macro overfilling in okay so be honest saying there yet there's certainly some situations where it will be better to use a macro rather than using formally the main benefit of using a macro is you don't have formula setting in the spreadsheet if we look at these values here we can see that these are literally values so look at the editing bar at the top there's no formula there if we go across we do have formally that that now if you have lots of formally they can slow down calculation in Excel you've probably used a kind of slow spreadsheets so that's one a bunch of VBA means we don't have formally hanging around in the spreadsheet Li says do I come back here for members Monday members Monday I post a link in the community tab on the YouTube channel so go to the YouTube channel on your PC go to the community tab and you'll be able to see the link to members Monday obviously it's only for members so it's not generally available Adrienne says another testing session for this evenings be completed absolutely guys if you really want to push your heads don't just be satisfied with the examples I give you go away work with the code do some testing do some experimentation I'm always talking about play and so that's gonna really build your learning Pizza says I have Asperger's I'm here over ya allied out pizza I've seen people with kind of similar conditions be very very good programmers attention to detail is so important in computer programming Stanislav we are waiting for new lessons we'll be back here four o'clock tomorrow stanislav Yuliya says thank you welcome jelly's retina says thank you for the lesson very informative excellent the general says thanks a lot Krissi I salute you respect to the general see you again tomorrow and Lee says thanks Chris I'll see him members Monday and be honest says yes that's an advantage of VBA of course if you have big datasets and Olga Olga I haven't seen you in the chat before but welcome and you're welcome I was going to say welcome and thank you but you're saying thank you so who's saying thank you saying you're welcome crazy will say thank you to each other and I hope to see you all tomorrow if I don't see you in members Monday hope you taken care of yourself especially can the Sun shines out here in the UK I've been out for a nice walks and I've got to do thirty thirty with members Monday to come as well so I'm having a decent day take care of yourself guys I will see you tomorrow at four o'clock UK time

Channel: Tiger Spreadsheet Solutions

Views: 20,189

Keywords: Excel VBA Beginner Tutorial

Id: bOw69Quz8jE

Length: 33min 24sec (2004 seconds)

Published: Mon Mar 23 2020