Recording Macros

Quick reference

• 00:00 Now we're gonna look at recording macros and this is cool stuff
• 00:05 because basically what this means is that you can now get Excel working for you.
• 00:12 Macros are best suited to scenarios where you need to do the same task over and
• 00:17 over again, and basically what we can do is we can record it once and
• 00:20 then play that macro back so it re-executes the same actions.
• 00:24 This can be super useful for us, because if we have tedious work that requires
• 00:29 human intervention to make sure certain steps are followed in certain orders, we
• 00:33 can use the macro to make sure that they are always executed exactly correctly,
• 00:38 with no doubt at all as to what's gonna happen.
• 00:40 The one thing you wanna be careful with here though,
• 00:43 is before you start, you really wanna make sure you know what you're doing, because
• 00:47 everything you record will be recorded in that macro when you start doing it.
• 00:52 Now, the first thing that I wanna show you though, before we can even get started,
• 00:55 is we need the developer tab showing.
• 00:57 And it's not showing, by default, in most cases.
• 01:00 So, to find it, we're gonna right-click on any tab at all,
• 01:02 and we're gonna say customize the ribbon.
• 01:05 And when you do that, you 'll find in here that there is a tab for developer.
• 01:09 We'll check that and say okay, and
• 01:12 it now gives us a new tab on our ribbon called developer.
• 01:15 And the most important thing here that we need right now
• 01:18 is the ability to record a macro.
• 01:20 So we're going to click record macro.
• 01:23 It asks us to give the macro a name.
• 01:25 I'm gonna call mine RollForward.
• 01:28 And we can even give it a shortcut key.
• 01:31 I would highly suggest that you test the shortcut key before you use it,
• 01:34 cause you might learn some new ones here.
• 01:35 I'm gonna go with Control, shift, R for RollForward.
• 01:39 And it offers to store it in this workbook, or the personal macro workbook,
• 01:44 or a new workbook.
• 01:45 This workbook will store the macro in this file, so when I email it to somebody,
• 01:49 the macro will exist.
• 01:50 If I wanna use the same macro in a bunch of workbooks that I own and
• 01:54 I don't have to worry about sending them to anyone else,
• 01:57 I could store it in the personal macro work book.
• 01:59 For this case right now though,
• 02:00 we're going to go with this work book and we'll say okay.
• 02:02 And at this point, we need to recognize that down in the bottom left hand corner
• 02:07 we have a square that tells us the macros currently recording.
• 02:10 It also shows us a square on the developer tab.
• 02:13 What this means is that any action I do now will be written into code.
• 02:18 So, what I'm gonna do is I'm going to select cells E4 through E15.
• 02:23 I'm going to right-click on them.
• 02:27 I'm going to copy them I'm gonna right click on cell B4,
• 02:31 and I'm going to paste values.
• 02:34 And then I'm going to select the blue cells here from C4 to D15, and
• 02:40 press delete.
• 02:41 And I've now taken my closing balance, pasted it to my opening balance, and
• 02:44 cleared out my schedule so that I can use it next month.
• 02:47 At this point I'm done, I'm gonna click stop recording.
• 02:53 Now the question is how do I actually use this macro?
• 02:56 How do I know it's gonna work?
• 02:57 Well if go on through a thousand dollars in here,
• 03:00 I'd like to actually see this macro to do what it needs to do.
• 03:03 Remember I set up my keyboard shortcut with control, shift, R?
• 03:07 So I'm gonna press that right now, and
• 03:09 you'll see that it has now updated this number to 1,500.
• 03:12 If I were to go and put my payment in here of 1,000.
• 03:15 I'm gonna press Control, shift, R.
• 03:17 And what we should see is that this area will clear out.
• 03:20 And this 550 that's showing in E7 or sorry, E6 rather, will show up in B6.
• 03:26 Here we go, control, shift, R.
• 03:28 But what if you forget your keyboard shortcut?
• 03:31 Well, that's okay.
• 03:32 We can actually go back to the macros box here and
• 03:36 we can see all the macros that are in the file.
• 03:38 So, we could click run and it will run the macro as well.
• 03:43 There's another way to make this a little bit more obvious,
• 03:45 because keyboard shortcuts are hard to remember, and
• 03:47 you may not want to actually have to teach someone to do this.
• 03:50 So what we'll do is on the developer tab we'll go to insert, and
• 03:54 we'll take the very top left form control.
• 03:56 So I'm going to click on button.
• 03:57 I'm going to left click and drag on the worksheet, and
• 04:01 the only purpose of this button is to assign it to a macro.
• 04:05 So we're going to select our roll forward macro, and we'll say okay.
• 04:10 Now, notice the selection handles are around the outside of the button,
• 04:13 that mean it's in edit mode.
• 04:14 So we can actually select, or
• 04:16 in this side here, delete this, and type in RollForward.
• 04:22 Now I can click outside to make sure that these selection handles go away.
• 04:27 Let's go put some numbers in.
• 04:32 And why don't we put something on this one here too.
• 04:34 We'll clear this one out completely.
• 04:38 That's customer number 788, it will go to zero.
• 04:41 RollForward, and there we go.
• 04:44 And we can keep clicking that as many times as we want.
• 04:46 We've got a beautiful macro, all ready to work with.

Lesson notes are only available for subscribers.