I realized that for many of you, you are just getting into some of your first Excel spreadsheets, and so you're beginners in that regard. But I wanted to make you all aware of VBA Macros. It's never too early to start thinking about how you can customize or tailor your Excel worksheet environment using VBA Macros. So I'm just going to give you a quick introduction to VBA Macros in this screencast. So what I'm going to be showing you first in this screencast is how to make custom macros or shortcut keys. What if you have a really long label like this, and every day you're working with these long labels and maybe you want to make it bigger, you want to make it bold and italics, you want to make it green, and then you want to wrap the text. Let's say you're doing that every day and that's really time consuming. You can make little shortcut keys using VBA Macros. Let's just put in another really long label. I've made this already, I'm going to show you how to make this. But I can do Control Shift F and it automatically formats that. It doesn't have to be limited to simple formatting on the worksheet. You can do all sorts of different things, and this is using a built-in programming language called VBA that comes along with the Windows version of Excel. So let me show you how to build that macro. The first thing, if you want to put in macros in your workbook, you're going to need the Developer tab. To insert the Developer tab, you go up here to File, Options, and then we can click on Customize Ribbon. When you click on Customize Ribbon, you see all the main tabs are shown over here. So the ones check marked are the ones that already show up when you open Excel, and there's a tab down here called the Developer tab that by default is not selected. So go ahead and click on that and then click Okay. You notice now we have this Developer tab. Now what we're going to do is we're going to record a macro. You can click on the Developer tab, and then once we're ready to go, we can click Record Macro, and it's essentially making code behind the scenes for every move that we make. So I'm just going to put in, this is a really long label, and go ahead and click in that box, and we're going to record a macro. Now you can name the macro here, so I'm just going to call this formatting, and we have a shortcut key that you can select. So let's hold down the Shift key and F, and when I did that, when I press F, it did Control Shift F. So that's going to be the shortcut key that's going to run this macro that we're recording. If you want, you can store this in a personal macro workbook. So it'll be available to all workbooks, but I'm just going to do it in this workbook. You're welcome to put a description there if you'd like, but I'm going to go ahead and click Okay. So now, it's recording every move that I make, and that's really important because a lot of times, you'll accidentally forget to stop recording up here. So let's just go to the home tab. I'm going to increase the size, maybe to 12, I'm going to make this bold and italics. Let's go ahead and change the font color to this nice blue, and the last thing we're going to do is wrap text. That's the formating that I want to do. So I'm going to go back up here to the Developer tab and I'm going to stop recording. Now, this is beyond the scope of what I wanted to teach you here, but you can always go into Visual Basic here on the Developer tab, and you can open up the Modules folder here, and you can double-click here, and this is VBA, Visual Basic for Applications code for what we just did. So just FYI, if you're interested. But a lot of times, you don't really need to know what's going on behind the scenes. So now, if we have another really long label and we want to format it the same way, I don't have to go through everything I did in the home tab, I can just do a Control Shift F and it formats that as we had recorded. I wanted to show you just another couple of examples. So maybe every day you're opening up a new workbook and you're typing in the days of the week in cells A1 through G1, you probably know that we can auto fill this, I have a separate screencast on that. So it's not too hard, but maybe you just want a quick shortcut key to do that in any workbook that you opened. So I'm going to go ahead and record a macro, and we're going to name this days because it's going to be the days of the week. Let's do Control Shift G. You can choose whatever you'd like. But I'm going to store this macro in my personal macro workbook on my computer. So it's going to be available to all workbooks that I ever open on this computer. I go ahead and click Okay. So its recording, and I'm just going to do the Monday. I'm going to drag this over to Sunday, and then I'm going to stop recording. Now because I have recorded that in my personal workbook, well, first of all, I can go to a different sheet and I can do a Control Shift G, and it puts those in cells A1 through G1, or I can go insert a new workbook and I can do Control Shift G, and it's going to put those days of the week in cells A1 to G1. You may have been wondering why there's this huge relative references. Well, maybe you didn't want it to be in cells A1 to G1, maybe you just want to be able to click somewhere and have it fill down. Let's do the months of the year going down. If you don't have relative references selected, right now, it is not selected, then it's always going to be the same cells that you clicked on when you recorded that macro. So when I record the previous macro, I was selecting cells A1 to G1. But if I use relative references, and I'm going to go ahead and record the macro now, let's do Control Shift M for months, and I'll go ahead and record that just in this workbook. Click Okay, and I could do January, I'm going to drag this down all the way to December, and then I'm going to stop recording. Now because I used relative references, it always is going to start in the cell that I have selected when I run the macro. So if I do Control Shift M, it puts that there, and I can go to a different sheet and Control Shift M. There's one more really important thing that I forgot to tell you guys. If you have any macros that have been recorded in your file, you can't save it as a.XLSX file, otherwise those macros will not be available, they won't be saved. So instead, you go up here to File, and then instead of saving as, let's just say we browse somewhere, I'm going to browse to my desktop, instead of saving as an Excel workbook, you need to make sure that you are saving as the second option, Excel Macro Enabled Workbook, it's got an XLSM extension, and if you don't do that, then the macros, they aren't going to be saved and they're not going to be available. So this is just a real quick introduction to VBA Macros, and if you're excited about this stuff or you want to really enhance your Excel worksheets, I do have a complete specialization, it is called Excel/VBA for Creative Problem Solving that's on Coursera, and you guys should definitely check it out after you are done with these courses. Thanks for watching.
