Locked lesson.
About this lesson
Setting up a master macro allowing you to call other macros from a single source.
Exercise files
Download this lesson’s related exercise files.
Calling other macros.xlsm27.2 KB Calling other macros - Completed.xlsm
29.3 KB
Quick reference
Calling other macros
Lining multiple subroutines together
When to use
To call one macro from within another subroutine
Instructions
It is a best practice to create a "Master" routine that calls smaller sub-macros instead of writing one long macro
Benefits of calling other macros
- Allows for easier debugging
- Allows you to re-use routines should you need them more than once
Syntax for calling another macro
Sub MyMasterMacroName
Call Macro1Name
Call Macro2Name
End Sub
Setting Macros to Private
- Optionally, you can insert the word Private before Sub when creating a Macro
- Private Subs do not show up in Excel's Macro dialog
- Private Subs can still be called from other macros in the same code module
Hints & tips
- The Call keyword is optional, but recommended as it makes your code easier to read later
- 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.