Excel VBA Beginner Tutorial

(bright music) - [Instructor] Well, hello there and welcome to VBA coding for Excel. My name is Chelsea Dohemann and we are going to be learning a whole heck of a lot about Visual Basic for Applications. Now, of course, Visual Basic for Applications, which is what VBA stands for, does have the word applications in it. That sort of implies more than one application. So it does actually work in the other programs in the Microsoft Office suite. However, in this particular class, we are really going to be focusing on Excel, which is, in my opinion, one of the places in which it has the greatest utility for the largest number of people. In this beginner class, we're going to start off really nice and simple. So if you've never heard of macros, or maybe you've seen one that somebody else made, you are in the right place. If you've already created your own macros, don't worry, stay tuned and pretty soon we'll be in the middle of amazing stuff for you. In the beginning videos, we're going to start by discussing the basics of VBA and its language, displaying the Developer tab of the Excel ribbon, recording some basic macros, learning some VBA terminology, figuring out how to use the VBA interface, which I do have up over here, we will talk about how to bring that up together, writing macros, debugging macros and adding them as buttons in the ribbon or in the interface of Excel. So I could add it as an extra button over here, or I could add it directly into the interface as well. For this little bit, you get to just sit back and listen and watch. Don't worry, I won't do anything cool without you. So what is VBA? As we discussed earlier, VBA stands for Visual Basic for Applications and on the right-hand side of my screen, you will see a window over here that looks pretty old-fashioned. And that's because VBA is old-fashioned. This is my VBA window over here on the right-hand side. And you'll see that there is something that kind of looks like the old-fashioned ribbon that we used to have, which really looks more like my quick access toolbar, right? And then up here, I do have these old-fashioned dropdown menus. So for those of us who thought we were done with using those, we are going to see them in VBA. And we'll talk about what each one of these does. I'm also going to see in VBA on the left-hand side, a project panel under here's the properties panel. We'll talk a whole lot later about what each one of these does. And then on the right-hand side, I will see a coding window over here that would actually have code in it, if I actually had code here. Now, I don't have any code here specifically, but in my project panel on the left-hand side, and we'll go over this in detail, I'm going to find one of my modules that does have some code and here my code is. This is what the actual code in VBA looks like. So let's talk about what the point of any of this is. VBA is a tool for programming, editing and running application code . By application code, I mean, whatever the host application is, in this case Excel. So the whole point of all this language over here is to actually run it using Excel. So run it in Excel. VBA is not a standalone program, right? It can usually only run code within a host application. So it's going to run all this code inside of Excel itself. It is a combination of Microsoft's event-driven program language, which is a fancy name for just the VB, right, Visual Basic, which is what we tend to call the language that we use here. It's a combination of the language with the integrated development environment, which is a really fancy name for the whole VBA interface. One of the really neat things that VBA does allow us to do that we wouldn't otherwise be able to do is it allows us to custom design our own functions. So let's say in Excel, I need to regularly add some things up. So I got some numbers here and I want to add them up. There's a neat function that does that, It's called SUM. It allows me to just grab some stuff here and add it all up. Just like that. Now this function has already been programmed by Excel, right? So the Excel programmers decided, oh, there's this thing that would be really handy for most people, we'll call it SUM, it adds some stuff up. Now let's say I need to be able to do something for which there isn't already a function in Excel. In that case, I'm going to need to make my own; I can't actually do that in Excel, but I can do that with VBA. So I can come over here into my VBA interface and design a function here. And then once I've designed it in VBA, I can actually use it in Excel. So if you need to calculate the area of a triangle, there's not a function that does that, but you can make one. If you need to calculate percent of change over time, there is not a standard function in Excel that does that, but you can make one, you can make one in VBA and you can use it from then on out. And one of the last things we'll talk about, that's really important to know about Visual Basic for Applications is that it does expand on the abilities of earlier application-specific macro programming languages. So it expands on earlier languages like Word's Word Basic. Because it expands on these older languages, we're going to see some language that's really old and some language that is new. With every new incarnation of Microsoft Office, there are updates to VBA as well. So you'll notice throughout our learning about VBA, that you will see some coding statements that are a little bit old-fashioned, right next to some coding statements that are brand new. And we'll talk about that a whole lot more once we actually get there. Object Oriented Programming is an excellent description for the coding language that we are going to be using as we get to know VBA a little bit. Object Oriented Programming is a great descriptor for the kinds of languages that tend to be used with a lot of applications these days. So probably most of the applications that you're using are based on some kind of programming like that. Certainly all of the Microsoft, all the standard Microsoft Office applications are based on an object oriented programming language. Let's talk about what that is supposed to mean. What that means is that behind the scenes, there is all this programming language that happens and each one of these statements that happens starts by defining some kind of an object. In all of your Microsoft applications, you've probably noticed that in order to effect some kind of a change, you always have to select on the thing that you want to change first. For instance, maybe I want to delete some text in cell B4. Well, I know because I've been using these programs for a long time that I need to actually click on B4 or select B4 first, before I can actually delete the stuff there. Because I have selected on it, I can now delete the things that are in there. I could also change the color of a cell, but again, I must select it first. So I'm selecting F5 here, and I'm going to turn it green. In order to make that kind of a change, I do have to select the object that I want to change first. Let's say now I want to make a change to A1. Is there any way for me to make a change to A1 without selecting A1 first? The answer is no. And the reason is that the beginning of every programming statement must start by defining the object I want to make a change to. So in the last circumstance, I had to select on the cell and then delete the content, right? In this example here, I had to select on the cell first and then turn it green. I cannot make any changes without selecting an object first. And the reason is that in these statements, this is a really simple example over here, I want you guys to ignore most of what you see here, so ignore all this stuff here. Just take a look at the very beginning. This statement here at the very top, it's defining an object first, it's saying, oh, that thing that you already clicked on, do this to it, right? Okay. So we're going to see this terminology used over and over and over again. We're always going to need to state an object first. You can kind of think of it like a noun. My dog is going for a walk, right? In that case, my dog is the noun, right? My dog is the object of that sentence, that statement, right? So a noun and an object are sort of synonymous. In this case, we are going to be talking a whole lot more about the other grammatical options that we have in VBA programming language, but we're starting with the one called an object. So in this case, I want to make changes to cell A1. So I have to click on cell A1. And if I were actually converting this into code, you would actually see the beginning of the statement say, cell A1, that's the first thing it would say. Okay, so that's what object oriented programming actually means to us. If we want to get a little bit more techie with it, we could get into the fact that it's based on, obviously, as we've already said, the concept of objects, which are data structures that contain data about an object as well in the form of properties and also code in the form of methods. And again, we will be talking about this all later. But that is the basics of Object Oriented Programming languages. In the next section, we're actually going to be jumping up and doing some stuff. So for everybody, who's just been hanging out with me so far. Maybe you've just been sitting back and just watching these first couple of things that we talked about, now is the time for us to actually pop up and start working on some stuff together. So what I want you to do is pause the video and open up Excel for yourself. So I want you guys to open up your Excel program, pause the video and do that. And once you have it open, continue to the video, and then we will start making those basic modifications to Excel so that we can start making some macros. All right, so hopefully you're all caught up with me. Hopefully you have already opened Excel and opening up a completely blank workbook is fine. I am in my template called book one, right? That's the one that we get into every time we open up Excel and just say, we just want a blank workbook. And I do have my window fully expanded here. I am in Excel 2016. However, I have mentioned this in the first video, but it definitely bears repeating. If you are using an earlier version of the program like Excel 2007, Excel 2010 or Excel 2013, everything that we're going to be doing in this class is also doable in each one of those versions of the program. If you are using an older version of that, no worries. There might be little things here and there that cause a little bit of a hiccup. And by simply Googling those little things, we tend to be able to find fixes very quickly. They're usually the very first thing that comes up in our Google search. Now in Excel 2016, it looks a whole lot like Excel 2013. I do have my normal tabs of the ribbon on the upper left-hand side of my screen. I have my File tab, my Home tab, Insert, Page Layout, Formulas, Data, Review, View. And then I have this area that says, tell me what you want to do. This is one of the new features of 2016. It is pretty cool in here. I could look for anything, like I want to make something bold and I could just access the bold tool right there. And it actually turns it on directly from up there. So if I wanted to insert a pivot table and I couldn't figure out where those are, right, you can access it there. It's pretty cool. Anyway, so the one tab of the ribbon that I do need that I don't see is called the Developer tab and we're going to need to turn it on in order to be able to play with all of the macro stuff that we're going to be taking a look at. So that's the first thing that we're going to do. Anytime we want to customize our ribbon, that is a customization of Excel. Any time we want to customize Excel, we can access those options in the file tab. And if we go to the file tab all the way down at the bottom, we'll see the menu item that says Options. We can click on that. That will open our Excel Options window. A bunch of amazing customization features in here. I'm going to have us skip right down to the option that says, Customize the Ribbon. And on the right-hand side of the Customize the Ribbon window, you will see a list of the tabs of the ribbon that are enabled currently. And if you have any tabs of the ribbon that are disabled, the boxes next to them will be unchecked. In my case, I do have a tab of the ribbon called Developer. However, the box is unchecked. Now that's actually the tab I want. So in order to be able to actually get this tab, I'm going to click on the box and click, Okay. And that's going to enable that tab of the ribbon for me. I can click on it and take a look on it. And that is where we're going to get started here. So that's the first thing that we need to do, do that with me, please. Go to your File tab, go all the way down to Options. We're looking for the ability to Customize the Ribbon. And on the right-hand side, we're going to find the tab called the Developer tab. Check the box and click Okay. Why don't you go ahead and pause the video and make sure that you have done just that and that you can actually see all of the options on the Developer tab. And once you've done that, come rejoin me and then we will actually start recording some macros. Lucky for us these days, we don't actually have to know any coding whatsoever to create our very own macro because we have this amazing tool called the Macro Recorder. On the Developer tab, on the left-hand side, you're going to see a command group called Code. In that command group, you're going to see a lot of buttons that we're going to be clicking on today. The very first one that we're going to start playing with is Record Macro. VBA, what it does when we click on Record Macro is it watches everything that we do in Excel, and then it converts it into code. And once we have that code stored safely in VBA, we can choose to re-execute that whenever we want. So let's say for instance, a couple of times a day, I'm getting these reports from a neighboring department and it's my job to put on a report title. And then I also have to format that report title. Maybe I have to turn it bold and italics and underline, and I have to put an outline on it and I have to turn it green and I have to make the text white and I have to make it bigger. Maybe I have to do that every single time I need to put this report title in. If I need to do that every single time, that's six or seven steps that I might forget one of, or maybe I want to be able to delegate this task to somebody else and I'm concerned that they will forget one of the steps or possibly I'm asking them to do a bunch of these things in this process and I'm concerned that they don't actually know how to do one of them, right? Maybe they don't know where to find the border button. In that case, I would really prefer if I could save the process of going through this as a macro and then have VBA redo all these steps for me whenever I would like. All right, I'm going to clear out the contents here, so we can all do this together. Go into the Developer tab. And I'm going to ask VBA to please watch everything that I do in the next little section and store it as a macro so that I can rerun it every single time I need to generate a report title. So the Macro Recorder is going to do that for me. I'm going to click on Macro Recorder. And the first thing it's going to make me do is name this macro that I'm creating. It's also going to ask me if I want to assign a shortcut key here, and then it's going to ask me where I want to store this macro. So here are the details here. The macro name, there is a naming convention for this. We must have no spaces. It also has to start with a letter. So I'm going to call this report title, no space. If you would like a space in there, you can just put an underscore in there. That'll help you synthesize a space, but you don't have to. You can assign a shortcut key if you would like. Maybe I'll call this Control. Control comes free of charge. You don't actually get a choice whether or not to use the Control. You always have to have it, which is actually a good thing because if I assigned it to something really commonly used, the problem is that it replaces or it overwrites, or it ignores other shortcuts that I have. So for instance, if I use Control + S all the time to save my documents, if I assigned Control + S to a macro, what it would do is ignore the save function and it would just run my macro. So I do want to be really mindful of this. That's why a lot of us macro recorders, frequent macro recorders tend to assign shortcut keys that also have a shift in there. So I'm going to put my Shift + S in or my, in this case, I'm going to put Shift + R and you'll see that I do have this Shift added to the shortcut key. Now, there are a couple of different places where you can store your macro. You can store it in this workbook, or you can store it in your personal macro workbook. The differences are, if you store it in this workbook, it will be able to travel with this document. So you can send it off to a coworker. They will also be able to use this macro. So that's kind of nice. However, it does mean that you can't really use it in any of your other Excel documents, unless you also have this one open. So if I want to be able to use this macro across the board in all kinds of different documents as well, instead, gonna store it in my personal macro workbook. Now, for our purposes, I'm going to have us store it in this workbook. That's going to make it easier for us. This is a really simple example, probably gonna want to delete it after we do it. So let's just do this workbook and click Okay. And now VBA is watching everything that we do, and it's going to turn it into code. So the very first thing that I want to do is tell VBA, hey, I always need to put my report title in cell C2. So the way that I'm going to do that, even though I'm already clicked on C2, even though I already have it selected, I'm actually going to take my mouse and click on it again. Now, because I just started recording, that's the very first action that it is going to record. So I'm sure that my report title is always going to be in C2 and now I'm going to type in the name of my title. And I'm executing that with Control + Enter, which is my favorite execution method. The thing I like about it is that it doesn't actually navigate me away. So if I used Enter, it would execute, but it would navigate me downward one cell. Whereas if I use Tab, it would execute, but it would navigate me to the right one cell. So I'm going to be using Control + Enter constantly in these videos. And I use it constantly in Excel. It makes it a whole lot easier for me to execute and continue to do things I want to be able to do without really having to re-click on stuff. So I'm going to be using Control + Enter a whole lot. All right, Control + Enter to execute. Go into the Home tab and I'm going to make all these formatting changes that I would like to make. And you guys can go ahead and do this with me. So we are choosing to make some formatting changes. You guys don't have to do the exact same ones that I'm doing, but these are just example formatting changes that I'm making here. And once I'm done, I am going to make sure to go back to the Developer tab and click on Stop Recording. This is one of those things that gets forgotten, even by the most experienced macro gurus out there. So you really want to make sure to click on Stop Recording. Now that I've clicked on Stop Recording, I do have my Record Macro button back. That's what it looks like once I've actually clicked on Stop Recording. And now I have that macro, so that macro is saved. So let's say I need to be able to rerun this in another place. Well, maybe I have another sheet here. I'm going to go down and click on this Plus button. In Excel 2010, obviously that looks like a different icon there, right? But whatever your icon is, I'm clicking on that icon and create a brand new sheet here. And then I'm going to try and run my macro here. So I'm going to my Macros button. I'm going to find that macro that I just created. Again, that Macros button in the ribbon, that's where I'm going to find the list of all the macros that I have. And here's my list right here. And I'm going to find that macro that I created, click on it and click on Run. And that's going to run that macro right there, all six steps, all six or seven or eight or 600 steps, all at once. And that's the whole point of the Macro Recorder. Now, when we just ran this macro that we just created, the reason why it looked so nice and perfect is because it was set up so that it specifically recorded and then ran this macro in cell C2. And the reason is that by default, all these macros are going to be recording using absolute cell referencing. Just for those of us who are not familiar with that term, if you've ever seen a cell address that has these dollar signs in it, this is just an example, but if you've ever seen a cell address that you've used in a formula or a function or something, and it actually had the dollar signs in it, or a bunch of other tools, that'll put it in there as well. So the purpose of those dollar signs is to lock or anchor the cell reference in place so that when you use the autofill handle, maybe it doesn't migrate anywhere, that kind of thing. Now, of course, we do have the standard cell reference that doesn't have the dollar signs. It's just A1. This is what's called a relative cell reference. So the other one was absolute, that's with the dollar signs, this one is called relative. And the whole purpose of these regular cell references is that they do migrate. So if I was trying to make a list of calculations here, I'm just going to set up an example, nobody has to do this with me, but if I was just setting up a table here and I was asking Excel to please multiply this times this, and then do this over and over and over again for the rest of these cells. But I want you to, instead of using the original cell references, this function is using the cell references in row two. Whereas the next formula down is using the cell references from row three, right? So those cell references are migrating and that's what a regular cell reference does. If I put the dollar signs in there, it wouldn't change from F2 to F3, it would stay on, let's say F2, right. Okay, so if I do want to use relative referencing, I'm going to need to turn on this button here that says, Use Relative References. Now, why would I want to do that? In this case, my macro did exactly what I wanted it to do, and I didn't click on that button. Well, let's say for instance, I want to be able to run this macro in a different location. Maybe I want to be able to run it in cell G2. And then in a few minutes later, I want to be able to run it in cell D8, right? The problem is that the macro as I've recorded it doesn't run anywhere else except for C2. And indeed, if I click on macro and again, you don't have to do this with me, I'm just giving us an example, and I choose my macro and I click on Run, kind of looks like nothing happened. And the reason is because it can't run anywhere else. So it basically just replaced it with what was already there. If I select on cell E3 and try to run my macro over here, again it doesn't work because it only runs in cell C2. So if I want to be able to have this macro run in all these different spots, then I'm going to need to turn on Use Relative References. So in order for us to create a macro, that actually does this, let's go ahead and click on another cell, whichever cell we want to eventually start in before we click on Use Relative Reference. So in the last example, you probably noticed the very first thing that we did was click on the cell where we actually want the macro to run. In this case, we're going to click on the cell where we want the macro to run, in the first example, anyway, before we actually record our macro, okay. So I've selected on cell C6. I'm turning on my Use Relative Reference button, and I'm going to click on Record Macro. And we're just going to make a really silly example here, we're going to say these are ExpenseTitles. Remember no spaces, has to start with a letter. I'm going to skip the shortcut key. I'm going to store it in the workbook and I'm going to click Okay. And we'll say, maybe this is payroll. And maybe the next category is Office Supplies. Telephone. Rent, whatever it might be. Just enter a couple of different kinds of expenses there or whatever you would like. And when you're done, of course, make sure to execute. I am using Control + Enter to execute that. And then of course at the end, I'm going to click on Stop Recording. Okay, now I'm going to go ahead and use this in different circumstances. So maybe I have a table over here and I want to be able to populate all of my expense categories under here. I can click on my Macros button, find my ExpenseTitles and click on Run, and it's going to run it over here. And then I could also do the same thing over here. The expenses I care about. And I'll run my macro over here. So in this circumstance, I have created a macro that can run in many different locations. Now you might be asking yourself at this point, why wouldn't I always turn on relative references? The reason is that sometimes we definitely have the need to record our macro and run it in exactly the same spot. We're going to be able to use this Record Macro feature in a bunch of different situations. So we're not actually going to need to code it by hand, which is really nice. But depending on the kind of thing that we're recording, it would be handy to use absolute references. Let me give this a few examples. If you're going to create a macro, that's supposed to sort stuff, right? So maybe I have a list and I always need to sort my expenses, for instance. If my expenses are always going to be in that same column, in that data set, I'm going to want to use the sort using the absolute references, which means I'm going to want to turn off relative references before I actually record that macro. And the reason is that sort macros work best if you actually use absolute referencing. Filtering macros are going to work exactly the same way. So again, sorting and filtering are great things to use the Macro Recorder for, so long as you want to allow people to sort or filter by a specific thing in particular. If that's the case, those kinds of macros actually work a whole lot better if we use absolute referencing and we'll see this more in action as we continue through the course. Make sure that you've actually recorded a macro using relative referencing. So again, the process that we went through to do that is we did turn on Use Relative References. Then we recorded our macro, of course, we named it, chose to store it in the macro workbook, we clicked, Okay. We then completed all of the things that we wanted to do. And when we were done, we clicked on Stop Recording and then we actually tested it out and ran it in a few different spots. Why don't you pause the video right now and give that a shot if you haven't done so already, or maybe give it another shot if you've already done it just to kind of get used to the process. When you're done, go ahead and unpause the video and we will continue with some videos specifically about creating some useful macros. Welcome back. All right. So what we're going to be doing now is creating some very simple, yet very useful macros using the Macro Recorder. Now, hopefully you've already downloaded this file called Simple Macros. If not, now would be a good time to pause the video and go ahead and download that particular file. It should be right there in your Alert at Anytime Interface. And once you have it, go ahead and unpause the video and we'll continue with the exercises. Okay, I already have it so I'm going to go ahead and click on it. And here it is, it opened up for me. Now, let's say I have the regular exercise of formatting certain numerical values. And I have a few challenges here. I do need these phone numbers here. However, all those phone numbers are actually for the UK. Now they're showing up like American phone numbers here. And so what I want to do is convert this into another kind of formatting, but you'll notice if I go into my Number Formatting dialog box here, I can go down to Special and choose Phone Number. And I could change this to something like UK, but it just converts it to general formatting, right? So what I'm going to do is actually create my own custom number formatting, and I'm not going to do it out here like normal, because if I did it out here, I would have to do it over and over and over again, every time I wanted to do this. What I'm going to do is I'm going to turn it into a macro. That way I can use it over and over and over again, whenever I would like to using the keyboard shortcut, much faster. So here we go. I'm going to start by going to the Developer tab and I'm going to record my macro, but before I do so I'm going to ask myself two things. First of all, do I need to be using relative references or absolute references, right? So do I need to turn on the Use Relative References button? So the real question, let me ask myself is, do I want to be able to use this particular macro in this spot, in this spot, in this spot, in this spot, et cetera? The answer to that is yes. So therefore I want to be able to use relative references so I have the flexibility to use this macro, wherever I would like. Then I am going to ask myself whether I have already selected on the cell that I do want to format before I click the Record Macro button, because I'm using relative references, so I need to make sure the first thing the Macro Recorder records is, oh, the cell that I'm already in, right? If I clicked on Record Macro and then the first thing I did was like click over here. The first thing it's going to record is, okay, move two cells to the right, move two cells down, right? Don't want it to do that, it's going to do a bunch of wrong stuff in the wrong places, exactly where I don't want it to. So I'm selecting the cell before I start. And then I'll click on Record Macro, and I'm going to name this one UKPhone and I'll assign a shortcut key for this one. And maybe the key is Shift + E. It's safer to do Control + Shift shortcuts instead of Control shortcuts. There are only three left that are unused and actually with the newer version of Excel, there's only two control something shortcuts that are unused. So it's much safer to do Control + Shifts, there are a lot more of those available. So I'll use Control + Shift + E for England and click Okay. And here I go, I'm going to go to the Home tab of the ribbon, find my number, my Number command group, what I like to call the number formatting command group and I'm going to use this little button in the lower right hand corner of that command group to bring me into my number formatting area. So this is my Format Cells dialogue box. I'm on the tab called Number, and I'm going to go down to my custom number formats. And here we go, I'm going to change the formatting of this particular cell. And here's where we want it to show up. I always want the UK area code to show up at the beginning. So the country code for the UK is +44 so that's what I'm going to want first. Then I'm going to tell Excel, okay, now you can start displaying my numbers here and it should be like this: number, number, number, dash, number, number, number, dash, four more numbers. And there are a few different ways to see UK phone numbers displayed. It's been a while since I've been there, but I think this is the way it was displayed last time I was there. So that's the way I'm going to do it. You guys could do it any way you'd like, and I'll go ahead and click on Okay. And then that number is formatted just like that. Wonderful. Okay. So I'm going to go to my Developer tab and Stop Recording. And now I should be able to select all the cells in this column and format them just like that. So I'm selecting all the cells in this column using, I've already clicked on the top numerical cell there, and I'm using Control + Shift + Down to select everything in that particular column. And I'm using my keyboard shortcut, which is Control + Shift + E for England. And there we go. All my numbers are formatted like that, going to go to the list on the right-hand side to practice it. Here we go, selecting on the top phone number cell, and I'm using Control + Shift + Down to select all those guys. And then using Control + Shift + E to actually change to that formatting. And it's just as easy as that. Alright. Maybe I work in HR and I have the duty of keeping track of everybody's birthdays. However, I really want to be sensitive about people's, anybody who might feel a little bit age sensitive. And so I don't want to display their birth year on their birthdays. I do want everybody to be able to know what their birth day is, but not their birth year. So I would change my number formatting here. Now I'm going to do this again as a macro so that I can do it whenever I want to with the keyboard shortcut. Okay, so here we go. Here's another example of using relative references, want to be able to do this in a bunch of different places, so I am using relative references. I have clicked on a cell that has the values in it that I want to format before I actually click on Record Macro. So I'm all good. So I'm going to click on Record Macro and here we go. Here's the second macro we're going to make. This is all going to be about birthdays. So I'm going to say BirthdayNoYear. And my shortcut key for this one is going to be B, B for birthday and I'll click Okay. And here we go. I'm going to the Home tab of the ribbon and on the right-hand side, you'll see it's currently listed as a date format, but I want to make this a little bit more interesting. So I'm going to the Number, More Options button, which brings me into the Format Cells dialogue box. And the left tab is number. I'm going down to Custom and I'm going to assign this as Month and maybe I want it to show up as the full month, so I'm going to put four M's in there. That's going to show up as July and you'll see up in this sample area that it is telling me how this thing is going to display so far. So I want the month and I want the day, two digits is fine. Okay. And I'll click Okay. And that's it. I'm all done. Go into the Developer tab, Stop Recording. And I can grab all the cells in this particular column and use Control + Shift + B for birthday and there it is converted. Maybe I'll do that on the right-hand side as well. Control + Shift + Up this time and Control + Shift + B for birthday. All right, so I've been able to do that fairly easily. Now I did that with the Number Formatting dialog box, right, the Format Cells dialogue box using the Number tab. So sometimes what I need to do can't actually be accomplished by those Number Formatting options, even the custom ones. And so I'm going to need to rig it up a little bit more interestingly, for instance here, I have a list of a bunch of people's social security numbers. And my issue is that I don't want anybody to be able to see anything, but the last four digits of their social security number. And so I've decided I'm going to convert this entire document so that the only data that's stored here is the last four digits of their social security number. Now there's not an easy way to change the formatting here, so I'm going to have to use some functions to do it. Now, there are a couple of functions I really like. We do have the RIGHT function. This is one of my favorite text functions, pulls a certain number of characters from one side of a cell, in this case, the right side. There's also one called LEFT, and there's also one called MID. This one's really easy. You just choose the text that you want to pull characters from, or the numbers of course, that you want to pull numbers from. You select that cell, and then you tell it how many characters you want to pull, right? So it's as easy as that. Now let's say I want to make it a little bit more interesting. I want to get this thing to also display some characters on the left-hand side of those four digits that sort of imply that they're hidden there. So I'm going to put in a parentheses and I'm going to use the asterisks, and this is how I'm going to want it to show up. So the asterisks on the left-hand side with the dashes, and then I want to combine that. So I'm going to use my ampersand. I want to combine that with the value that's returned by the RIGHT function. So I'm going to go ahead and execute that. And then I'm going to ask this function to please populate this for the rest of the column. Now, eventually what I'm going to do is copy and paste this over here to replace the social security values. But unfortunately, what I got going on here is these cells are requiring information from these cells, right? So if I did that right now, if I copied and pasted it right now, it would still have the function. The function is still looking for stuff from E8, right, so it would give me a reference warning. So what I'm going to do instead of that is copy and paste this function. So I'm going to copy it. And then I'm going to just paste the values that are returned by the function. So I can use my regular Control + C to copy, but then I'm going to go over to my handy Paste button and click on the triangle underneath it. And I'm going to choose Paste Values, one of my favorite buttons. All right, now I still have my flashing marquee around it, so I don't have to copy it again. However, if I wanted to, I could, but I'm going to go over here in a social security number column. And this is where I'm actually going to paste those values, and here I go pasting them. I just pasted it with the Enter key. You could do it with Control + V as well, but Enter's designed to paste once and remove it from the clipboard, which is why I like it. Okay, great. So we got those values pasted over here. It looks pretty good. So I'm going to go over here and delete all this stuff over here, using Control + Shift + Down to select it all, then hitting my Delete key to remove it. And finally, I am going to select that very first cell there, the cell that I started with, and I'm going to turn off my macro. Okay, so that's the process that I'm going to go through. Let's do it. Now, normally when you're doing this, this is like a five step process. So this is the time when you would want to start to write down every single step and then you'd follow every single step. As soon as it gets beyond a few steps, that's what you want to do. And indeed, I've written down all the steps for myself, so I'm ready to go. So I'm going to click on Record Macro here and here we go. We're going to call this the SocialLastFour and I'll use Control + Shift + S for that particular shortcut. And here we go. Great, so we were able to record those couple of simple macros that really made our job a whole lot easier, getting these birthdays and these phone numbers to show up exactly like we want. Now I have a larger challenge. And so this is going to require a few steps for me. I'm going to need to put a function in there. I'm also going to need to put a formula in there, and then I'm going to need to copy stuff and paste it as values. That's going to be a little bit of a process. And so what I'm starting to do at this point, at this point in my macro recording career is start to write down all the steps that are required by my macro. So all of the steps that I actually want recorded, I'm going to break them down on a piece of paper and that's going to help me when I actually record the macro, not to make any mistakes, because obviously any mistakes that I made while I'm recording do get recorded, right? So I've written down all my steps, and now we're going to go ahead and do this. So here's my dilemma. I have a social security numbers column, and I only want the last four digits to display. What I'm going to be doing is using a combination of a formula and a function to do that. So I'm going to be saying, hey, Excel, and you don't have to do this with me right now, we're all gonna do it together in just a second. But what I'm going to do is tell Excel, hey, I want you to use this cool function called RIGHT. And you're going to use that function and pull the right four digits from that number. Okay, so it's going to pull the right four digits. Now I'm going to make it a little bit more interesting because maybe I want this number to show up, with the kind of giving the implication that there are other digits that somebody else can't see. If I wanted to do that, I just put in some asterisks, right? So I put in, maybe I'd use an asterisks couple times, right? So I'd use it for the first three digits, for the second two digits. And I put dashes in between those numbers and then a dash after those asterisks. So that right after that, it'll populate the four digits from the right-hand side of the social security number. Now, any time I actually want characters to show up in my cell at the end, I do have to surround them with quotation marks. So I'm going to go ahead and do that. And then in between my formula that I got going on there and the function itself, I have to tell Excel, hey, I want you to combine this stuff. And the way that I do that is with the ampersand, that's the shorthand for it. Okay, and then when I execute that, that's how my number is going to show up. So that's great. And then when I'm all done here, what I'm going to do is I'm going to copy that and I'm going to paste it over here, over the social security numbers. Now I can't actually copy and paste the formula and function here because it's using some cell references. Which cell references? It's actually using the same cell where I want to paste it to, that's going to be a problem. And so what I'm going to end up doing is copying this and then pasting only the values over here in the social security number column. Makes sense? All right, here we go. All right, so I'm going to start by selecting the cell that I want to begin working on. And I am going to use relative references because I want to be able to process this macro in another spot later on. And I'm going to click on Record Macro and I'll call this particular macro SocialLastFour. And my shortcut key for this one maybe is Control + Shift + S and I'll click on Okay. And here we go. I'm doing it step-by-step, being really careful. Okay, so I'm going to the cell that is two cells right of that. And I'm going to put in my formula here and my function. So I'm going to need to put in my asterisks, dash and another dash at the end, following that with my quotation marks, then I got my handy ampersand, which is followed by the characters that are going to be returned by the RIGHT function. And it's going to pull from my social security number cell, and it's going to pull four characters there and I'm going to execute that. And once I've executed that, I'm going to auto-fill the rest of my column here. Okay, so we're good so far. Now what I got to do is copy and paste these as the values, right? And once I do that, you'll see that the values here that show up in the formula bar is no longer the formula or the function. It's just those strict values that were returned by this particular process. Now I do still have the flashing marquee surrounding the selection, so I can also paste it in another location. So I'm going to have it pasted here as well in the social security number column. And I'm just going to use my Enter key to do that, because what it's going to do is paste that and remove the original selection from the clipboard, which is really nice. So I got my values showing up over there. And now what I want to do is delete it from this column on the right-hand side. So all I'm going to do is use Control + Shift + Down to select all that content. And I'm hitting my Delete key. And that's going to remove it. Now that I'm done, I'm just going to select the cell that I started on here. And I'm done recording that particular macro. So here I go, Developer tab, I'm going to Stop Recording and I can test it out on the table on the right-hand side. So here I have my social security numbers and I'm going to use Control + Shift + S, which is my shortcut key. And just like that, I get all of these converted that quickly. Now, as handy as this is, it did require a few steps. So I did have to write it down. So that was part of the practice here, is making sure to write down all the steps. Why don't you go ahead and do that? Take a moment to pause the video, write down the few steps that we want to do here. After you write it down, make sure that you have recorded all three of these macros. We're going to make sure that we've recorded the simple ones that we started off with. And then the more complex one that we did for the third example, which had a few different steps there. Okay, go ahead and do that and come back and then we'll continue with even more interesting and useful macros. Welcome back. So in the last few examples, we did get to do a couple of actions. They were simple, they were formulaic, or they are formatting options. This time, we're going to use some of the bigger tools in Excel and see that a lot of these bigger tools actually work just as well with macros. Okay. So what we're going to be doing is creating a few sort macros and a few filter macros. Now, when we're actually recording this on the Developer tab, you're going to want to leave this Use Relative Reference button unchosen. And the reason is that our sort and filter macros are going to work best using absolute references. So that's what I'm going to do. And then I'm going to click on Record Macro, and here I go. I'm going to make my first sort macro. This is going to sort by the department and then by the last name and I'll click Okay. And then I'm going to go to the Data tab where I find my Sort Tool and I'm going to add my two levels. The first one is by department and the second one is by last name and I'll click Okay. And there it is. As easy as that I'm done with my macro, go to my Developer tab and Stop Recording. And I'm going to make one more, then I go to the Developer tab, click Start Recording Macro. And this time I am going to make a sort macro that has to do with sorting by the status and then the first name. Go into my Data tab, choosing my sort button. This time, I'm going to sort by status first and then by first name and I'll click Okay. Now I'm done, go to the Developer tab, click Stop Recording. Here's the really cool part. If we have coworkers who are not able to sort and filter, maybe they don't know how to use those tools. Anything that's a macro, I can actually turn into a button on the interface and they can just click on that button and it will do what they want to do. So if I go to the Insert tab, so on the Developer tab of the ribbon, and I'm going to the Insert button and in the Insert Menu, you'll see a bunch of fantastic form controls here. We will go over a few more of these throughout the course of the class, but I'm going to start with talking about the Button macro, it's on the top left. I'm going to choose that. And when I bring my cursor back into the interface, it looks like I'm about to draw a square. And that's kind of what it looks like when I click and drag to actually create this thing. And this is going to be my sort, this is going to be my sort by department, and then last and I'll click Okay. And I can right click on this to edit the text and I can actually change the text to say Sort Dept and Last, and then maybe I'll put it in another one for my second sort and my second source is the sort by status and then first, and I'll click Okay again. Editing my text here, Sort Status, and then First Name. And now my end users, folks who might not already know how to use sorting, they can just click on these and have it sort exactly like they wanted to. The same thing works for filtering. So I'm going to make a couple of filtering macros. I'm going to click on Record Macro, and this one is going to be a filter for my operations department. And I want to see all the people that work full time in the operations department. Okay. So I'm clicking on Okay. I did start my recording here, going to the Data tab, my filter's already on. So I'm going to go to the department and say, okay, I want to find operations. There it is. I'm choosing just a simple operations and I'll click Okay. And then I also want to find people that are full time. So I'm going to choose, select all to unselect all, and I'm choosing full time, full time. There are a couple of different people. There's a dash in a few of them and I'll click Okay. And these are all the people in my operations department that worked full time and I'll go to the Developer and I'll stop recording. So that's one. Let's say I need to make another one for my coworker. Alright, so I'm going to record another one. And this one is going to filter folks who work between 35 to 40 hours. And I'll click Okay. And over here in the hours worked column, I can now apply a number filter, these number filters work just as well. And I'll say between 35 and 40 click Okay. And then I'm done. So I'm going to stop recording and then maybe I'll make one more for them, that's just going to clear the filter. ClearMyFilter. I'll click Okay. And now I'm going to go back to the Data tab and I'm just going to click on this Clear button. That's it, going back to the Developer, Stop Recording. And now of course, just like before I can create these nice-looking buttons here, and this one is going to filter for operations full time and I'll click Okay. Maybe I'll make that a little bit wider. Okay. I'll put another button here. That's to filter 35 to 40 hours and I'll click Okay. And then I'm just going to put the nice one in there that's going to clear everything, and there I'm done. Now, my coworkers should be able to filter by operations in full time and then clear it and see filtered 35 to 40 hours. And they can see them both at the same time if they filter one of them and then filter the next one as well, okay. So why don't you go ahead and try that. Pause the video, record at least one sort macro and at least one filter macro. Make sure to not use relative references. And once you have those recorded and test them out, then come back and we will talk about some even more interesting macros that we can record. Welcome back. One of the other fantastic tools that works really well with the macro recorder is cell and sheet protection. Now I'm actually going to use a special pathway through which to select cells, to lock and not to lock. Normally you could click on any cell that you wanted your end users to be able to modify after you protected your sheet. And you could go to the Format tab and say, I want to unlock those. That's how we would create cells that an end user actually could modify. However, I'm not actually going to select these cells. Because once I select these cells, that's what actually gets recorded. And I can't use this on any differently built spreadsheets. So instead I'm going to use the Find and Select tool in Excel. That's on the Home tab. All right, let's go ahead and do this. Go into the Developer tab, clicking on Record Macro and I am going to record this macro. I'm going to call it InputCellFormat and then Protect, then I'll click Okay. And now I'm going to go to the Home tab of the ribbon and all the way on the right-hand side, I'm going to find the option that says, Find and Select. I'm going to choose, Go To Special. And in here I'm going to choose Constants, particularly constants that are numerical values. So I'm going to ignore Text, Logicals and Errors. Just the number of values that are constant, and I'm going to click Okay. These tend to be input values, which tend to be the kind of thing that my end user can modify. So I'm going to go ahead and unlock these, going to my Format tab. I'm going to unlock these guys. I'm also going to change the colors ever so slightly so that my end users will know that they're supposed to be able to modify these cells. And then I'm going to set up my sheet with the protection that's going to protect every other cell in the worksheet by clicking on Protect Sheet. I'm not going to apply a password here. I'm just going to click Okay. And I'm done. Go into the Developer tab and Stop Recording. And if we want to test this and see if it works, we can go to a completely different-looking sheet here, which very much looks completely different. And it should still work here because of the pathways that we used. So I'll go to my Macros button up here, I'll find my macro, which has to do with InputCellFormatProtect and I'll click on Run. And there we go. And if it's worked perfectly, I should be able to click on any one of these cells and not be able to enter any information. So there I go, clicking, and indeed I do get that error message, but I should be able to go up to cell B3. This should be a valid input cell, and I should be able to change this to another value. And there we go. I was able to do that. It did modify all of the formulas in my spreadsheet and there we are. If you haven't done so already, take the time now to pause the video and try and create this macro for yourself as well. You'll go to the first sheet here. You will start recording your macro. You'll go to the Home tab, use your Find and Select tool and Go To Special, to find only Constants, only numerical constants. You'll click Okay, format those, unlock them. And then you will protect your sheet. After you've done that, make sure to click Stop Recording, and then go to the next sheet in line, which is called Test and run your macro on this sheet and see if it works. When you're done, come on back and we will move on to even more complicated macros. Welcome back. For the rest of the course, we're going to start actually writing some VBA scripting. In order to make any edits, we're going to be spending quite a bit of time in the VBA interface. So let's go ahead and get used to it now. On the right-hand side of my screen, I do have the Visual Basic for Applications window open. Now most of it is gray, but on the left-hand side I do have what's referred to as the Project Explorer over here. And I do also have the Properties window that I can see over here as well. Now I do expect to see an area over here where I actually have some code, however, that's not present because I don't have any code in here. I don't have any code in here because I don't have any macros in here. On the left-hand side in my project Explorer window, normally, if I actually had some macros in here, you would see a folder that said Modules and that folder called Modules would contain all of the different macros that I have for this particular file. So in order to get a module in here, I'm going to go to the Insert Dropdown menu in the tool bar. I'm going to choose Module. And that inserts this module for me. It also inserts a Modules folder and I instantly have Module1 and I could go ahead and type in whatever code I want to. Now of course the code actually has to make sense, so what I just typed would be not particularly helpful, but I do have my code window up here on the right-hand side. So that's exactly what I wanted. There are a couple other features that I do want us to be able to see before we move on and start doing some scripting. I want us to be able to see the immediate window. Now, I can't see it right now so I'm going to go to my View dropdown menu, and I'm going to choose my immediate window. My immediate window is this area at the bottom of the screen. Now, normally there is no script in there whatsoever, but I do have my immediate window down here. And this immediate window is amazing. It allows us to be able to run little bits of code and also ask questions of our workbook. So let's say for instance, I want to ask a question like, how many worksheets do I have in my workbook? If I'm going to ask a question, I'm always going to start with my question mark, and then I can type something like Worksheets.Count and we'll be able to explore this a little bit later and see why exactly that little bit of code I just typed in makes sense. But I can ask a question like this and just hit Enter. And then I get that answer. I can also ask a question like, hey, what value is in range cell B2? So what value is in B2? And it will tell me exactly which text is in that particular cell, right? I can also choose to execute little bits of code here. People do this for different reasons. Maybe they just want to make a quick edit, or maybe they want to be able to test out a line of code and actually see if it's going to work. This is a great place to test out your code. So maybe I would say something like, hey, that cell that I have selected on right now, when it comes to horizontal alignment, casing doesn't really matter here, I want it to be centered. So I'll say xlCenterAcross the selection. And when I hit Enter, it actually executes that bit of code. And now it is centered there in cell B2. If I changed my mind and I wanted this to be left alignment, I could say xlLeft and execute that. And there we go, that is left aligned now. The last thing we want to understand about our interface, or be able to see about our interface, our VBA interface that is, before we move on and start doing the coding for the rest of the class, is we want to know where our Object Browser is. And we want to know what it is. This is another thing that I want to be able to see. So I'm going to go to the View dropdown menu and choose my Object Browser. And I'll see this extra window that pops up here on the right-hand side. Now this is a library of all of the objects and collections and properties and methods and all the cool kinds of stuff that you can do with VBA. Now, when we're first starting out, we're just going to learn a few handy ones that are good to know as we're getting started with VBA. However, it is very nice to know that this library's in here so I can consult this library. Okay. So go ahead and pause the video and make sure that your interface, your VBA interface looks like mine. That is we should have a Project Explorer on the upper left-hand side. In that Project Explorer, we should have a folder that says Modules, right? And I got that folder that said Modules because I actually inserted a module. So I went to my Insert dropdown menu and I inserted a module right there. And that's what gave me that folder here, right there. And when I got that folder here and I got Module1, I also got this code window. So we do want to make sure that we have this code window up here. We should also have a Properties window down here, that usually comes standard. The other thing that we do want to see is our immediate window, which is on the bottom of our screen. Now, in order for me to see that I did have to go to the View dropdown menu and choose Immediate window. Now a second ago, I did also show us the Object Browser. We're not going to be using the Object Browser right now, but I did want us to know exactly where to find it. So just go ahead and make sure that you do see your Immediate window though, at the bottom of your screen. And you have a Code window up here on the upper right-hand side. Go ahead and pause your video and make sure that you have it and then come back. And we're actually going to do some scripting. For this next section, we're going to be using a file called Font Styles. And I have that file open on my screen up here called Font Styles. Now is a good time to pause the video and grab the file called Font Styles. Then open up VBA on the right-hand side of your screen so that you can see them both side by side. And once you have VBA open, go ahead and unpause the video and we'll continue. When we get a little bit more experienced in creating macros, we'll find ourselves creating them from scratch. We're going to be doing that in just a minute, but before we do, let's see how the recorder actually builds them. If we have our Excel window and VBA window side by side, we can watch the macro creation as the recorder works. So let's go ahead and record this macro here. What we're gonna be doing is we're going to be changing the font of this particular spreadsheet here. So let's say, for instance, one of the departments that gets this financial report needs to be able to, or prefers to see it in a particular font styling such as Times New Roman or Verdana or Calibri or something like that. And so every time I create this report, I have the issue of needing to go here and use my Font dropdown menu and go down and find Times New Roman. And maybe that takes me 10 seconds or so, but it would be a whole lot more handy if I had a macro that would automatically just change the formatting of the entire document here. And that's what we're going to be doing. And as we're doing this, make sure to keep an eye on the right-hand side, where we are going to be able to see certain changes being made by the macro recorder. All right, let's go ahead and get started. We're going to the Developer tab. And on the left-hand side, you're going to find your Code command group up at the top. You will see a little button that says Record Macro. Now, depending on how large you have your window open, it might look like a small icon like this, or it may look like a larger icon like this that actually says Record Macro. It'll all depend on how big you have your Excel window open. Mine is small, like this. I'm going to go ahead and click on Record Macro here, and we'll call this one, let's call this one TimesNewRoman, no spaces, has to start with a letter and I could assign a shortcut key. Maybe I'll assign the shortcut key Control + Shift + T and I'm holding down shift + T, so capital T. And that does make my shortcut key Control + Shift + T. I don't have the option to not have Control in the shortcut key. And I am going to store it in this particular workbook and I'm going to click Okay. And here we go. The first thing I'm going to be doing is clicking on this area in between my row headers and my column headers on the top left. This selects everything in my worksheet. And then I'm going to go to the Home tab and I'm going to choose to change all of the font here to Times New Roman and I'll scroll all the way down into the Ts and find Times New Roman. And there we go. And I'll click on my Developer tab and hit the Stop button. And that's created this particular macro here. Now on the right-hand side, you'll see that this new folder was created called Modules. I didn't have any macros in here ahead of time, so I didn't have this folder a few seconds ago, but the moment I started to record a macro, I did get this folder and now I can open up this folder and take a look in the module that has actually been created here called Module1. And if I double click on that, I will see the code window that was created here for me. So I do have this macro called TimesNewRoman, and this is what the macro recorder recorded. Now, let's say for instance, that I want to also be able to send this to a different department and that other department, they really prefer the font style Verdana. In this case, I'm going to record a macro. So I'm going to click on record and I'm going to call this one Verdana and I'm going to click Okay. And here we go, I'm selecting all my text first, going to the Home tab, finding my Font Styles, and I'm going to go and find Verdana here. Okay, notice when I do that, did you see what happened over here in my code window? A bunch of code got recorded by the macro recorder here. And so far that code says, this is the name of my macro, and this is some comments about it. It's called the Verdana macro. That's the comment, very simple. And then it says all of my cells select, and we'll get into this a little bit more, but then I have this big old chunk of code that happened here and I'm done with my macro. So I'm going to the Developer tab and I'm going to hit the Stop key. So if I want to switch this back to Times New Roman, I can do Control + Shift + T, that is the shortcut that I assigned to this that it changes all of the text there to my TimesNewRoman macro. But let's go ahead and take a look at what the macro recorder recorded. So we have our first macro up here. It's everything from the word Sub down through the end, where it says, End Sub. Now you'll find that all of your macros are going to have this kind of a structure. They're going to need a beginning statement and an ending statement. I like to refer to these as bookends. They help hold everything inside together. Okay, so I do have at the beginning Sub and End Sub, and I'll see that again in my other macros here, I see the next one, the Vedanta macro says Sub and End Sub right there. Okay, so that's always on the outside. Some other patterns I want to make note of are the fact that all of this text is green. And at the beginning of these lines are these little apostrophes. That apostrophe is used to comment out certain lines of stuff. So if I wanted to make a note to myself for later on, maybe I want to say, this is for the development department, maybe they prefer that particular look, and I'm going to hit my Enter key, and then notice that that shows up as green. So the nice part is that this isn't actually going to be run as code. This is just a note for me, right? This is called commenting out, and I'll be referring to this throughout the course. And indeed other macro coders are going to be using this term as well, commenting out. Now commenting out is also used for other strategies as well. For instance, when I start to look at my code here that the macro recorder created, there's actually a ton of stuff here that I don't need. Normally, if I were to create this macro on my own, it would be a whole lot more simple. But the macro recorder has a habit of recording a bunch of extra stuff that's not needed. You'll see this line that says, do you want to put a strike through on the text? And it says false, which means no, no, I don't want to strike through. No, I don't want a superscript. No, I don't want a subscript. No, I don't want an outline on the font. No, I don't want to shadow, right? So we're basically seeing all these, I don't want this, I don't want that, I don't need this, I don't need that. This is all just extra stuff that the macro recorder throws in because it doesn't really know whether or not you need it. So it just throws it in there and says, nope, you don't need it. But it turns out that all of this stuff is actually completely unnecessary. Now let's go ahead and take this concept with what we've seen so far, where it says, Cells.Select And then we're going to take this little statement here that says Selection.Font, name it, the name of that font, it's Times New Roman. And what we're going to do is we're going to condense this macro down to only have that little piece of text, because that turns out is all I need. So all of this other stuff in here, the With statement, all of these things down through End With, I actually don't need that stuff. And we'll talk about later today, why it says With and End With. That's a particular coding structure that you can create yourself. It's handy for a couple of different reasons, but it turns out for us, we don't actually need it. All we need is the statement that says Selection. I don't need any of this. And all I needed to say is Selection.Font and if I were to run this, it would run perfectly fine. Now let's go ahead and run our Verdana macro so we can switch our text back to Verdana so that then we can test our Times New Roman macro. I do have my Verdana macro down here. And if I place my cursor at the beginning of this macro, I can run it directly from VBA. If you look up in the tool bar in VBA, you'll see a button that looks like a play button. And the screen tip that pops up for me says, Run The Sub, right? Sub is short for sub procedure, which we'll be talking about in a little bit, but it says, run this up. Okay, I'm going to do that. I'm going to click on my play button. And it runs that entire macro, which turned all of the text into Verdana. Now I do have my Times New Roman macro now, and I want to see if it still works. I've deleted a bunch of lines of code, and I just want to see if it still works. So I'm going to click in my TimesNewRoman macro, and I'm going to click on my play button and indeed there, it actually worked. So I didn't need all this extra stuff that the macro recorder included. And you guys are going to find that across the board, when the macro recorder records stuff, about half of the time, it records a bunch of stuff that we don't actually need. So let's keep that in mind. The reason why you might want to remove this is just because if you have a really, really very large macro, it can be somewhat time consuming to run that macro and having little lines of extra code in here that are not necessary does make our macro longer to run. You'll see that I have one, two, three, four, five, six, seven, eight, nine extra lines of code and only one line of necessary code here, right? This is sort of considered the same line. So this is going to make my macro, having all this unnecessary stuff here is going to end up making my macro 10 times longer to run than this one up here. So in some cases I do want to remove the extra things that the macro recorder puts in there. This is one of those cases. If you haven't done so already go ahead and do that. What we're doing is we are recording a couple of simple macros here. We do have the Excel file called Font Styles open. And what we did is we recorded two macros, one called TimesNewRoman, and one called Verdana. The TimesNewRoman macro, what we did is we modified it so that it only had these rows of information. And these were consolidated from this line and a combination of the second two lines here. Go ahead and do that, consolidate your TimesNewRoman macro, so that it only has the lines of code that you actually need. And when you're done with that, come on back and we'll continue working with editing macros. Welcome back. In this next section, we're going to be modifying some macros that we've already created or editing the macros in the VBA editor. So let's say we've created this macro called Verdana and it converts all the texts in our worksheet to Verdana. However, it turns out that the department that we thought wanted Verdana, they actually now want Courier New. So instead of having a Verdana macro, I'm going to need a Courier New macro. You can go into the VBA interface and directly make any edits that you want to directly in the code window. For instance, in this line of code right here, where we have the font type populated, we can simply replace the word Verdana with Courier New. And we'll see that this actually runs. If I click on the play button, there we go. Now all of the text in my Excel file is converted to Courier New. Of course I'll want to clean this up a little bit. And so the name of my macro, which is up here, it's always after the word Sub. Sub stands for Sub procedure. And we'll get into that a little bit when we start creating macros completely from scratch. But here I go, I'm just modifying this macro that already exists, and I'm going to rename it. I'm going to call it Courier New. Now, remember you can't have any spaces there. And then down in here where I've put a little note in for myself that says, oh, this is a Verdana macro. I'm going to say this is a Courier New macro. And a little note for myself about which department that's actually for. Now, just like before, the macro recorder still has a bunch of code in here that's unnecessary. So if you haven't already done so with your second macro here, go ahead and make it look like the first. We're going to remove all the unnecessary code in here. Now, normally what somebody would do is they would go in here and delete this, but we actually recommend that you don't do that. What we recommend is that you move certain pieces of font around, such as this one that says .Name = "Courier New", and you just move this around. So I'm going to move this up after the font, and then I'm going to, and if you get messages like this, it's okay, because we're still in the process of working with this. But what I am going to do is I'm going to put my little apostrophe and I'm going to put the apostrophe before every single line that I'm thinking about deleting and what's handy about this is that it allows me to actually keep the code. So when I test the code out in just one second, I'm going to make sure that it actually works without all of this code in here, because the VBA program is not going to run any of this commented out code here. It's going to skip it. And so I'm just testing to make sure that my macro is actually going to work if I delete all this stuff. And if I test it out and indeed it does work, which apparently it did, I can run my TimesNewRoman again, to see the change. There's TimesNewRoman, then I'll run Courier New, and that worked as well. And now that I'm confident that it actually worked without these lines of code. Now I can actually go in here and delete these lines. Now that I'm confident that it didn't work. Sometimes we'll be modifying macros and we delete some code and then we try and run it and we get into a bunch of issues with it saying that it has a runtime error or it's not working or some kind of a problem arises. And for that reason, we want to be careful to not completely delete the code until we're absolutely confident that we don't need it. In fact, in the next example, we're going to talk about running into issues when we are modifying macros manually and we have some kind of a bug in the process. That process will be called the debugging process. And that's next. So if you haven't done so already, go ahead and modify your macros here so that we don't have any of the unneeded macro recorder code. And when you're done, you can go ahead and save this file and close out of it because we're going to be opening another one in the next section. Welcome back. For the next section, we're going to be using a file called Debugging. So take a moment to pause the video and make sure you have the file called Debugging open, and it's helpful to snap your windows left and right. So I'm going to take my Excel window and snap it left. And then here in Windows 10, now I can just click on the Visual Basic Window on the right-hand side. If you don't already have visual basic open, of course you can click on your Visual Basic button in Excel and then open up Visual Basic on the right-hand side and make sure that you can actually see some of the code here. You may need to click on modules and click on Module1 in order to be able to see the code here. Go ahead and pause to do that. And then come on back. When we start scripting, you're going to find that sometimes it doesn't go quite right. And in fact, this is part of the adventure of scripting. All of us do run into trouble and macros don't go right all the time and when they don't go right, we need to fix them. So it's going to be advantageous to understand how to use the helpful tools in VBA to discover what may have gone wrong with a macro. This is called debugging. We're going to be using some tools in the VBA toolbar in order to debug our macro here. Now, some of the tools that I really like to use for debugging aren't actually in the toolbar currently. You can customize this toolbar very easily. If you right-click on some of the empty space on the right-hand side, you can go down to where it says Customize. And then you can choose to go to the left-hand side and see a bunch of things that are in each one of these categories here. I'm going to find the debugging category, and I'm going to grab a couple of tools here that I like to use all the time. I'm going to find the tool that says Step Into, and I'm going to click and drag Step Into, and I'm going to place it up here. And I'm also going to grab Compile Project, that's going to help me too. There are a couple other tools in here that are nice to use. A lot of people would like to use Toggle Breakpoint. You could grab that as well. That basically allows you to just run a piece of your code instead of the whole macro itself. And there's some other ones in here that are helpful. I'm just going to grab those two, three. So we want Step Into, Compile Project, and Toggle Breakpoint. And once you've done that, go ahead and click on Close and you should have those tools up in your toolbar in VBA. Go ahead and pause the video and make sure that you do have those. Okay, so here we have an example of a macro that is about to go awry. On the tab called End Product in Excel, we'll see what this is actually supposed to look like when this macro runs appropriately. However, as we're about to see in just a second, this macro is not going to run appropriately and we're going to need to follow the code and see where it starts to run into problems. You'll notice that I have extra tabs down here at the bottom. I have this one that says End Product but I also have these ones that say Backup, Backup of the Backup and then I have the original data over here. A couple of things to be aware of when we are creating and running macros for the very first time or when we are debugging them, things to be aware of are that macros are very hard to undo. In fact, the program Excel itself will not undo any action that is executed by a macro meaning anything that you, that a macro does to your spreadsheet, you can't undo it. So that's one problem. Now, a lot of coders like to input little pieces of code that actually allow them to reverse those actions, but that only works on a really simple scale. And so it becomes helpful for us, when we are starting to create these macros to always create not just a backup, but a backup of the backup. We never want to run our macro on the original data, unless we are completely confident that it's going to work very well. So here I go, I'm going to go to the backup of backup sheet and I'm going to run this macro. Now, remember it's supposed to look like the sheet that says End Product here. On my backup of a backup, this is just a data dump. And I need to create this report on a regular basis, maybe five times a day or something like that. I've created a macro, that's supposed to do it. So I'm going to go to my macros button and I'm going to find the macro called CreateReport and I'm going to click on Run. Now, obviously that doesn't look exactly like I probably wanted it to look, right? I'm just going to go to Back Up and make an extra copy of this, where I'm going to continue to test this. So I'm going to the Backup Sheet and I'm choosing Move or Copy. I'm going to select Create a Copy and I'll put it ready for the one called Backup of Backup and click Okay. And now I have backup two and here we go. Now I'm actually going to see where the macro starts to go wrong. If I want to get this macro to execute line by line, then I'll actually be able to watch and see where I start to run into problems. So I'm going to go ahead and do that. The tool that's going to allow me to do that is this button that we just grabbed and put in our tool bar, which is called Step Into. Now, you can use the keyboard shortcut, which is F8 to Step Into your macro. I'm going to use the button here because that allows you to see it easily on my screen. However, I highly encourage you to try and use the shortcut keys. All right, so I'm going to step into my macro here. And once I do that, you'll see that this line of code gets highlighted. And now that line of code was run. And now it's highlighting the next line of code. Notice that this says, the last one that we were on says, oh, Sub CreateReport. So that's just the name of my macro. This next line says Insert Rows and Columns. And you'll notice down here that I have the ability to create this macro called InsertRowsAndColumns. And I'm actually using it in this other macro up here. So the process that's being controlled by that macro is actually written in the code that's down here, more of it we can't see that's down there, but I'm running it up in this macro up here. We'll talk about this later, but this is called calling a macro, which means that you're just executing the code in that macro. Okay, so I'm going to continue to click on Step Into, and you'll see that it hops down to that macro so we can actually run the code that's in that one. And then in a few minutes, it'll hop back up and run the code that's in the next macro. All right, so here we go. Here's our macro called Insert Rows and Columns, and I'm going to Step Into, and it's going to select rows two through four. And once I click on Step Into, it's actually going to do that. And you'll see in Excel, it's selecting rows two through four. Now the next line of code here says, oh, Selection.Insert. So it's going to be inserting some rows here and watch what happens when I do that. Okay. So the user probably meant to insert rows above all the data here, but that's not what they did. They inserted rows above. Row two has now been moved down to row five and we have a few extra rows, but we probably meant for what was in row one to go down there as well. So that's going to be one thing that we're going to need to fix. We're going to need to change that row two to row one. All right. Now my macro is going to select column A, it says Column A Select, okay here we go. I'm going to run that, clicking on Step Into. There it goes, it selects Column A and then it's going to take the selection and insert something to the right. And I'm just going to see what that looks like. Okay, that inserted another column to the left. That looks pretty good to me. All right, and now we're on the last line that says End Sub. Once I actually run that code that says End Sub it's going to hop me back up, because remember we are running this larger macro up here and we are just calling to these smaller macros in order. So it looks like the next macro that's going to run is this one called InsertText. Okay, I'm going to Step Into this process. And this one says, here's my sub procedure InsertText. Okay, and then we're going to hop down to the next line of code and that is range A1 Select, okay. And it selected range A10. And then the next line of code says active cell put in the words, "Our Global Company," and we'll talk about what all this coding stuff means, but just go ahead and watch this. So my guess is that the end user didn't actually mean to put this word in our global, these words, "Our Global Company" in cell A10. All that they meant to put that up as a title in cell A1. So I'm going to go ahead and correct that. There we go. All right, and I'm going to continue stepping through my macro here. So range A2 is going to get selected next, and then we're going to put in the word, "Stock Prices." And then in cell B4, we're going to put in "Symbol" and "Open" and "High", "Low", "Close", all of these neat little headers here are automatically being inserted. So that's great. No problems there. I'm going to click on Step Into, and now it's running the third macro in my original, large macro at the top. It's going to run this third macro called FormatText. Okay, let's Step Into that one, so here we go. We're walking through FormatText, and I'm looking at my Excel sheet and it says columns C through G select. And it's going to format all of those numerical values as currency figures. Okay, that looks good to me. All right, now it's going to select columns H through J and format these all as percent. That's good. And then it's going to select cell A1 and change the way that cell A1 looks. Course I don't have anything in cell A1 because I made a mistake, right. I accidentally put that text in cell A10, but when I run this again next time I've already made those corrections. So that should get fixed. And then it's going to change the look and feel of cell A2 there, and I'll continue running through my macro, which changes the look of my headers as well. And then my macro is going to select all these columns and auto-fit them. And that's very nice. Thank you very much. End Sub and there I'm through it. Okay. So I watched all these mistakes as they were happening and I corrected them. So I should be able to now go to the sheet called Backup, make another copy, and then find this item called Backup 3 and let's go ahead and run our macro on it. And we should have debugged that whole thing. And there it goes just the way it's supposed to look. If you haven't done so with me, go ahead and take the moment to pause the video, walk through this macro with the Step Into tool and make sure that all of the bugs are fixed in your macro. Don't forget to continue to back up your file every single time before you test it. And when you've done that, go ahead and come back and we'll move on. For the next couple of sections, we're going to be talking about a file called Creating Macros From Scratch. We're going to be using this particular file to create a macro from scratch. So make sure that you have this file open. You can pause the video right now to do that. Open up this file, and then also open up VBA and make sure that you can see this bit of coding on the right-hand side that will be located in Module1, Go ahead and do that now. Now the very first macro we're going to use isn't going to be made from scratch because it's already in here. We do have a macro on the right-hand side here. Now we are going to run this and just see what it does. So keep an eye on the Excel document on the left-hand side. And we're going to go ahead and run this macro and see what it does. So this macro looks like it puts in an extra row at the top. It puts in some headers here. We have employee ID, last name, first name. Those are our headers. And then it also turns them bold. Okay. So we're going to be looking at the code on the right-hand side and starting to read it as though these were normal sentences. The very first line here says Rows. It starts with the word Rows. Following that we have the exact row we want to be talking about in parentheses. Now this is also surrounded by quotations. You're going to see this a whole lot. So this is the syntax of this particular object statement. Rows are an object. And like we were saying at the beginning of class, every object oriented programming operates in a very similar manner. So objects are always stated first. And then after we state the object, in this case Rows, then we can say what we want to do with it, or what color we want it to be or what we want it to do. Something like that. But we always must state the object first. So here we have this statement that says Rows, specifically, row one. And then we're saying, we want to Insert. So this little statement of Insert, this is a different kind of terminology. So we have our objects, that's our Rows, that's our object. And then we have this thing called Insert. Insert is what's called a method and let's take a look at some VBA grammar. Now let's start by talking about objects. There are many different kinds of objects that you can use to start your statements. These are just some common ones. Sheets are objects, tables, charts, cells, columns and rows are all objects. And you can use them to start your statements in VBA. Following these objects, then we need to be able to say what we want to do with them or what we want, or have some way to describe them, right? Maybe we want it to be yellow or something like that. Properties are the way that we describe certain objects. So if we have a cell, that's cell A1, maybe you want it to be a specific color. Maybe you want a dollar to show up as a specific format, or maybe you want your text to show up with a particular look. These are descriptions about the way that any object is going to look or basically just descriptions about them. You can think of these as adjectives. And then the third kind of terminology we'll talk about is methods. Methods do something. You can think of these like verbs. So here in the macro coding that we have over here, we can see that each one of our lines is starting with an object, of course. And then following Rows, I'm saying I want to insert extra rows. Inserting, that's an action. So insert is a method. Following that line will say that the next thing we want to do is select range A1, which means select cell A1, or rather refer to cell A1. And then after that, we're saying we want the value of cell A1 to be the words, "Employee ID" or "Emp ID". And then we do the same thing for cells B1 and C1. And then at the bottom of our macro code, here we are then selecting some rows. So we're saying Rows A1 and then specifically that row, I want to alter the font. I want to make the font bold. And the way that we do this is we say Bold = True. And the reason is that bold can either be or not be, but there aren't any more options for whether bold is or is not or ways that bold can be. It can either be bold or not bold. And so for that reason, that is a logical explanation there that we have in the terms of true or false. So we do say Bold = True. If I wanted to turn off bold, I would just say Bold = False. And then at the very last line here, I am just grabbing all my content or rather deselecting all of my content by selecting on range A1. Let's go ahead and build this ourselves. All right, let's move on to the sheet called, MakeMacro. And we are still in the file called Creating Macros From Scratch. And I'm on the sheet called MakeMacro. This is where I'm actually going to be able to create my macro from scratch. Now let's just remember what my macro needs to accomplish here. We need to insert a row at the top of all of our data, and then we're going to insert headers in that top row for each column. The third thing we're going to do is turn Row on Bold. And the fourth thing we're going to do is de-select. So let's go into VBA and we're going to create a whole new module for ourselves. So instead of using Module1, I want us to actually insert a new module. So use your Insert dropdown menu and choose Module. And that inserts a new module for us. Now, the other thing that we need to do is insert a new Procedure. So I'm going to Insert again and choosing Procedure. And now I have to figure out what kind of a procedure I want to make. Now, procedures are a way of saying like, this is a large macro, so you can put a bunch of different macros in a procedure, but they're basically the same kind of thing. They just run a bunch of code. I'm going to name my macro here, or my procedure here. And I'm going to name this FormatDataTest. There are a few different kinds of procedures that you could choose. You could choose a sub procedure. This is just a regular macro. So sub procedures are just like many procedures. You can think of them as like regular macros. Maybe you'd have a big macro what'd you think of as a procedure, right? Okay. The other kind of procedure that you can have is a function procedure. At the near the end of this course, we're going to be talking about how to design your own functions. So if there's not a function, you already not a function that you would like in Excel. Maybe there's a function in Excel that you would like to be there. Maybe you need to accomplish some kind of a task and there isn't a function already for it in Excel. And so you're having to build it using a formula all the time. In this case, what you can do is actually build it as a macro and then it becomes a usable function in Excel. So that's pretty cool. However, we're going to stick to the basics for now. We're choosing the sub procedure, and then we're not going to be using this a whole lot, but just as an explanation, we have the options between public or private. We're going to be using public for the majority of this class because public macros are macros that can be used in other areas. There are macros that can be used by other macros even. Our previous example, where we were formatting text, you probably noticed that that macro called two other macros, which is something we're going to be talking about later. We will need those macros to be public in order to be able to do that. So I've named my macro FormatDataTest and I'm clicking Okay. Now I have my bookends here. My first bookend says Public Sub, public sub procedure. And then my ending bookend says End Sub, and I'm going to need these bookends to surround any group of code, basically holds everything together. All right, I am going to give myself a little bit of space here. So I'm putting a few hard Enters in there, hitting Enter a few times, and this is going to give me space to actually design my code here. All right, so I'm just going to follow these steps that we lined out here. The very first step that I'm going to do is insert a row. And of course, I always have to name my object first. So I'm saying Rows. That's my very first object here. And I'm going to explain which rows I want to select. I want Rows 1 and I want the entirety of Row 1. And that's how we describe that to VBA with this colon in between 1, 1:1. And we surround both of those with the quotation marks. And we surround the quotation marks with this parenthesis. Following my object here, I'm always going to put a period that lets VBA know what the differences between my objects and my methods and my properties and all that stuff. So I'm putting my period in here and then I am putting in the name of the method that I want to happen with this row. So I want to grab this row and then I want to insert another row. All right, now that I've inserted a row at the top, the next thing I'm going to want to do is put in some headers here. So I'm going to select range A1. That's where my first header is going to go. And the value of range A1 should be the title and the header name, employee, "Emp ID". Okay. And my next header is going to go on range b1. You don't have to worry about being case sensitive here, and that's going to be "Last Name" and then C1 is their "First Name". Now, once we put in those headers there, I do want to take all those headers that I've made in row 1 and turn it all bold. So I'm going to tell VBA, please grab row or rows, specifically, Row 1. And the fonts in there, I want you to turn it bold. And the way that I express that of course is with TRUE. I'm in the habit of typing in TRUE with all caps, but of course you don't have to. And in fact, when I hit Enter here, see how it makes that a proper capitalization. All right, and what I'm all done, this is just for good measure. It's absolutely not necessary. All I'm going to do is just de-select here. Meaning I already had the row selected, right? That was going to turn it bold. And so it's still going to be selected unless I de-select on range A1 and I'll just put in range A1 Select, that'll just select that one cell on the top left. Go ahead and pause the video. And when you're done writing out all of your script here, come on back and we'll run the code. You may notice at times that a little window pops up as you're typing in certain objects or methods or properties. This is called the IntelliSense window. It's not a term that you have to remember, but it is very important to know that it exists because it's pretty handy. See here, I do have the IntelliSense window suggesting that maybe I'm looking for the term Select and I get this little icon on the left-hand side, that's the icon for methods. And I could double click on that. And that would insert the rest of that if I hadn't finished filling it out already. Okay, so here we go. Let's go ahead and test this out. I am going to place my cursor at the top of my macro and click on my play button. And the macro I'm looking to run is called FormatDataTest. That's the one we just made and I'm going to click on Run and there it goes. Go ahead and test that out. You can test it out on multiple different sheets here, if you would like. And once you've tested that out and practiced, if you would like, come on back and then we'll be making some more interesting macros. For the rest of this course, we're going to be creating some of our own scripting or a lot of our own scripting. In order to be able to refer to any given cell we're going to want to know, or be a little bit familiar with the range selection object, or rather the range object. Now, there are many different ways to express the range object, but the basics just says, I'm going to select a range. That range is, let's say C3. I've selected this cell inside this completely blank workbook here using that particular range object. And I'm going to show you that in just a moment. I've typed in that cell "select this cell". So let's say my cursor is down here somewhere else in the interface. And I want to be able to select that particular cell. I can simply type in range. That's my object. Now I'm going to need to specify exactly which range I want to select, which I do using the syntax parentheses, quotation marks then the actual cell I want to select or cells I want to select. And then I close it out with the quotation marks and a ending parentheses. Now, if I actually want to select that item, I am going to have to put a Select method on the end of this object that actually not just refers to that range, but selects it. And when I hit my Enter key, you'll see that I have now selected that given cell. That's the most basic way to make a simple cell selection in VBA, but there are a whole lot more ways that you can make a cell selection. And it all depends on what you want to do. Let's just look at a few different ways that we can make a particular cell selection. Using the range object, we can use the simple example of selecting Range C3 in the example that I just showed us, I could also say a range inside of a range. We do that by typing Range.Range. What this does, is it essentially navigates from the beginning of the spreadsheet or A1, if you will, to Range B2. And then after that, I make another jump to another range that isn't actually the cell B2, but rather what it means is that it's moving over two and moving down two, so you can have a range inside of a range. You don't see this happen very often. You do see the macro recorder do it, however, so don't be fooled when you see it done by the macro recorder. It does mean a range within a range, which does not refer to the cell as you would be familiar with it as the regular cell B2. What it means is that it's offsetting past that cell. You can also use the object called Cells. Cells uses numerical values to refer to the column. Now, the benefit of using cells is that VBA and Excel both like to use integers rather than text strings. So you'll find that this one sometimes works a little bit faster. You can also use the shorthand, which is the cell address, surrounded by the square brackets. And then later on in this course, we are going to be using the offset method. If we were going to be using that to select cell C3, we would start with something like, let's say cell A1, which we have there in the fifth example range, A1.Offset, offset is my method. And what that does is it essentially moves us in a certain direction. The Offset method does use integers to specify how far down and how far over we're going. The first one is how far down we're going. The second one is how far over we're going. And we are going to cover this later on in the course as well. So you don't need to memorize it right now. The final way that we could refer to a range at this level of VBA coding anyway, is by using a variable. Now we're going to be talking about variables a whole lot later. So this is not something that you need to memorize right now. However, if I stored a particular cell reference or cell range as a variable, let's call it myRange for example. Later on, when I want to be able to refer to that particular range, I can say, oh, I'm going to arrange which range? The range that I named myRange. And those are the many different ways that we could refer to ranges. And they all worked great. If I were to do this in the immediate window, I could say Range C3.Select. Or in our second example, we started by choosing range B2 and then we said, range B2 again, which you may or may not remember, basically offsets from the original and makes that selection. And I'm just going to go ahead and click on a random cell over here so that you can actually see this in action. And when I execute this, I have selected on cell C3. Now I could also use the Cells object, which I described earlier. Cells uses integers, okay. And we'll see in that little hint window, the Row Index. So the row that I would like to choose, and then the column that I would like to choose. Course, I do need to put in my Select method there. And there we go, it'll select it. Now, of course, if you actually want to see this in action, I'll click over here for you and show you, there we go. And it works. I can use my shorthand using the square brackets, C3 square bracket, and that works as well. And I can also use an offset, which again, we are not needing to learn right now. I just want you to see that there are many different options for you. So I can start by selecting any given cell and then move in a certain direction. Let's say two rows down and two rows over, which is going to place us at cell C3, if I select it, of course. And there we go. So those are the many different ways that you can express a range in VBA. And you'll want to keep those in mind as we're going throughout the course. If you haven't done so already, open up a completely blank workbook, open up your Visual Basics for Application window on the right-hand side, go ahead and type in any cell in your Excel spreadsheet. And I want you to practice using these ranges to actually select that cell, using the different options that you have available. Go ahead and come back and use any of the given options that are listed on the slide here to actually select that range. The next most important selection tool that we're going to have is the method called Select, which we've already seen and the object called Selection. They are very closely related, but they do very different things. As we saw in the last example, I could select a particular range by referring to that range directly with the name of that range using the Range object. And then after that, I could say Select and that'll select Range C3. Now that I've actually selected that item, I can now refer to it using the object called Selection. Now notice the difference between selection and range. Selection does not require me to actually specify a cell name, meaning the range is an absolute cell reference. It absolutely always refers to a particular given cell, whereas the Selection object instead refers to what has already been selected. So this can be rather dynamic. We can use this in all different kinds of situations. And indeed later on, throughout the course, we're going to be using this bit of code to be able to allow other bits of code to make a selection for us, and then be able to run whatever kind of specifications we would like here. Now that we have selected this given cell, we can do a number of important things. I could put a particular value in this cell. I could say something like, "Hello world". And that changes the text that is actually placed in that cell to whatever I chose. I can also change some of the features of this particular cell. I could say, okay, the interior color, and you don't have to memorize this, of this cell is something like 65535 which I happen to have memorized. It is also known as VB yellow. It is one of the most typical color selections that you can make in visual basic. Should you be interested, you can absolutely go into the slide deck that has been provided for you for this course. And there are some slides that are all about color specifications in VBA. There are groups called Theme Colors and VB Colors that have a specific number of assignments already in VBA. These are the lists of the possible assignments that we could use. You do not have to remember these right now. We will be taking a look at a couple of them, but basically you can come back to the slide anytime you would like to be able to check these out. There are other options for how you can color code things in VBA. And that is using what's referred to as RGB colors or hex colors or hexadecimal. You can Google or Bing either of these options, either of these terms. And you will get nice websites with full listings of different kinds of RGB colors, depending on which particular hue you are interested in. If we were to use either of these, I could just say Selection.Interior.Color = 65535. That's the hex code for this. Or I could say something like VB yellow or VB blue. I'll say blue so that it actually changes the color and we can see it happen there. There we go. The interior has changed now to blue. That's how we use the VB colors. You can also use the same colors by saying, hey, this is going to be a theme color. And we could refer to a specific theme color by saying X, L, you'll see XL starting a lot of these property specifications, xlThemeColor, we'll say Dark1. Now Dark1 happens to not be very dark. We'll try Dark2. That also happens to not be very dark, but we can see it in action. And then finally you can choose any of your RGB colors by simply saying RGB first and then specifying the three digits of that color. Like so. Now of course, Selection is used for much more than just color. You could say Selection.Font like we did earlier and say, I want to turn that font bold. And there we go, it's bold. It's kind of hard to tell, because that cell is a little bit dark, but that text has bolded there. Here, let's use both of them together. Now you can see that it's actually bold and that's how we use the Selection object. We are also going to be using that a whole lot throughout the course. So if you haven't done so already, go ahead and get a few practices in using the Selection object and come on back and then we'll start doing some very interesting scripting. It's at this point in VBA programming, where we're going to need to start being able to insert certain contents or certain values into given cells. Now there is a property that does that very nicely for us, and it's called Value. It works very well with either the Range object or the Selection object. Those are the two that we're going to be focusing on right now. I could simply state my Range object such as cell A1, and I could say, I want the value of that particular cell to be the word or words, "Hello world". And that gets inserted there. Now I've already done that in cell C3 as well I've inserted something in cell C3 and I turned it bold in the last example. Let's say now I want to be able to clear contents from a given cell. If I wanted to clear the contents from cell A1, for instance, I would simply refer to cell A1 using my Range object. And I would say, Clear. Notice there are a bunch more options here. We have Clear, we have Clear Comments. We have Clear Contents. That will keep the formatting, but remove the contents from a given cell. You can also clear the format specifically, so not the contents but the formats only. Clear is for Clear all. And when I hit that, it absolutely clears everything from that given cell. If you haven't done so already take a moment to use your immediate window and practice using the Range.Value and the Range.Clear. And when you've done that one, practice the exact same with Selection.Value and Selection.Clear. Often we get content that comes in from a data dump, or maybe we have a coworker that does some data entry for us. Either way, sometimes we get a bunch of content and it's our job to spend quite a bit of our own time, just being able to format that content. And so being able to turn that into a macro would be highly advantageous for us. I often have circumstances in which I get a bunch of content and I need to rename all the sheets that came in with that particular content. In order to do that, I have to be somewhat familiar with the sheets and active sheet objects. Now I'm going to use my immediate window to exemplify these first few examples. If I wanted to name the active sheet, which happens to be sheet four in this case, I could simply refer to active sheet. That's the name of the object. We could use a property called Name and I'll just name that "Portfolio4". And when I hit enter, that has renamed my sheet to "Portfolio4". Now, let's say I want to be able to move to a different one of my sheets. I can do that in a few different ways. I can do that using the Select method, but I am going to need to refer to which object I need to go to first. So I could say something like sheets, the first sheet, in order. If I used sheets one, the first sheet was in the order of sheets in my workbook, or I could refer to the sheet by saying sheet one. That happens to be the name of sheet one right now. And then I could say Select. So if I wanted to turn this into a macro that automatically went in and renamed all my sheets for me, I could easily do that by navigating to a given sheet, using the Select method. And then naming that using the Name property. Why don't you go ahead and practice that. Insert a new module, using the Insert dropdown menu, create a module for yourself and create a procedure as well. I'm going to name mine, Testing123, and I'm going to put a macro in here that's going to go to each sheet and then rename it whatever you'd like. When you're done, you should have all four sheets selected in your workbook. And then you should have each one renamed. When you're done coding that, go ahead and run it and make sure that it works. And then come on back. Welcome back. The last little bit of important information that we're going to learn before we actually get a chance to practice everything we've learned so far is the Current Region property. Now the Current Region property basically does what Control Aid does for you if you are actually operating within Excel itself. So if I selected one of the cells within this dataset here, and I'm in the file called Insert and Format Text, you can join me if you would like. If I select any one of the cells inside of any one of these lists, I happen to be in the list on the sheet called Hybrid. If I were to click on one of these cells and use Control + A, it selects all of the data and it knows exactly where to stop because I have a well-formed list. Now, if I were to do this within VBA, I would use the Current Region property. So I'm going to start by typing in the word Selection, the object Selection, and I'm going to use the current region using the Current Region property. And then after I've referred to the current region, I'm actually going to say, I want to select it. And once I actually execute this, you'll see this in action. And indeed VBA is able to select this whole table. We're going to be using this throughout the course of our class. So make sure you are able to explore this a little bit and practice it. And once you have done so, come on back and then we're actually going to put everything we've learned so far into a practice exercise. This is a practice video. What we're going to be doing is taking everything that we've learned so far throughout this course and being able to implement it all at the same time into one macro. I do have the file called Insert and Format Text open, and I've opened my Visual Basic for Applications editor, over on the right-hand side. I am going to select the sheet called Conservative. That's what I'm going to get started on. So make sure that you're on the sheet called Conservative. The tasks that we are going to be accomplishing in this particular rehearsal is we're going to be inserting some headers. We're going to be formatting those headers and columns of data all the same time. And we're going to be able to execute these on all the other sheets that we have inside of this workbook. Furthermore, the last thing that we're going to do is actually select all the content that we have organized in this list here. And we're going to color the background of that so that it looks like a nice visually-pleasing list there. So that's the exercise that we're going to go through. We're going to create three different bits of macro script in order to be able to do this. The first two we're actually going to be doing with the macro recorder. Because if the macro recorder can do anything fast for us, we prefer to use the macro recorder. So go ahead and get started with the macro recorder. And this is going to be the macro that actually inserts our text. So I'm going to call it InsertText. I'm going to call it InsertTxt. Then I'm going to click Okay. And I'm going to get started by just putting some titles on here. Now I'm going to call this "Our Global Company," and I'm putting that in cell A1. In cell A2, I'm going to say this always talks about our stock prices. Then in B4, I'm going to put in the name of my Portfolio here, "Conservative Portfolio". Now you may already be wondering, well, is that going to cause a problem? If I'm writing Conservative Portfolio here, what if I move on to the Balanced Portfolio and the aggressive Portfolio and the defensive Portfolio, then this little bit of script that I've created here, isn't actually going to work and you would be right. We are going to be putting in some custom script in a few minutes, that's going to check the name of our sheet for us, but for now, let's just keep going. I'm going to put in some titles of my list here. So this is my Symbol and I have the Open, Close and then the Net Change with the time period. And that's all the texts that I need to be able to insert with this particular macro. So I'm going to go ahead and click Stop, and it's stopped recording. And I'm going to go look and see what the macro recorder made for me. It's going to be located in my Modules folder in my Visual Basic for Applications window. Here we go. Let's take a look at it. Now the macro recorder actually put in a bunch of stuff I don't need and some stuff that looks a little bit weird. This thing that says Formula R1C1 that's a really antiquated coding language. That just means value. So most people, these days don't actually type that in, what they type in is Value, like I just did. And in fact, the macro recorder also said, Range A1 select and then ActiveCell input the value. But I don't need that. That's a little bit redundant. So I could shorten this down to simply Range A4.Value = "Our Global Company". And I'm going to do that for each one of these lines. That's just going to clean it all up for me. The only reason that you would want to clean this up, it's a little bit less byte expensive. And the other benefit is that when I come back and look at this macro code a few months from now, it's nicer for me to have it cleaner so it's easier for me to tell what's going on. Most of we macro programmers have the real world scenario in which we come back to a macro later on and we can't remember exactly what it did. And so we have to read it all over again. So the cleaner it is, the nicer it is. Now we were talking earlier about the difficulty with having this word "Conservative" in here, because I'm going to have multiple different Portfolios and they're going to need their own names. So instead of having the macro code actually put in the word "Conservative," I'm going to take that out. And I'm going to tell VB, hey, check the name of my sheet right here, check the name of my sheet and put in that word. So as we learned earlier, we can refer to the active sheet and the name of that active sheet using ActiveSheet.Name and then I can simply concatenate that with the word "Portfolio". I did intentionally leave a space here because I want the word "Conservative" to be separated from the word "Portfolio" because that's going to be nicer to look at. And for the most part I'm done inserting my text. All right, if I wanted to test this out, I could go to the next sheet called Balanced and I could click on my play button. And indeed that all gets inserted for me, including the word "Balanced" in this case, instead of the word "Conservative," which is exactly what I wanted. If you haven't done so already, go ahead and record your macro, clean it up and then make sure to make this slight modification to the code so that your code will actually insert the name of the sheet and name your Portfolios in a custom manner. And then come on back and we'll continue. The next little bit that we're going to be adding to this is a formatting macro. So I'm going to go back to my Conservative sheet, because this is where I'm creating everything, although I wouldn't have to, and I'm going to go and record another macro here. I'm going to click on record and this one is going to be called FormatTxt and I'm going to click Okay. And I'm just going to do some basic formatting here, cell A1, I'm going to make this text size 20, I'm going to make it bold and the next cell down that says "Stock Prices," maybe I'll make this 18 and bold and where it says Conservative Portfolio, maybe I want that to be 14 and bold. And my headers here I'll make those bold as well and size 12. Now, the other thing I need to do is change all my numerical values here that are looking a little bit confusing. I want them to all look like numerical values. So I'm going to click and drag on my column headers to select multiple. And then I'm just going to click on my dollar sign on my Home tab of the ribbon in the Number command group. And that's going to convert those all to a dollar sign. Now a couple of these columns are a little bit too small, especially the one called Net Change. So I can't see everything that's there. And so I'm going to select all the columns in my list here, all of them, including B, so B through E, and I'm going to auto fit them by hovering over the divider in between any of them and double clicking. That's going to make it big enough to fit all of the content. And then the last thing I'm going to do is de-select here. I like to de-select so I don't leave a bunch of things selected. So I'll de-select on E1 and that's fine. And I'm done. I'm going to go to the Developer tab and stop recording. Now, of course, we're going to go back and look at the code and see what's here that we need and what's here that we don't need, just like in the last example. With this particular example, when it comes to formatting text, the macro recorder brings in a lot of things that we don't need. So you'll see in this very first line here, it says, okay, select cell A1. And then you'll see this thing called a With End With statement. The reason this is here is because without it, each one of these slight modifications, like the name of the font, which is Ariel, the size of the font, which is 20, the strike through whether I want it or not, the superscript whether I want it or not, et cetera, all of these would have to have the words, Selection.Font in front of them normally. So it would be Selection.Font.Name, Selection.Font.Size, Selection.Font.Strikethrough. And that would start to get a little bit cumbersome for me as a programmer. And so I have this nice thing called a With End With statement that allows me to make a object statement once Selection.Font, and then refer to all of the properties of that particular object all at the same time. So I don't have to type my object name over and over and over again. In this case, there's a lot of things in here I don't actually need like theme font, tint, and shade, color index, underlying shadow, blah, blah, blah, blah. I don't need anything except for the name and the size. And so I'm going to be deleting those. And so this is how I'm going to clean up this. Now you'll see this statement down here that says Selection.Font.Bold = True. Well, technically I probably want that up here and again, I don't have to have it in here, but the nice thing about moving that around is that later on, when I look at this macro code four months from now, or whatever, that I'll be able to see all of my font formatting directly in the same spot. If you haven't done so already, take a second to pause the video, record your macro, that actually formats your content. Then come back in here and clean up whatever you can. Ideally, if you are not sure that you can delete certain texts, make sure to comment it out and then run it, see if it works. And if it works, then you can actually delete all that content. So go ahead and do that now, come on back and we'll do the third step. And the last little part of this particular practice is for me to select all the content that happens to be in this list and make it a slightly different interior color. And that way it'll kind of look nice. However, all of my lists happen to be different sizes. And so I am going to want to use the Current Region property that's going to figure out exactly what the size is. Then I need a Format there and it's going to do it for me. In order to do this, I'm actually going to do my own manual scripting. You could also do it with the macro recorder if you wanted to, but I'm going to take this opportunity to insert a new procedure here. A procedure is basically a mini macro or a macro, and I'm going to call this one FormatList, click Okay. And all I'm going to do is start by selecting range B6, because that always has the list content in it. As you can see here, my list starts with B6 and it just so happens that it also starts in B6 with all the rest of the sheets there. So I'll say Range, B6.CurrentRegion.Select and then I'll say based on that selection, I want the interior of all those cells to be a particular theme color, and maybe I'll choose xlThemeColor, Dark2. And if I run this on the given sheet, I'll be able to see that it quickly formats my list there. Okay. Now that you have created all of these macros, go to all the rest of the sheets in the document and run them all here and just sort of appreciate at this moment, exactly how much faster it would be to create these macros and then have them permanently from now on, in every single sheet here and use them instead of doing it manually. When you're done, come on back and we'll keep going. Thanks for watching. Don't forget, we also offer live classes and office applications, professional development and private training. visit learnit.com for more details, please remember to Like and Subscribe and let us know your thoughts in the comments. Thank you for choosing learn it. (gentle music)

Channel: Learnit Training

Views: 2,378,849

Rating: 4.9333491 out of 5

Keywords: free excel 2016 tutorial, excel 2016 Beginner tutorial, excel 2016, microsoft excel 2016, learn excel 2016, excel 2016 tips and tricks, learn excel, excel tutorial, excel training, tips and tricks excel 2016, excel 2016 how to, free excel 2016 training, excel 2016 free tutorial, excel 2016 tutorial free, free, how to use excel 2016, e learning excel 2016 video, vba tutorial, VBA, Vba beginner, Vba Beginner tutorial, Excel VBA, Excel VBA Tutorial

Id: G05TrN7nt6k

Length: 130min 30sec (7830 seconds)

Published: Fri Nov 22 2019