Recording Macros

Quick reference

• 00:04 In this video we're going to take a very brief dive into recording and
• 00:08 playing back macros.
• 00:10 Now if you've never worked with the macro in any program before,
• 00:14 basically the idea here is that we can record actions.
• 00:17 And play them back in the future.
• 00:18 This is great for tedious and repetitive tasks.
• 00:21 The more you do it, the better the payback is.
• 00:24 So let me give you a quick example here.
• 00:27 This worksheet is a continuity schedule.
• 00:29 So every month I take these actions, I go and select the data, right click and copy.
• 00:35 I come over to the opening balance, right click and paste values.
• 00:39 And then I go and I actually select all the blue cells, and
• 00:42 delete the values that are in them.
• 00:44 And this gets my schedule all set up and ready for next month.
• 00:47 Now I'm just going to press Ctrl+Z a couple of times,
• 00:50 to roll all of this back here.
• 00:51 Because I want to make sure that everything is working nicely.
• 00:54 So there we go, we've got our original data.
• 00:56 I want to record a macro to do this,
• 00:58 because it's just going to make my life easier.
• 01:01 So, here's what I'm going to do.
• 01:02 The first thing is, I'm going to go and activate the developer tab.
• 01:05 Because that's where the record macro button is found.
• 01:08 And yet you'll notice that I don't have a developer tab showing.
• 01:12 To get it, we're going to go right click on any of the tabs and
• 01:16 choose Customize Ribbon.
• 01:17 And you'll find developer down here, and it's probably unchecked,
• 01:21 because it's unchecked by default, which is kind of a shame.
• 01:24 We're going to say OK, and that adds the new developer tab to our ribbon where
• 01:29 you'll find the Record Macro button.
• 01:31 Now, it is critically important, before you go and
• 01:35 click this button that you have practiced exactly what you're going to do.
• 01:41 Every key stroke, every mouse click, every selection.
• 01:45 You need to have it all dialed in, so
• 01:48 that you can record it without making any mistakes whatsoever.
• 01:52 Because everything you do after you click OK when recording a macro,
• 01:56 will get recorded in code.
• 01:58 So if you make a mistake and
• 02:00 then press Ctrl+Z to undo while both those actions get recorded in your macro-.
• 02:05 It'll make the mistake and undo it every time you replay that macro.
• 02:09 So you want to be really, really careful with this.
• 02:11 And always remember, you can just throw your file away without saving changes.
• 02:14 So you can practice as many times as you want.
• 02:16 Here's what I'm going to do, I'm going to click Record Macro.
• 02:19 And I'm going to give this macro a name.
• 02:21 I'm going to call it.
• 02:24 All right, there we go.
• 02:25 No spaces, those aren't allowed and
• 02:27 you also cannot start your macro name with an underscore.
• 02:30 I'm going to give it a keyboard shortcut to run it,
• 02:33 we're going to go with Ctrl+Shift+R, okay?
• 02:36 You should always check to see that you're not going to overwrite something.
• 02:39 Don't use Ctrl+P because that will actually take over your print, okay?
• 02:42 We don't want that.
• 02:43 And I'm going to choose to store the macro in this workbook.
• 02:47 And that way anybody that uses it will have it accessible to them.
• 02:50 Optionally I can fill in a description here, like this macro will roll for
• 02:55 the workbook.
• 02:56 But honestly, hardly anybody ever actually reads those.
• 02:59 But it is a good idea to actually put one in place.
• 03:02 For me right now I'm just going to skip it, and I'm going to say OK.
• 03:07 Now at this point what happens, is we're actually in recording mode.
• 03:11 And we can see that because we have a stop recording button up the top.
• 03:15 Anything I do, any click, or
• 03:17 anything I type at this point in time, is going to get recorded.
• 03:20 So here's what I'm going to do, is I'm going to go and select my closing balance.
• 03:25 I'm going to go and press Ctrl+C to copy it.
• 03:28 I'm going to right click on the first cell of my opening balance and paste values.
• 03:34 I'm then going to go and select my new charges and payments.
• 03:37 And I'm going to press my Delete key to make them go away.
• 03:41 And at that point I'm going to come back to the top.
• 03:44 And I'm going to choose stop recording,
• 03:46 because that's all the macro needs to do, okay.
• 03:49 Like I say, you always want to practice that to begin with and
• 03:52 make sure that everything works.
• 03:53 Now, the next step is to test and see if it works.
• 03:56 So let me go and throw in some new charges for this customer here and this one here.
• 04:01 And we'll get some payments from client 780 and we'll get rid of that.
• 04:06 So what we should see when we roll this forward is that client 780 is going to
• 04:09 have a zero balance.
• 04:10 And my first two are going to be 1,500-- and 1,550.
• 04:14 So if I press Ctrl+Shitf+R, you'll notice that it flashes through and
• 04:17 that's exactly what's happened.
• 04:19 It's copied from here, pasted its values here and cleared this out.
• 04:23 Now, that's cool, but who's going to remember to press Ctrl+Shift+R,
• 04:27 if I send it to them?
• 04:28 Probably nobody.
• 04:29 So what I'm going to do, is I'm going to come up to the developer tab,
• 04:33 I'm going to find the Insert button.
• 04:35 And we're going to find the very first form control right here,
• 04:38 called the button.
• 04:39 I'm going to click on it.
• 04:40 And I'm going to go and drop it right here, left click and drag.
• 04:44 And that, now pops up something that says,
• 04:46 please tell me what macro you'd like to use.
• 04:49 And I'm going to choose the roll forward macro that we have here, and say OK.
• 04:54 Now it gives me this button.
• 04:57 I'm going to select the text in here.
• 04:59 And unfortunately, it's really hard to select the text,
• 05:01 I'm going to select by left clicking there.
• 05:02 I'm going to hold down my Shift key and press End in order to get all of the text.
• 05:06 And now I'm just going to go and rename it to roll forward.
• 05:09 And in this case, I can use a space.
• 05:11 You'll notice it's in selection mode because it's got little dots
• 05:15 surrounding it.
• 05:15 I'm going to click right down here to get out of selection mode.
• 05:19 And now what I'm going to do, is I'm going to issue a $1,500 payment.
• 05:23 $1,550 payment here to blank those ones out and add $1,000 in new charges.
• 05:29 And I'm going to roll forward.
• 05:31 And you'll notice that clicking the button makes it work.
• 05:34 You can see that by the quick flash that's there.
• 05:37 That is the successful recording of a macro.
• 05:40 And here's the thing though, when you save this workbook,
• 05:44 you will need to save it as an XLSM file, Excel macro enabled.
• 05:49 And anybody that opens it up,
• 05:51 will be prompted to know that there are macros in it.
• 05:54 This is code, and
• 05:55 it's important to realize that it actually gets security warnings.
• 05:59 Having said that, you can spend a lot of time actually writing and
• 06:03 doing incredibly advanced things with code.
• 06:05 But for the scope of this course, this should get you started and
• 06:09 recording your basic macros to help automate some tasks.

Lesson notes are only available for subscribers.