Cleaning up Recorded Code

Quick reference

• 00:05 We've seen a lot of theories so far, and now it's finally time to go and
• 00:08 get our hands dirty.
• 00:09 So, you'll notice that I've opened up the example file for this,
• 00:13 which is called microcleanup-start.
• 00:15 I've reset the prepaid schedule back to what it originally was in January,
• 00:19 but the code is here with our roll forward macro.
• 00:21 What I'd like to do is now go read that particular macro.
• 00:24 So, I'm gonna press Alt+F11,
• 00:25 and I'm gonna bring up the Visual Basic Dialog or Visual Basic Editor.
• 00:30 I'm gonna drill into Module1, I'll double click on it,
• 00:32 and that's gonna bring up the code that I'm actually working with.
• 00:35 And you'll notice that, in here,
• 00:37 it starts off, the very first line says Option Explicit.
• 00:41 Then it says in blue sub, and then underneath that role for after that role,
• 00:44 forward open close parenthesis.
• 00:45 So, like I said, everything starts with the sub to begin with, here's a name and
• 00:49 I'll open close parenthesis.
• 00:51 Then we see some lines that are parents by this single quote,
• 00:53 and you will notice that these are notes.
• 00:56 These are just pieces that we can actually leave for ourselves.
• 00:59 So the macro is just a standard name that goes into it.
• 01:03 It basically takes the name of your thing and then put macro or after, okay?
• 01:08 The next part says this macro a.
• 01:10 Where did it get that?
• 01:12 That was what we typed into the description, okay?
• 01:14 So that's good to know.
• 01:15 Once we type that in there though, I also went back and modified it to say,
• 01:19 hey, I'd like to record the Keyboard Shortcut.
• 01:22 That did not get rewritten back in here, okay?
• 01:24 So if I wanted to have that information in here I would need to put something in that
• 01:28 says, can run via keyboard shortcut of
• 01:34 CTRL+SHIFT+R, and I would have to leave myself a note there.
• 01:41 Okay, now other things, though, do I really need this line here?
• 01:45 Not really, so I'm just gonna delete it.
• 01:48 Do I really need this one here?
• 01:50 Well, let's get rid of those, and we'll put in just a single line there.
• 01:53 All right, so we can clean this up.
• 01:54 Now, the next piece that I want to look at is what
• 01:57 we actually end up having recorded here.
• 02:00 So you think back to what we did when we actually looked at this,
• 02:02 the very first thing that I did was I selected E6 through E10.
• 02:08 So if I go back and look at the Visual Basic Editor,
• 02:10 you can see that it says Range E6 to E10 .Select.
• 02:14 Well, that make sense and then I did I do?
• 02:18 I right-click on it and said Copy.
• 02:20 So it says Selection.Copy.
• 02:22 Well here's one of the interesting things about ABA
• 02:25 we don't need to select something in order to operate on it.
• 02:29 But we do need to talk through the object model chain So in that case, where it says
• 02:33 select and then it says Selection.Copy, if I delete that this is the same thing.
• 02:38 Range E6 E10 dot Copy.
• 02:41 So I don't even need to select it.
• 02:43 Then what do I do?
• 02:43 It said Range B6 dot Select and
• 02:46 then it said Selection dot Pastespecial Pastevalues blah blah blah.
• 02:50 Okay Well, I see the same thing here, select dot selection or
• 02:54 select and selection.
• 02:55 So, I'm gonna delete that, as well.
• 02:57 So, I now said, range b six pace special.
• 03:01 As it happens, you'll learn more about these a little bit later,
• 03:04 all of the rest of this is just setting defaults and I could look pace special up,
• 03:08 in the object browser to find what's required and what's not.
• 03:13 But in this case here all I need is the paste equals pixel values.
• 03:17 So I can delete it.
• 03:20 The next piece I'm looking at, it says select.
• 03:22 And then it says application.cutcopy mode equals false.
• 03:27 So I selected cells C6 to D10.
• 03:31 And then what do they do here?
• 03:32 This is actually the equivalent of pressing the escape key to stop
• 03:38 the marching ants from walking around the border of E6 to E10 that I copied Okay?
• 03:43 You know when you copy, you see that flashing dotted line going around there.
• 03:46 Application.CutCopyMode is the equivalent of pressing escape.
• 03:51 So, to be fair, I don't actually need to press escape in this case here,
• 03:55 because I just selected this area and then press the delete key.
• 03:58 So, that would override it.
• 03:59 So I could actually take this line out.
• 04:02 Which leaves me with the select and a selection that I can clean up.
• 04:09 Range A4.Select and it says, ActiveCell.FormulaR1C1.
• 04:12 Well you know what, the selection,
• 04:15 the active cell in this case are the same thing.
• 04:18 I only selected one cell so I can delete this as well.
• 04:22 Next one, A4.
• 04:23 I see a select and a selection.
• 04:25 I can delete those.
• 04:26 Range A3, I see a select and a selection, I can delete those.
• 04:31 Again like above I don't really need this stuff for the pay special.
• 04:35 So I can delete that too I see another application that copy mode in here so
• 04:40 I could delete this one leaving me with that select and
• 04:45 a selection here that I can clean up and bingo,
• 04:48 I have code this much shorter and much cleaner why this is so important to me is
• 04:53 because you know what That macro recorder, it actually records everything I did and
• 04:57 I don't need to actually interact with the spreadsheet to make this work.
• 05:00 How do I know?
• 05:01 Well, check this out.
• 05:02 What do we do before we actually go anywhere?
• 05:05 File, save as.
• 05:07 Let's go and put it right into this particular folder here.
• 05:10 I'm gonna save this guy as complete.
• 05:13 I want a backup, in case something goes wrong, right?
• 05:16 Save it first and then test it.
• 05:18 Lets hit roll forward, there we go, it worked.
• 05:22 February 28th, all the balances copied forward.
• 05:24 So, this is one of the things that you want to play around with,
• 05:28 with your code when your actually working with it.
• 05:30 What I would highly recommend that you actually end up
• 05:34 making a copy of your file first before you edit it.
• 05:38 If you don't know what a line does.
• 05:40 Let's say a cheese.
• 05:41 I don't know what this one does.
• 05:42 I'm just gonna put a single ' in it and then I'll go run it.
• 05:46 And if it didn't work, it lead or kick up an error or it will do something wrong.
• 05:50 I just throw the workbook lay without saving changes.
• 05:52 I come back in and say, okay, well, maybe I needed that line.
• 05:55 This is exactly the same way that we worked with the paste special.
• 05:58 You just save the file and then you knock off each one of those last components and
• 06:01 try it And see what happens.
• 06:03 Tinkering, it's how it works, okay.
• 06:05 So at this point code much shorter, we got rid of all the garbage that the macro
• 06:09 recorder records that we actually don't need.
• 06:11 We have a much more beautiful macro that we can actually read.
• 06:15 Range G6, we copied it, we then went to B6,
• 06:19 we pasted values, we then went and cleared the contents of C6 through C10.
• 06:23 So it's basically like it Works in reverse English it actually says where
• 06:27 we would say hey I'm gonna select these or I'm gonna copy, E6 to E10,
• 06:31 it says give me the range first and then tell me what you wanna do.
• 06:34 Okay? So that's how we read some code and
• 06:36 how we clean up the stuff that get's generated automatically for
• 06:39 us when we record macros

Lesson notes are only available for subscribers.