Locked lesson.
About this lesson
Learn to record basic Excel macros to automate your actions (instead of doing them manually).
Exercise files
Download this lesson’s related exercise files.
Recording Macros.xlsx12 KB Recording Macros - Completed.xlsm
23.3 KB
Quick reference
Topic
Macros.
Description
Recording basic Excel macros to automate actions.
Where/when to use the technique
Excel contains a powerful language called VBA that can be used to script actions to happen. Recording macros is the first step in this process, and is very useful for automating repetitive steps.
Instructions
Expose the Developer tab (if it is not on your ribbon)
- Right click any command on the ribbon and choose “Customize Ribbon”
- Click the box next to Developer tab in the list on the right
- Return to Excel and activate the Developer tab
Before recording
- Practice the steps you will go through, as the macro recorder will record your mistakes!
- Be aware that the macro will do EXACTLY the same thing EVERY time it runs (this can be modified, but it involves learning to program)
Recording a Continuity Schedule Roll Forward macro
- On the developer tab, click “Record Macro”
- Call the macro “RollForward” and type a capital R in the Shortcut box
- Choose to place the macro in ThisWorkbook
- Select E4:E15 right click, and choose Copy
- Select cell B4, right click, and choose PasteSpecial --> Values
- Select C4:D15 and press Delete
- On the Developer tab click “Stop Recording”
Running the macro
- Enter some data in the data cells and check the balances
- Press Option+Command+R to run the keyboard shortcut
- Press Option+F8 to run the macro from the macro dialog
- Go to the Developer tab and choose to Insert a From Control Button on the worksheet (it will prompt you to connect a macro to it.) You can then click the button to run the macro.
- 00:03 In this video, we're going to look at recording macros.
- 00:07 And this is kind of exciting because this is where we start to
- 00:10 introduce a little bit of automation and making Excel work for
- 00:13 us rather than having to tell it what to do all the time, which is really cool.
- 00:17 The first problem that we have though when we want to get into this is that we don't
- 00:21 have a necessary tab on our ribbon called the developer tab.
- 00:24 So, to fix that we're gonna go to Excel and Preferences.
- 00:29 We're gonna go to View and down near the bottom we're gonna click Developer tab.
- 00:34 And then we'll just close the View Options here.
- 00:36 So we now have a new tab called Developer, which is great, and
- 00:40 on here is a really important button called Record Macro.
- 00:43 So what I'm gonna do is,
- 00:45 I'm gonna record a Macro right now that takes the closing balance, so my $8,900,
- 00:50 it's gonna copy these values, paste them as values over the opening balance.
- 00:55 So we're gonna replace the opening balance.
- 00:57 And then we're gonna clear out the blue cells for entry here.
- 01:01 And this will allow me, every month, to put in the new charges and payments and
- 01:04 carry the balance for it all the time so I can see what's happening with it as I go.
- 01:08 So, here's what we're going to do.
- 01:09 We're going to click record macro,
- 01:11 and we're going to see that we have the option to give our macro a name.
- 01:15 I'm going to call this one roll forward,
- 01:20 because that's what I'm doing is rolling forward this particular spreadsheet.
- 01:23 Now, we have the option to store this in this workbook, or
- 01:27 the personal macro workbook, or a new workbook.
- 01:31 The biggest ones that you'll ever use are this workbook and
- 01:34 the personal macro workbook.
- 01:35 The difference being that macros recorded in this workbook go with the file.
- 01:40 So no matter who you email them to, they'll go along with that file and
- 01:43 can be used by the end user, which is important.
- 01:46 The personal macro workbook is where you would record macros that you want to use
- 01:51 yourself but you may want to use them on several workbooks.
- 01:54 But the challenge here is if you store these files,
- 01:56 or the macro in the personal macro workbook, if you email this workbook to
- 02:00 some one else, they won't be able to use your macro cause they won't have it.
- 02:02 So, we're gonna keep this in this workbook.
- 02:06 You also have the option to put in a shortcut key to run your macro, so I'm
- 02:10 gonna go with a capital R here and you'll notice that means option command shift R.
- 02:15 Okay, that's how you're gonna actually kick the macro off, is one method.
- 02:19 And you can fill out the description for your macro if you want, don't need to.
- 02:22 I'm gonna say okay right now.
- 02:25 So now you should see that the Start Recording button has
- 02:29 turned into a Stop Recording button.
- 02:31 The only other indicator we have is this blue square on the bottom.
- 02:34 And what this means right now is that Excel is recording every action we do,
- 02:38 every Success, every mistake we make.
- 02:40 So it is really important before you guys should go to record a macro you
- 02:44 practice several times because you do not really want to record mistakes here.
- 02:48 So now we are going to carefully replicate the steps that I want to go through.
- 02:51 I am going to select my closing balance.
- 02:53 I am going to right click and I am going to say copy.
- 02:57 I am going to go over to select my opening balance.
- 03:01 Right-click and say Paste Special Values because the items over here are formulas.
- 03:09 So we've locked down those values, and now I'll select my data range and
- 03:13 press Delete, and then I'm gonna stop the recording of the macro.
- 03:18 So that's all it's doing is copying, pasting as values, and
- 03:21 clearing out my data.
- 03:23 Now I'd like to give it a test, so we'll go and
- 03:25 we'll just drop some random values, in to some of these cells.
- 03:30 And we'll go and throw in some credits on the other side as well.
- 03:34 And let's see what we get here.
- 03:37 There we go, perfect.
- 03:40 So now we want to run the macro.
- 03:42 I said my shortcut was Shift+Option+Cmd+R and
- 03:49 you'll notice that it's gone through and it's done exactly that.
- 03:52 There's my 78, 98 that's been copied over.
- 03:55 Let's go in and put some new charges in here.
- 03:57 I'll just put $3,000 in this one and just bring this up.
- 03:59 So we should end up at $10,898.
- 04:00 I'll give you a different way to run the macro
- 04:03 instead of just the keyboard shortcut.
- 04:05 I could click on the macros button over here and it says,
- 04:09 well here's the roll forward macro, would you like to run it?
- 04:12 You'll also notice I have options.
- 04:14 I could change or remember what my keyboard shortcut is,
- 04:18 but I'm gonna just click run.
- 04:21 You'll notice that I now have $10,898 over here.
- 04:23 That's good.
- 04:24 So what if I want to clear one of these things out?
- 04:27 We'll put $1778 in here.
- 04:29 Next month comes along, I want to run this, I could go with Option+F8 and
- 04:34 that'll bring up the Macro dialog box as well.
- 04:37 So those are methods to run our macros and get them working.
- 04:44 There's one more method we could do, and
- 04:46 that is to create a button on our worksheet.
- 04:49 If we click this button, and then we left-click and
- 04:52 drag, the only purpose of one of these buttons is to actually run a macro.
- 04:57 So it will immediately pop up as soon as we let go, and say,
- 04:59 what button would you like to run?
- 05:01 We'll say Roll forward.
- 05:03 Okay, no problem.
- 05:05 And our button shows up on the worksheet.
- 05:07 I can then edit the text in this.
- 05:10 So we'll just go and type in.
- 05:13 Oops, it's kind of a little ugly to work with here.
- 05:15 Roll forward and we'll go all the way to the end and
- 05:20 just delete the remaining text here.
- 05:22 So I've now got a nice little button.
- 05:24 I click outside.
- 05:25 Those little handles go away.
- 05:26 If I right click it, it goes back with these little handles so I can edit it.
- 05:30 If I go and left click those handles go away.
- 05:32 And now you'll notice that when I click that
- 05:35 you can see the macro runs through its steps.
- 05:37 And it's working.
- 05:37 So one final word of note as well.
- 05:40 When you save these files it's really important that you save this as.
- 05:44 An XLSM file.
- 05:46 Not as an XLSX file because XLSX files don't have macros in them.
- 05:53 So you need to change your file format type to use XLSM if you
- 05:58 want to actually preserve your macros, otherwise Excel will just eat them and
- 06:02 they won't be there when you come back next time.
Lesson notes are only available for subscribers.