Excel Macro Class 1 - Getting Started Programming Macros

here I'm gonna cover what a macro is and the basics of how to create one as well as giving you five examples that will get you started creating your very own macros before we start check the video description and click the link to teach Excel so you can download the files for the tutorial and follow along and make sure to subscribe and accept notifications so you can see all the new tutorials ok so let's start at the very first step what is a macro a macro allows you to automate a task in Excel it could do anything from formatting a cell to adding data to cells to taking data from cells anything that you can do in Excel with a mouse and a keyboard you can do using a macro so it's just automating tasks essentially and it makes Excel really really powerful in this tutorial I'm not going to cover macro recording so this is all about programming your own macros that means the first step is to go to the VBA window to do that we use the keyboard shortcut alt f11 it's going to take you to this window here and this is where we're going to create our macros now the first thing that you want to do is to insert a module this is where the code will go so go up here to the menu insert module and then usually it's not going to be fullscreen like this it'll be a window like this here and now to create the macro we first have to kind of make the outline for it the box or the container so we type sub space and then the name of the macro let's call this one selections and data then just hit enter and I should automatically fill in the end sub and an opening closing parentheses here now for just starting with macros I'm not going to cover what you could put in between these parentheses or anything outside the sub and end sub let's just stick with the very basics the one thing that is important to note is you can't have any spaces up here in the name so that's going to throw an error so just leave it it simple easy to read names if you want you could use underscores but that's not really necessary so let's get started I'm gonna have five examples here and each one will have a comment above it that explains what it's going to do a comment is what you put into your macro so you can better understand it when you view it later you put a comment in with a single quotation mark so this is a comment and once you hit enter and go to the next line it will turn green that's how you know it's a comment first thing let's select a cell now there are a couple different ways to do this I'm going to show you the easiest and most intuitive way simply type range open parenthesis double quotation mark and then the cell to which you want to navigate let's say cell c2 close quote close parenthesis then type a period and now this is very interesting the little window here is everything you can do with this range here we're gonna keep it very simple type select hit enter and now we have created our very first macro that is going to select cell c2 now if you want you can run the macro from here hit the green arrow up here in the toolbar or hit f5 but let's go back to the workbook and run it from there so we hit alt f11 to go back to the workbook now hit alt f8 to go to the macro window this will show you all the macros available to you in this workbook or any open workbook select the macro we named it selections and a data then hit run and you see that the cell that was selected is c2 so I'm going to select another cell watch that again alt f8 run the macro watch the active cell change BAM your very first macro let's go back to the window alt F 11 now what I'm going to do is I'm going to comment out each example once I finished it so I put a single quotation mark and that means that this code will no longer run so will no longer select range c2 when you're viewing this workbook simply remove the single quote and then you can run that code so for the next one let's select a range very simple just like the first example except for we type a range in there so let's say a1 2 c2 alt f11 all to f8 run and now it's selected an entire range so you can see that using range is really intuitive really easy to use a single cell just type in the cell a range type in the range it's very similar to how you would do a formula inside of a cell so it's easy to remember now let's comment that out go to the next example here I'm going to show you how to change worksheets so let's say navigate to another worksheet this very simple remember you're going to another worksheet a sheet so we can use the word sheets open parenthesis quotation mark and now the name of the other sheet so the name of the sheet within quotation marks then a period then simply type activate now let's test it out alt F 11 lf8 run the macro and you can see we're now on sheet 2 so you want to put the name of the worksheet in there the name that's visible on the tab here there are other ways to access the worksheets where you use their index number but using the name visible on the tab is more intuitive and easier especially when you're starting out so just sheets then the name of the sheet within quotation marks then activate now let's comment that out move on to the next example now oftentimes you want to do something on a cell that a user has selected so you want to select the currently active cell let's do something that's common here let's offset the active cell so first thing we need to tell the macro that we want to work with a cell that is currently selected how do we do that active cell it is as simple as that now we put a period what do we want to do with the active cell we can do so many things in this case let's simply do an offset so let's say user select cell a1 and we want it to automatically do something with the same row but a different column or with a cell that's two down and two to the right so basically we are offsetting so we type offset open parenthesis the first argument the row so how many rows away do we want to offset it let's make it go down one row how many columns to the right let's make it go two columns to the right and so we can see what's happening let's use select so now here what we are doing is we are telling the macro do something with the active cell the cell that has been selected by the user what do we want to do let's offset it let's offset it by one row and two columns now once we've offset that what do we want to do with that new cell let's select it let's go ahead and try it out off f11 and select a random cell now let's hit alt f8 run the macro watch it change from C 8 to a 9 perfect so an offset down 1 over 2 try it again as that and if you want you can offset it negative so let's make it go back one row and over two columns to the left now it's back to cell a1 if you run it like this when it's already in cell a1 and try and take it off of the worksheet you're going to get an error hit debug and it takes you to the line of the code that caused the error when you're done looking at that thinking hmm what went wrong Oh went off the worksheet area okay you can hit the stop button up here to reset the code and continue working on it what I'm gonna do real quick is copy this so you can have both examples going negative and going positive this is one of the most important things that you're going to have to do in Excel is to use the offset just like this especially on active cells it's going to allow user to select a cell in a row and then the macro can do something with each individual cell in that entire row without knowing its actual address it's just going to offset it from the cell that was selected by the user or the cell that was selected by the macro through another search process there's lots of reasons you want to use offset so definitely try and remember that now let's comment this out and move on to the next and last set of examples here I'm going to show you how to put data into cells this is building off the last example so let's start with the very first one active cell that's going to do something with the cell that we select so what do we want to do with the active cell let's change the value of that cell that's how you change the contents of a cell that's also how you get the contents of the cell as you type value then equal sign let's just put a simple hi in there now all f/11 let's select our cell say a one-off f8 run the macro and it puts the text hi in there perfect any cell you select the same thing will happen eleven and let's do some more examples next let's put a value in a very specific cell well you know how to select a cell so all we're going to do is change the select to the value so up here remember this is how you select a cell and the first part is going to say the same it's this part that tells the macro that we want to do something with this cell in the first example we just told the macro to select it so let's go with cell b2 what do we want to do we want to change the value what do we want to change it to let's change it to hey there again we use range b2 value hey there alt F 11 f8 run and now we've got text in cell b2 now let's combine the examples to put text in a Cell on another worksheet to do that you first tell the macro what worksheet you want to use we want to use sheet 2 then you put a period then you tell it what cell in that worksheet so we're basically just stacking the first few examples well let's do it on cell c5 so first what worksheet second what range there's no space in here even though it looks like there's a space right there everything is together now at the end we still have to tell what to do let's change the value and let's put more text now when you do it like this you do not have to select the other worksheet so you'll notice when we go back to the workbook we're still on sheet 1 here hit alt f8 run and now on sheet 2 c5 more text you do not have to select the worksheet or select the cell in order to work with it we simply used the examples of selecting cells and ranges earlier and activating the worksheets so you could see how it works so you can see how you work with the worksheets like this and I work with arranges like that but by no means do you have to select it and you really shouldn't be selecting it and activating worksheets just to put data into them the only reason you want to select something or activate something is to make it more intuitive for the user let's comment this out now and use an example with offsets so let's offset the active cell let's offset it by two and two so down to over to change the value to offsets baby hit alt f11 of f8 now it should go over to and down to effect so now I've got sheet one with these examples and a sheet two so here are all of our examples now we started out with a very simple way to select the cells then how to select the range then how to select or go to another worksheet then how to offset a current selection and then how to combine all of that in order to put data into an active cell the cell that's been selected a specific cell a specific cell on a worksheet or a cell so many cells away from the one has been selected that's all I'm going to cover in this tutorial but it should help to give you a really good jump start on making macros in Excel I hope you liked the tutorial if it was helpful don't forget to give it a thumbs up and make sure to subscribe and accept notifications so you can see all the new tutorials

Channel: TeachExcel

Views: 544,018

Rating: 4.9471436 out of 5

Keywords: macros, vba, excel macros, macro tutorial, macro class, free excel tutorial, excel macro training, introductory class, microsoft excel, microsoft office, teachexcel

Id: NMzSIRQ8qMA

Length: 15min 46sec (946 seconds)

Published: Thu Apr 19 2018