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

welcome back to 30 for 30 let me know in the chat can you see me can you hear me it's good to be with you again today and let me hit you up with a question straight away get you thinking what about position control position control have you ever thought about position control in Excel what on earth does it mean position control was actually one of the most critical skills for a VBA program we're going to be getting started today with a key technique that I absolutely love that super powerful position control so Excel gives us a framework here an infrastructure if you like for us to work on it's a bit like a blank canvas for us to create something beautiful that's that is the way I think about spiritual development I have to say what does it consist of it consists of cells these cells are organized across worksheets and then we might go on to the next level which is having different files so our ability to control position which means to control the cell that we're working with when we've got all these cells on a single sheet we saw yesterday we've got millions of cells on a single sheet and then we've got other sheets as well and possibly other workbooks that's a complicated situation we've got a control position as best we can so my first question for you before we get into the chat and say some hellos what VBA technique is gonna help us with position control and I can see some people in the chat are on the money already so well done guys position control what do you think let's get into the chat say hello to some people I hope you're doing well hope you take care taking care of yourself taking the care of the people around you it's a pleasure to be with you today hello a murd from Bahrain welcome Craig is with us take Craig making his debut in the live chat Walden for say hello good to be with you Craig Adrian is with us from Wales fantastic Lee is with us now Lee has a membership badge next his name if you want to hear about channel membership taking your VBA to the next level click join below this video all details of that your organs here the general set let's make sure we salute the general welcome jörgen Graham is with us welcome Graham from up in Scotland Jasmine is with us California also a member well Jasmine Ian from Edinburgh stuning and who is lots of people today Stanislav welcome from the UK and fantastic Johanna is with us good afternoon gu7 Spain welcome andrew is with us as well welcome Graham s is with us welcome be honor from Denmark is with us welcome be honor will a Willem is with us welcome with him how are you Steve mandra's with us welcome Paul Taylor is with us long term support the channel good to see you Paul Sven is with us Thank You Sven says he loves the videos p NR 8 UK is with us welcome and we have jelly's there Buettner Shevchenko's with us welcome I haven't seen you before in the channel good to have you with us and I think we've covered everybody there so welcome I'm Freddie just tuning in at the bottom that Freddie from Canada welcome Freddie so what about position control what VBA technique is going to help us and we did have a good answer in the chat somebody has given it to us goose' has given it to us ball has given it to us as well Stanislav fantastic well done and hello to Andrew and Mohammed so off set of sets so again I like to think how did these formally get their names how did these concepts get their names and I like to think of Steve juror Nastya Steve Jobs I'd like to think of Bill Gates and his mates when they were kind of thinking of Excel sat around the water cooler in Seattle maybe and thinking you know what are we going to call this formula and as with many formerly in Excel many things in Excel it's often not easy to understand what it is just from its name so I went online today to the old online dictionary offset so what of these definitions is going to help us here this definition might help us here the amount or distance by which something is out of line and then this third definition down at the bottom here several suddenly placing something out of line hopefully that gives you the sense of position we've got three ideas when we're dealing with off so the three ideas and anchor points that's where we're starting the number of rows to move down from that anchor point and then the number of columns to move across from that anchor points are only three things an anchor point rows down columns across and then we're going to return the value of the cell that we get to because this concept is so important we're gonna try it out in Excel first in the worksheet as a formula then we're going to transfer to VBA and get it working in VBA too so let me know in the chat are you going to be working along with me today I hope so right today we're gonna need a little table of numbers here I'm gonna go ahead and just create some random numbers using the randbetween formula here if I hit that's happy now exhales going to put the formula in for me and then I can have a bottom and top parameter in there and Excel is going to but a random number in there how exciting is that yes Excel gives us random numbers just fun I think to be able to generate some random numbers these are interesting when we do things like simulation modeling in Excel so hit the f9 key now if you hit the f9 key Excel is gonna calculate and you're gonna get a new number how about that rather than hitting f9 you could go to formulas at the top then over on the right so let's just go to the full view here go to formulas at the top over on the right make Excel calculate by hitting this that's icon I just clear hit in the top right corner so when Excel calculates Excel is going to create a new random number how about that so don't you want one number less let's create 16 random number so I'm just clicking and holding the drag handle and we can see the random numbers there so if I hit f9 again on the Windows PC we can see we're regenerating those numbers we want to fix the values though we want to fix the value so ctrl shift holding down ctrl + Shift + Windows PC rights arrow and down arrow those keyboard shortcuts are so important try to practice them ctrl C it's a copy ctrl Alt holding down ctrl alt hitting V for paste special paste special and then V for values that's going to convert these formally into values which means now what happens when we hear f9 I'm hitting f9 now Excel is calculating but nothing is happening that's because we now got value say you see we don't have the formula there's no run between formula there so we've got a nice little table of values this is going to be our playground today so I'm going to go ahead and inputs and offsets formula here again hit that's happy now excels going to put the formula in for us why do any additional typing hit the tab key the formula is in there and as usual we get our prompts we get out front so Excel tries to help yeah exile is telling us what it needs to make this formula work and can you think back a few minutes what were the three ingredients we need it to make offset work we need three things we need an anchor points and X I was going to call that reference I don't know why anchor point would probably be more informative an anchor point a start points and then the number of rows down the number of columns across so our anchor point I'm going to position it just in cell c4 there so one cell to up and one cell across or across from the beginning of our table that's going to be our start point and then I'm going to hit a comma I'm gonna then hit one for rows then comma again one for columns and now as we're putting these components in you can see how Excel highlights what it's expecting next in these prompts so let's start c-4 I can see references highlighted reference is going to be highlighted in bold until I hit the comma key then Excel is saying right I'm ready for the next piece of information I'm gonna hit one and then hit one again so the question I've got for you is what value is this formula going to return let me know in the chat what value is this formula going to return let me know who else is with us in the chats I think we picked up everybody ate yet Freddie Andrew welcome Mohammad is with us Union from the UAE good to see you Muhammad welcome Daniel Daniel's here from Holland hi Daniel how are you and then we've got some goods contributions here ok Stanislav I'm bianna very helpfully are translating these formally into other languages so in russian stanislav thank you hands be honor his i can't believe that translation slump melam slump melon means offset in danish magnificent stuff yeah that's really helpful guys thank you and we've got some people who are getting the right answer here so what's going to happen I'm gonna hit enter now and we have a value of four that that's because Excel is started here this is the anchor cell or what Excel calls the reference it's moved away one row down one column across and then it's returned of value from the cell that it's got to that so again now we're in experimentation this this is your blade time let's have some play with it what if I do four here again stop the video what value is this formula going to return now if you're guessing this then you are then you've got the conceptual understanding the only way to get to the technical understanding is via the conceptual understanding when people are struggling in Excel they're always thinking oh the technical stuff is impossible often it's not clear enough in your own head what's going on so let's get that concepts at first what value we gonna return here okay a value of two so we've started at our reference point in c4 then we're gone four cells down once one two three four and one cell across to return a value of two that just to prove that I'm gonna change the value in this cell and then we can see it changing that cool I love offset I don't know why it just gives you so much power in Excel to get things done and as I said that feeling of accomplishment when things work even just with this simple example never fully goes away so some really good contributions in the chat yep we've got some people who got the correct answer there fantastic stuff is it possible to use a negative value it is in offsets and and this is something for us to cover now maybe so if we started here let's let's have a different anchor point let's have seen nine as an anchor point here I'm just going to highlight that cell for you ctrl c ctrl alt VT to take the format's now if I wanted to return this value eighths what would we have to do with the formula difficult question but it gets us thinking about negative values with offset we're dealing with the rows first anchor point and then rows positive value goes down because we can see the numbers yeah we kind of count down positive value goes down and then a positive value in the columns is going to go across that way he's going to go across to the right so a negative value is going to go left and a negative value in the rows is going to go up so if I say negative one here and then we want to return this value of eight in cell e8 I'm gonna go negative one and two negative one is going to take me up and then we're gonna go to columns across so they should now return a value of eight there so the anchor point is here started at C nine we've gone one row up two columns across and returned this value of eight there so let me know how you get on with this yes it got some very good participation here in the chat fantastic stuff over 40 people watching as well this is magnificent I think that's a record so far for thirty four thirty so are you ready to open the VBA editor now now we've got our conceptual understanding let's go ahead open the VBA editor and that's actually actually open quite nicely lined up quite nicely there oh where does code left can you remember from the first session we're gonna go ahead insert a module and then what's our random turn that we write to the top that we learnt about yesterday so you should be able to explain what opportunity is maybe that's what you're doing over the a table yesterday explained what option explicit is your family maybe not I don't know I don't know and let's say offsets experiments because this offset functionality we can access it in the worksheet with the formula we can also access it in VBA with the VBA method the VBA method we're gonna see it in a second not all formula translate into VBA like that I do want you to understand that not all formally translate into VBA as smoothly as as that although there's different ways for us to access formally from VBA we're gonna get to that later maybe on a thirty four thirty twenty five or something we'll get to that later on but offset translates very nicely into VBA so let's go ahead and try the syntax here and how to do this let's have yet rather this cell here let's have an answer cell okay alts HB a alt H ba on the Windows PC is going to give us all of the borders that I'm going to go ahead now and just clear yeah let's just clear this one all th e a it's gonna clear formats let's go ahead try to translate this into VBA okay so I want something to appear in this cell and what I want to appear in this cell is the result of a little offset calculation that we're going to do so let's give it a go so this is b3 so if I say range b3 here that's where we want the value so pet and then I'm gonna offset from the previous anchor point we were using so we pop that in first that's our objects now it gets gets a bit more interesting the since acts we're going to use a dots straight away and then we're going to say offset there we go now if that offset is called a method don't worry too much about the technical that the the language stuff it's called a method when we try to do stuff in Excel for example offsetting away copy selecting if it's an action we call it a method what else might you see after a dot in VBA a property a property dot value dot address whatever it might be so properties or methods we see after the dots and we may also see another objects after the dot I'm probably confusing confusing you now but you all get used to kind of the structure in the main features of the VBA language so we're gonna say dot offsets and then let's go ahead and say we want to get the the first value so a nice easy example first so 1 1 so just like we did with the formula so we're going to start in C for move away one row down and again we get we get the prompts in VBA how good is this exhales actually helping us here row offset how many rows do you want to go down how many columns do you want to go across there whoops just hit answer just delete that ok we should be good to go there any interesting stuff in the chat some good contributions here Alana's with us welcome Elena also from Ukraine good stuff yeah we have a question here can I get the last row through offset yes that southern will gets you later in the program getting the last row the last row in a range of data that's something people often want to do in Excel will get there don't worry it's got to be patient with me right let's give this a go so what's going to happen when we play this macro so b3 yeah it looks good okay what value is going to appear in b3 that's another question for the chat what value is going to appear in b3 when I play this macro I'm gonna play it now and we can see for appeared in b3 I'm gonna go ahead change this value to to play the code again so I'm just clicking the cursor inside the macro remember sub n sub tells us the starting points and the ending point for the macro we have an informative name for this macro so I'm clicking inside the macro hitting the play button and we can see we've got a value of 7 there why is that well we started at c4 whence who rose down one column across suppose want the last value in the dataset in this little table with dots and we can do that and hit play we can see we got to that and again prove this prove this to yourself then hit play and you are using offset in Excel VBA you are now controlling position in Excel VBA using offset how cool is that good stuff okay how are we going how we going in the chat let me know if you're following along with me here so let's take this to the next level here what we'll often see in VBA is combining techniques together like it's it's like we're learning these techniques in silos we learn about variables yesterday before that we learnt about working with ranges we're learning these techniques insight in silos the techniques themselves don't get any more complex this is a complex as it gets the complexity comes from and the power comes from combining the techniques together and that's what we're going to be doing more and more over the next few streams it's going to get more difficult but there's this combinatorial explosion is that that's not quite the right word there's this synergy this synergy when you combine these techniques together very quickly things get very powerful and very cool indeed so let me show you two ways you can combine techniques together using offset so let's suppose rather than typing the values in here I want it to type so let's say number of rows I wanted to type values into the spreadsheet and then for VBA to read those values of the spreadsheet how about that alt H be a on the Windows PC f4 key repeat the last action so what number of rows number of columns here I want to be able to enter values into these cells and then for VBA to read those values and to return the value that we get to so for example number of rows let's say let's say let's just do the easy one first one row and one column so we want to this value for in d5 right if you're watching on replay and it was great on Twitter today Brian on Switzer was saying III just watch the replays I love the replays that's all good with me if you're watching on the replay stop the video now and see if you can get that to works get vba to read those values from the spreadsheet combining techniques together combining offset with working with ranges this is when it starts getting really exciting let's say now how can you use Excel to get football form and data is starting to click on my brain it's a really good point like what you'll find if you stick with me is you know you've got all these kind of goals that you want to do in Excel something you're trying to do at work Lee is working with football data I've got loads of stuff I'm trying to do in Excel for me and for projects and then at the other end you've got all these techniques that we're learning gradually these these are going to move towards each other so you'll be like oh I've been trying to do that in Excel maybe I could do that using loops which we're going to learn tomorrow or the next day gradually the two will converge the things you're trying to do and the techniques that we're learning you're gonna see how you can do the stuff you want to do but it's gonna take time believe me it's gonna take time I've been doing doing this for 10 years I'm still kind of working that out myself what it needs to use to get things done but the journey is so exciting I'm jealous of you for having this kind of learning journey ahead of you if that doesn't sound too cheesy so let's go ahead put this syntax in and hopefully you're fairly comfortable with this syntax now so the number of rows is in b6 speech marks b6 then I'm gonna copy this across ctrl C then ctrl V and the number of columns is in b8 there ok so what's gonna happen now just wondering if we can view this a bit better all textured w3 on the windows pc f4 and then let's change this back to a value alt h o w3 again that just allows us to see things a bit better that's makes a bit bigger actually whole texture w-4 so now we can actually see what's going on here okay so again when we run this code what's going to happen now when we run this code so I hope you do your own experimentation home trying of more examples you know the level of experimentation I'm doing is it's nothing compared to what you should be doing at home testing all those different examples building up that muscle memory in your fingers to actually write this code build your understanding that way so what's going to happen that's that's my question for you let's look in the chat here jelly's about not welcome and I've already said hello to you thanks for this tutorial now I can put this into practice at my work you tell everything in great detail thank you yeah it's my aim to get you to create some kind of real world value for yourself in work outside of work whatever it might be you know that's what I'm doing for my job you know creating real-world value how would be able to get things done faster get better insights etc etc and Waldo the feeling of accomplishment yes get addicted to that get that positive addiction Lee says thanks Krista explaining that loops feels like a kid on Christmas morning this is gonna save me loads of time if I can master VBA absolutely I'm loving people's attitude in the church it should feel like play so it should feel like you know you're a child you've got a new toy it's exciting right let's go ahead play this code just checking it'd be 6 B 8 B 3 and C 4 so that's called a sense check a sense check so I'm just reading through the code does it look reasonable have I made any shocking errors I make them all the time that's why I do sense checks let's hit the play button here and we can see four has gone in here okay what if I change this value to two now again what do you think is going to happen experimentation play we've got seven or if I wanted to return the value from f5 while we're starting here so that's one row down and then going across so that's three rows I gotta go across so what happens now when we play the code and we can see the value of tan appears they're just to prove that just to prove that I'm gonna put a C and and then play the code here and that's its so officer you're now doing position control but not just with hard-coded values which was our first example you're combining techniques together we're saying the worksheet working with the VBA editor to create this beautiful synergy if this this looks fun we're just scratching the surface guys stay with us you are going to love it be honest says we get for absolutely B honor good stuff an agent says dynamic result using ranges to determine offset properties exactly that's exactly was it what is Adrienne goods Craig says conk you up due to screen issues starting to pause okay let me know if anybody else has got issues with the stream I'm getting good stream health I have a little a little kind of interface here YouTube live control room is what it's called very exciting and it does say the stream looks healthy at the moment 34 people watching and 8 likes fantastic stuff right finally here we're using ranges to store information which is you know very common thing to do in Excel people put values into ranges what's another place to store information that has just become available to you in fact it just became available to you yesterday when we did variables I think I just gave the answer away didn't I I think I did but can you get this working using variables how about that offsets and variables are like fish and chips fish and chips you can tell you're in the UK fish and chips beautiful combination let me know what's the equivalent of fish and chips in your company beer in your country bianna in Denmark have you got what's the equivalent of fish and chips there let me know ally Otsu in the chat will welcome good thank you for people who says the stream is fine so let's go ahead and declare two variables if this is totally new to you go back to yesterday's stream which is number three I always do that number three that's five number three and see how we declare variables and understand what we what we use variables for so that's a number of rows here what kind of variable is this going to be remember yesterday we not set a few different variable types including long variable string variables boolean variables we just we just kind of just met them for the first time and then later we're gonna actually try to use them what's the variable type here that we want well we're dealing with a number it's going to be a whole number as well because we can't go one-and-a-half rows down it has to be a full number so an integer variable is going to help us here PNR 8 UK says our variables case-sensitive I don't think they are I think if you actually writes a variable with the wrong case there you can see I've got small letters is Excel yeah Excel will go ahead and kind of correct the cases for you there so the cases don't matter so we want to create two variables here I'm gonna go ahead copy paste and then number of holes as integer so we've got two variables then when you declare a variable it's a good idea just put a little note for yourself put a little note so it inverted comma and just say number of rows to move across and then here number of columns to move across I should say number of rows to move down of course there we go so that might seem like overkill you know that's we're doing that for completeness now not necessary but later in your code in Korea you're going to thank me you're going to say oh that's what Chris said if your code is well-organized and you have well annotated code it really helps you understand Grahame s absolutely right and did your variable is just say range the VBA editor and Excel at the same time have you got this setup at home having two screens really helps I'm working on one screen today but that's possible to do don't say the streaming is the stream hell it's not too good at the moment if the stream stops then make sure you refresh there's nothing I can do about it at the moment as far as I can see so just going to try to plow on here yeah make sure you refresh your browser hoping you'll be able to see us so say number of rows here and then number of columns so I've taken those variable names and replaced those ranges with the variable names there so what's gonna happen here let's reset these two one one what's going to happen and what would be a good way to test this just doing the sense check b6 + b8 okay so let's hit the f8 key here there we go I think the stream should be back now I'm back to excellent health care so apologies for that I think at the moment that's because there's so many people using the Internet's it's gonna be a little bit of breaking up so hopefully that wasn't too bad thank you to the general who says the stream is perfect hip so apologies for that so I'm gonna hit the f8 key now or you can go debug and step in suit debug ins debug and step into that and you can see the VBA editor goes into break mode that's because this is like getting get getting the hood open on your car because you want to understand what's going on I'm gonna hit the f8 key now so the number of rows variable now has a value allocated to it a number of calls variable now has a value allocated to it I can just hover my cursor over the variable to see the value so what's going to happen now in b3 we can see the value of 4 has gone in there let's try another couple of values here to ensue and Gaye resets Helms gonna play the code this time hit the play button and we can see 5 has got in there let's do one more final one today 3 let's three and two so gonna start they're gonna go three rows down and then two columns across so this should return a value of the six for us there and we've got a value of six there there we go good stuff so let me know guys any final questions in the chat but this is you know seems like just silly little examples doesn't it and you you might be frustrated that you're like I'm doing a VBA course you know I can't see myself using this in my work but these are just little examples for you to practice the skill and if you stick with us you're gonna see those skills building up as we combine these techniques together and we've learned offsets at a super powerful position control we see offset combining with other techniques putting putting values from the spreadsheet into to work with the offset method as we did first where we had dot offset range b6 for example and then having variables and having variables working with officer that as I said that's like fish and chips there's a beautiful synergy between those two and as we combine more of these techniques together you're going to see these synergies and it gets really exciting with VBA programming let me know guys any final questions before we say farewell for today Stream seems to be back online so that's good to hear apologies for that so be honest says I love fish and chips - oh yeah I love fish and chess but no fish and chips at the moment of course Paul says yeah we can have spin buttons to change the row and column numbers absolutely yeah you could put some spin buttons in here just Google Excel VBA spin buttons to find out how to put those in yeah be honest says you can also activate view locals from the view menu yep you can have a look at that I won't look at that now don't over complicate things but that's a good way to understand the values held in the variables and he says commonly view given all this hell for $12.99 a month you explain it so well and easy to follow it's a bloody bargain with all the videos I really I mastered excel key see you tomorrow Chris as some very nice points of finish on Lee and Lee has signed up for our membership and let me tell you one more time abouts members Monday so members Monday is a weekly live tutorial with me and you might be saying well I'm getting it all for free I'm getting it over free now well that's true but in the Monday stuff we go into a lot more detail so not just talking about the techniques talking about applications um members sending me files that they're working on delivering real-world value but also how to manage a whole project you know if we've got this problem what's the best techniques to use what are the pros and cons of different techniques really trying to develop what I call a critical approach to projects and this a little testimonial here from bart's who's one of our members a really excellent member he helps me out with members Monday but hasn't been with us today but I'm sure we'll be watching the stream later sir hello Bart's and his Bart's little testimonial about members Monday so it's much more than just a normal video much more than reading a book and I think I think it's a totally unique offering at the moment online to have you know a practicing consultant trying to try to share the craft and really help people to live a real world value with Excel VBA so if you're interested hit join below this video I would love to see you in members Monday there's all kinds of other benefits as well that come with members Monday so that concludes my sale pitch I would love to see you there so are you going to be with me tomorrow I hope so loving doing these sessions so take care of yourself take care of the people around you stay positive if you can get outside have a little walk do some exercise go for it go for a jog walk the dog whatever it might be stay positive I'm looking forward to seeing you tomorrow take

Channel: Tiger Spreadsheet Solutions

Views: 11,291

Rating: 4.970149 out of 5

Keywords: Excel VBA Beginner Tutorial

Id: 0VzA5d1JrlE

Length: 35min 2sec (2102 seconds)

Published: Sat Mar 21 2020