Recording Macros

Quick reference

• 00:05 We're now gonna look at macros.
• 00:07 And this is exciting stuff because this is where we can actually start to get Excel
• 00:10 to work for us.
• 00:12 Now, I have a job here that I do every month.
• 00:15 Every month, I grab my closing balance, I copy it, I paste this
• 00:18 values over my opening balance, and then I delete this blue section so
• 00:22 that I can put in the new charges and payments to carry these things forward.
• 00:26 I'd really like to find a way to make that easier so that I don't have to right-click
• 00:30 and copy and right-click and paste, and stuff like that.
• 00:32 And this is where a macro is helpful,
• 00:34 because we can script these actions to be replayed upon demand.
• 00:38 But to do it, we're gonna need to expose the ability to actually create macros.
• 00:43 And that's done through the developer tab, which we don't have showing in our tabs.
• 00:47 So to get that we're gonna right click on any tab and go to Customize the Ribbon.
• 00:53 And in here, you can check the box next to Developer.
• 00:56 And now we can say OK.
• 00:58 And you'll have a brand-new tab show up in your Excel ribbon called Developer,
• 01:02 which happens to have the command were looking for, a Record Macro.
• 01:07 There's all kinds of interesting pieces in here, but
• 01:09 this is the one we want right now.
• 01:11 So I'm gonna click on Record Macro and I'm gonna be presented with some options.
• 01:15 First, it's gonna ask me what would I like to call this macro.
• 01:18 I'm gonna go with Rollforward, and you cannot use spaces in this area.
• 01:24 I'd like to trigger it from a keyboard shortcut, Ctrl+Shift+R.
• 01:29 Now if you're gonna start recording macros, I highly recommend that you test
• 01:33 the keyboard shortcut that you wanna use before you actually going on and
• 01:36 record a macro.
• 01:38 Not only is this a great way to learn new keyboard shortcuts, but it'll prevent you
• 01:41 from actually overwriting certain things like, I don't know, Ctrl+P, which prints.
• 01:47 Now if you never use the command, maybe it's not a big deal.
• 01:50 But if you do and you send it to somebody else and
• 01:51 you overwrite their print command, they're not going to be happy with you.
• 01:55 You get a choice of where to store the macro,
• 01:58 in the personal macro workbook or a new workbook or this workbook.
• 02:02 If I want to save this and send it to someone else,
• 02:05 it's important that the code be in this workbook.
• 02:08 For personal macro workbook that would actually be from any workbook that I open
• 02:12 on my computer, we're gonna choose this workbook.
• 02:15 And then we'll say OK.
• 02:17 And at this point there's a couple things happened.
• 02:21 The icon on the developer job changes to a little red box that says Stop Recording.
• 02:26 Down in the bottom on the task bar,
• 02:28 you can also see that we have a little square box.
• 02:30 This is where we could also stop recording the macros as well.
• 02:33 And in this point, we have to be careful because any action that we do
• 02:37 will get written into code and when we trigger the macro, will run again.
• 02:41 So you gotta make sure that you practice this a few times
• 02:44 before you do the recording itself.
• 02:47 So here's what we're gonna do.
• 02:48 I'm gonna grab my closing balance.
• 02:50 I'm gonna press Ctrl+C to copy it.
• 02:53 I'm gonna come over to B6, right click, and I'm gonna paste special values.
• 02:59 And then I'm gonna select my section of data here and press the Delete key.
• 03:05 And that's all I want to do to roll my schedule forward.
• 03:08 So now, I'm gonna go and say Stop Recording.
• 03:13 Now the key thing is, I wanna check and see if it's actually worked.
• 03:16 So let's say that Client 780 bought another $500 and
• 03:22 Client 781 paid $500 off.
• 03:26 At this point, I'd like to know, for next month, if I roll this forward,
• 03:30 will my opening balance for next month become 1,500 and 0.
• 03:34 So let's press, Ctrl+Shift+R.
• 03:38 And just like that, indeed it does.
• 03:40 So I can say now, let's say that this guy here paid this one off, 1,500 and
• 03:45 he bought another 250, Ctrl+Shift+R, and away we go.
• 03:52 It looks pretty nice.
• 03:53 So that's not bad.
• 03:54 We've actually got this scripted so that we can actually replay these commands.
• 03:59 But what if you forget the keyboard shortcut?
• 04:02 Well, you can always go to macros.
• 04:05 Find your macro, and choose options to show what it is.
• 04:10 But there's a better way.
• 04:12 If we cancel that out, we can come back to the developer tab and
• 04:16 go to the Insert box, and the top left control under form controls is a button.
• 04:22 And when we go and left click and
• 04:23 drag, it asks us what would we like to link it to for a macro?
• 04:28 This is the only purpose of a button.
• 04:29 So I'm gonna say roll forward, we'll say OK.
• 04:33 And you'll notice I have little circles around so I can come in here now and
• 04:37 I can change this and I can even go and put this in with a space of roll forward.
• 04:42 And now we'll go and click outside the grid and the buttons will go away.
• 04:44 Now if I need to edit this, I would right click and say Edit Text.
• 04:49 Outside of that I'm gonna click in the grid and now what we'll notice if I go and
• 04:53 say, let's go and add a new charge here with $1,000.
• 04:56 And now I click roll forward, and just like that it works.
• 05:00 And if I go and say, let's knock our payment down for $1,000, for
• 05:03 Closing Balance, it's gonna go from 1,000 to 0.
• 05:06 And boom, there it is.
• 05:07 It's down at 0, and everything is working beautifully with a nice button that my
• 05:11 users will never forget to click.

Lesson notes are only available for subscribers.