Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Let's look at creating macros inside of Microsoft Excel. The act of creating macros can be as simple as clicking Record on your VCR and recording actions that you take, then you can take that recorded file and play it back as often as you want to repeat those actions, or you can take that file and assign a shortcut key to it, you can assign it to your own custom menus, or you can drop an icon on the existing toolbar and utilize it any time you want with a simple click. The key to creating effective macros is knowing when to record your actions so that they become as useful as possible. We're going to see that right here. I should also note at this point that recording a macro records a complex VBA script. VBA is Visual Basic for Applications, and it really is a mini development environment that exists within the Office Suite. If you know how to script inside of VBA, you can create macros that really enhance the functionality of any of the applications inside the Office Suite. You can make your Excel spreadsheet look and feel more like a true application than anything else. We're going to stick to the simple record and playback method, which is how most people record macros and provides for more power than most people need anyway. Let's start by adding a row here in our spreadsheet, and in the first cell we'll type our title: QUARTERLY SALES. Now that we have our title created, let's begin recording a macro so that we can format this title, and then then we can do it again and again any time we need to format a title within a spreadsheet. Let's go up to the Tools menu and you'll expand this in case you don't see macro. You'll notice that the Macro's submenu is also where you access your Visual Basic Editor. We're going to click Record New Macro, and when we do we need to make a few choices. First the macro name. Let's call it TitleFormat. Now you can't use any special characters and you can't leave any spaces here. If you don't like having those two words together, put an underscore, but because this is also a filename, spaces are not allowed, and I'm going to use Control+F, and it's asking me where I want to store this macro. I can store this macro in a workbook, in other words it'll stick with this file or I can store it globally in my Personal Macro Workbook. I'm going to stick with the default for right now, and say OK. And you'll notice this small toolbar that's appeared. Now it's not literally recording you like a video recorder, but it is looking for actions and it's going to record each and every action that you take now within your spreadsheet. So let's start by selecting A1 through E1 and using on our toolbar the Merge and Center button.
Now we can bold that and increase the size slightly, and there we have an effective title. Let's stop recording and we've just saved a macro that will allow us to format a title any time we wish. In order to use that macro however, we need to have information set in cell A1, and we want that information to spread from A1 to E1, and that's the limitations because of the way we started recording that macro. Let me show you. If for example, I want a second title, let's insert another row, and I'll put this for 2003. If I simply make A2 my active cell, go up to Tools - Macro and now the Play button beside the word Macros indicates that I can run any of my macros. So I'm going to do that and here we are: TitleFormat, Run, but it didn't affect the cell that I wanted it to. It simply repeated the steps that I had done earlier, and it took the value in A1, which was already centered and merged and increased size and bolded, everything's been done for me. Now that macro will work very well on a new worksheet. If we simply open up a new worksheet, go in and create a title, let me just move away and back, there we go. Tools > Macro, Run your macro.
Notice now that it has a worksheet name attached to it because it's looking externally to the open worksheet to run this macro cause we saved it inside of that worksheet. Run that. Now that worked, but again our title only runs from cell A1 to E1. What if we need to extend it beyond that range or less than the number of columns that we previously recorded. Let me suggest that we do this. I'm just going to move to Sheet2 and here I'm going to play some information already in this cell. I'll call this TITLE2, and before I begin recording my macro, I'm going to select an area, a range. Now I can go up the Tools menu, down to Macro, Record New Macro, and we'll call this TITLE2, and I'll assign a shortcut key. Uh oh, that shortcut key is already in use. See how smart it is.
Well let me give it a new key then. Control+T. Now it's recording a macro. I have a selection already in place, so when I do my merge, bold and title size increase. I'll stop recording. It's working with an existing selection. Let me show you how that changes. Now I'll go to Sheet3, and I'll make TITLE3, and with TITLE3, I only want it to be centered and merged across three columns.
Tools, Macro, and I'm going to play back my macro called TITLE2. Notice that it worked within the selection that I currently set. By recording my macro after the selection was made, I changed the behavior so that I could go ahead and set up my title a little bit and then execute the macro to apply the formatting that I wanted. I can do exactly the same thing in row 4. Remember that the first macro was limited to the first row, because that's where I had recorded it. Now I can set in Subtitle and let me highlight more columns. Go up to Tools, Macro, Play Macro and again it worked beautifully. The selection that I have is the selection that it uses. Before we stop looking at this particular macro, let's look at it behind the scenes. If I go into the Tools > Macro menu, and play macros before I execute a macro, I can look at that macro by going into Edit. Here we're in the VBA Editor and although I don't want you to take away a lot of the coding behind the scene, I would like you to look at the English-like information that is here, to comprehend a little bit of what the macro is doing. For example, the range being specified here is part of the macro. So in the first macro we created our limitation was that it was always working between range A1 and E1. And then with the selection it began modifying our information exactly the way we asked it to, merging and bolding and centering it, making sure that our font size was increased. Let's take a look at the other macro that we created. I'm going to close the VBA Editor, go back to Tools > Macro, and look at TITLE2. We'll just edit that. Here you see that vital step at the beginning where we selected a range is missing. Now it's With Selection apply the following formatting. That's a big difference and it makes it far more versatile from a macro standpoint than our original recording. So keep that in mind, when you begin recording your macro is vital as to how useful that macro, or versatile that macro is going to be in the future. If you're simply doing the same thing over and over and over again, it's not so important, but if you're trying to make a more general application like this formatting macro, it may be key to record it at the right time.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91295 Viewers
80 Video lessons · 138225 Viewers
59 Video lessons · 57049 Viewers
52 Video lessons · 70707 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.