Calling Other Macros

Quick reference

• 00:05 The last coding construct that I wanna show you is how to call one macro from
• 00:09 another.
• 00:10 This is a really important concept because we don't ever want to write
• 00:15 blocks of code that are thousands of lines long.
• 00:18 What we'd really rather do is actually set up smaller routines, and
• 00:22 then call one from another.
• 00:24 This allows us a much easier time when we're debugging things.
• 00:27 Cuz we can basically check off a big block of code and say, yeah, this ones done,
• 00:30 I'm not gonna focus on it.
• 00:31 We can run through all of the stuff up to that point, and
• 00:34 just really into the places that are actually issues that we need to deal with.
• 00:38 So, it's a highly, highly important technique that you need to know in order
• 00:42 to structure your code properly.
• 00:44 The ideal example that we have to work on is our Prepaid Expense module that we've
• 00:48 been building up here.
• 00:49 So, what I'm gonna do is I'm gonna jump over into the Visual Basic Editor.
• 00:53 And we'll just take a look and make sure that everything looks the way that
• 00:56 it does, or the way that we expect it to.
• 00:58 So everything's the same as what it was.
• 01:00 Here's the save file routine.
• 01:01 You can see it's deleting the old file path and saving the new one.
• 01:04 So that's all good.
• 01:05 And further up, we have the RollForward module as well that's
• 01:09 doing all the stuff that we want it to do.
• 01:12 What I'd like to do right now is I'd like to generate a routine that actually calls
• 01:16 the RollForward module, and then calls the save.
• 01:19 Because currently what we have is we have it where we would expect the user to run
• 01:23 the RollForward, and then we would expect the user to run the saving macron.
• 01:27 We're asking them to do two things.
• 01:30 Well, sadly, there's a good chance that they'll get one of them wrong.
• 01:32 So, we don't really wanna do that.
• 01:33 We don't want them to save before they roll it forward.
• 01:36 We want to make sure that it does get rolled forward before it gets saved,
• 01:39 like all these different kinds of things that happen that can go sideways on us.
• 01:42 So, what we're gonna do is we're gonna create a new routine at the very top here.
• 01:47 And this one, we're gonna go and we're gonna call this one Sub,
• 01:52 let's call it Master.
• 01:53 And the purpose of this particular macro is
• 01:57 that this will ensure that the macros are running the right order.
• 02:05 All right, now in order to actually call one macro from another,
• 02:09 it's actually really, really easy to do.
• 02:12 What we're gonna do is we're gonna type in Call RollForward.
• 02:17 And you'll notice that if I hit Ctrl+Spacebar when I'm partway through,
• 02:19 I can just hit Enter on that.
• 02:21 So, the next one that I had was call, and I believe it was called save, well,
• 02:26 actually, let's just hit Ctrl+Enter on this.
• 02:27 You'll notice we get a nice dropdown list that shows all of the available methods
• 02:31 that are in this workbook and properties.
• 02:34 SavePicture, SaveSetting are built-in, but SaveFile is one that I created.
• 02:38 So, I can now hit Tab, and that will now actually nicely roll in there, so
• 02:43 I've got Call SaveFile, and we'll go down again.
• 02:46 All right, now, the key thing around this.
• 02:50 The Call keyword is actually optional, you can omit it.
• 02:53 You don't really need to have it, you could have just type RollForward and
• 02:56 SaveFile, and it would work just fine.
• 02:58 Personally, I'm a big advocate of having these things in here.
• 03:02 I think that explicit coding where you're actually putting these things in makes it
• 03:06 much easier to read later, and makes it much easier to debug later.
• 03:09 If you've just got a word that's thrown in there,
• 03:10 especially if it's mashed in with 40 lines around it, it's really hard to see.
• 03:15 The Call gives that blue keyword, it actually opens up a little bit of space in
• 03:18 there as well for some whitespace, just makes it easier to read.
• 03:21 So I highly suggest you actually use that.
• 03:23 Now, the next part that we have to be concerned about here is
• 03:28 what about this little piece right here.
• 03:32 I'm not really sure that I want somebody running the RollForward macro
• 03:36 without also doing the same.
• 03:38 So, unfortunately, I can't just cut this out, and paste it up here, and
• 03:43 make this work, that's not gonna do it for me.
• 03:45 So, what I'm gonna do is I'm gonna go and say, all right, well, this is cool.
• 03:49 Let's go and drop back to Excel for second.
• 03:51 Let's go into Alt+F8, and
• 03:53 you'll noticed now that we actually have all three of our individual macros.
• 03:58 So, let's go to RollForward, we go to Options.
• 04:01 And it looks like I've been tweaking some stuff, so
• 04:03 it looks like that keyboard shortcut has been lost, which is not a bad thing.
• 04:07 So we're going to Master, and
• 04:08 that actually happens anytime you modify the macro, by the way.
• 04:11 We'll go into Options for Master, and
• 04:13 we'll put in the Ctrl+Shift+R here, there we go, I'll say OK.
• 04:18 Now, this is fine, we could press Ctrl+Shift+R, and that would work.
• 04:22 Remember our RollForward box.
• 04:24 I'm gonna right-click on this too, and say Assign Macro because he is also pointed to
• 04:29 the RollForward macro, I'll need to change that to Master.
• 04:33 There's one more small problem in this workbook though, and
• 04:35 that is when I press Alt+F8.
• 04:37 If I don't want people running RollForward or
• 04:40 SaveFile, these are problems because somebody can see them here.
• 04:43 I really only want them to be able to see Master.
• 04:45 So to do that, I'm gonna quickly jump back over into the Visual Basic Editor.
• 04:50 And this where we can actually go and add one of those keywords that I talked
• 04:54 about beforehand at the very beginning of the course.
• 04:59 Private, when we say private on RollForward, and
• 05:03 we say Private on Sub file,
• 05:07 these can still be run from buttons, by the way, if they were hooked up to that.
• 05:11 But the big difference is that if I go back now, and
• 05:15 press Alt+F8, notice that they don't show up.
• 05:19 They'll still run, as a matter of fact, if I go in here now, I'll save the workbook.
• 05:26 And I'm gonna hit Roll Forward.
• 05:27 And you'll notice that everything rolls forward nicely.
• 05:30 As a matter of fact, if I go and
• 05:31 look out at my folder full of files, you'll see that Prepaid Expenses is there.
• 05:35 So everything is still working, but the beauty is that I've now actually
• 05:39 restricted the user from seeing what I don't want them to see in this area.
• 05:43 I've got one Master macro that's calling everything for me which is great.
• 05:47 This is really useful as well, by the way,
• 05:49 because we can set break points on our Master files.
• 05:53 We can run still, it'll run through, it's done everything in RollForward.
• 05:57 Now, I can step into the SaveFile macro, and
• 05:59 just work through where I need to be on these things, everything works great.
• 06:02 So it's gonna make your debugging life a lot easier to encapsulate your
• 06:05 code that way.
• 06:06 And I highly encourage using Private or putting a Private keyword in
• 06:11 on the modules that you don't want somebody to run from the macro dialogue.

Lesson notes are only available for subscribers.