Setting up the Macro Environment

Quick reference

• 00:05 In this module, we're going to look at how to set up the environment so
• 00:08 that you can actually take advantage of all the programming tools.
• 00:11 The first thing I'm going to call your attention to is that,
• 00:13 I'm using Excel 2016 to lead this, but once we actually get into programming
• 00:18 itself, It works the same in every version of Office from Office 5,
• 00:23 way back in like pre 1995 all the way through to current here.
• 00:26 There hasn't been a lot of changes inside the Visual Basic Editor although,
• 00:29 there is a change as to how you get there.
• 00:32 In Office 2007, you'll have a big Office logo on the top left hand corner
• 00:35 that if you click it, it would take you into your file menu.
• 00:38 And when you go into your file menu and hit options in Excel 97,
• 00:42 you'll have a popular tab up at the top here and there'll be a check box on that
• 00:46 to allow you to show the developer tab and the ribbon.
• 00:49 You need to check that box.
• 00:51 For Excel 2010 and higher we actually get there a different way.
• 00:54 What we can do is right click on any one of our ribbon tabs here.
• 00:58 We can say customize the ribbon.
• 01:01 And at this point we're gonna check the box next to developer right here and say okay,
• 01:06 and that will give us a new developer tab so no matter which way we got in here,
• 01:11 you'll see that we actually have a bunch of different buttons available to us,
• 01:15 one of them is called Visual Basic and we're gonna click on that now.
• 01:19 This is your very first look at the Visual Basic editor.
• 01:22 We're gonna spend a lot more time in here.
• 01:23 But for right now,
• 01:24 what I'd like you to do is go to Tools and I would like you to go to Options.
• 01:29 There's a box that I'd like you to check called Require Variable Declaration.
• 01:33 We're just gonna check that guy.
• 01:34 I think this box should be set by default, but
• 01:36 it isn't, so we won't worry about it too much right now.
• 01:39 We'll cover this off,
• 01:40 somewhere in Module Five, we'll talk about what this actually does.
• 01:43 But I'd like to get it set for you before you actually get started here.
• 01:47 We'll now say, OK, and we're gonna close the visual basic editor window.
• 01:52 Okay? This is one of the first things that I do
• 01:54 when I start off with a new version of Excel is go in and
• 01:56 check that check box right away to make sure it's all set up correctly.
• 02:00 Now, one more thing I wanna talk about.
• 02:03 We're gonna go to the file menu or in Office 2007,
• 02:05 you'd go back to the little office logo and we're gonna go to options.
• 02:11 And we're going to go to trust center.
• 02:14 And on the right hand side, we're going to go to trust center settings.
• 02:19 And in here what you'll notice is that we have a few different things.
• 02:22 We've got some macro settings.
• 02:25 The default is to disable all macros with notification.
• 02:28 And this is good, because when you open a file that has a macro on it, it'll pop up
• 02:31 a little message that says, hey, there's a macro in here, would you like to run it?
• 02:34 And if you don't click that box, it won't let it do anything for you, which is fine.
• 02:38 You also have the option to disable all macros without notification,
• 02:41 which just basically blocks all macros, we don't wanna do that.
• 02:44 If you have digital certificates the concept outside of what we're going to
• 02:47 deal with today, you could actually automatically trust them to run here
• 02:51 the last option in here is called enable macros never check this box this is
• 02:55 a really bad idea this is akin to the old excel-2003 method of security, and
• 03:00 unfortunately, it didn't really work.
• 03:02 So it's something that we don't wanna do.
• 03:04 The other thing that we don't wanna do, unless absolutely necessary,
• 03:08 is trust access to the Visual Basic app or project object model.
• 03:11 If you check this box,
• 03:13 it gives other applications the ability to use code to write code.
• 03:18 And that's a bit of a problem, because what'll end up happening is
• 03:21 you can actually have somebody that writes a really bad macro and if you happen to
• 03:25 open it up and have this check box checked, it can actually insert new codes
• 03:29 in to your modules and actually start to propagate itself and do dangerous things.
• 03:34 There are very few instances where this box needs to be enabled.
• 03:38 But if it needs to be enabled,
• 03:40 it actually affects the entire application not just the specific workbook.
• 03:43 So you want to avoid this at all costs because you can actually have some really
• 03:47 bad things happen with propagating code, if you run into a nefarious Programmer.
• 03:53 Now one of the things that happens with this little box here,
• 03:56 disable all macros with notification.
• 03:58 This is the default, this is the way I like to leave mine set up.
• 04:00 But what its gonna do is every time I open a file that has a macro, its gonna prompt
• 04:04 me and its gonna say hey, you have a macro in this file, would you like to run it?
• 04:08 And I'm gonna get a little tired of dealing with that.
• 04:09 Because I run with lots of things.
• 04:12 So what I'm gonna do is I'm gonna click on Trusted Locations.
• 04:16 And this is a paradigm that was added in Excel 2007.
• 04:19 And it allows us to go through and say,
• 04:22 Hey I'd like to set up a trusted location on my computer.
• 04:25 So that everything that's stored in that folder will run with full Permissions,
• 04:29 except for the object model.
• 04:31 It won't run with that.
• 04:33 But it'll allow us to actually avoid getting prompted with macros all the time.
• 04:38 So what you can do here is you can say, you know what?
• 04:40 I'm gonna add a new location.
• 04:43 And at this point it says,
• 04:44 "Hey, I'm gonna put it in drive C." I'm gonna say, "You know what?
• 04:46 I'm gonna browse and I'm just gonna go and I'm gonna trust my documents folder, and
• 04:50 in my documents folder, you can see I have all my course work stuff for this course.
• 04:54 So I'm going to save.
• 04:55 Okay.
• 04:56 Now I'm also going to see that the sub folders are trusted.
• 04:59 What that means is that any document that's stored in this folder when I open
• 05:03 it up in Excel, if there are macros in it, it will run with permissions.
• 05:07 That's okay.
• 05:07 I'm happy with that.
• 05:08 I know I'm only going to store stuff in here that I've Built.
• 05:12 So we're going to say okay.
• 05:14 Now the other thing that you can do, is create trusted locations on your network.
• 05:19 The problem with this one here, again you get the same options.
• 05:22 Would you like to trust subfolders and what not?
• 05:24 The problem with this is that you're not in control of what's going into
• 05:27 that folder.
• 05:28 And this is why it's marked as not recommended.
• 05:30 We used to use this for some of the stuff we did in our organization,
• 05:34 but we knew that we only had one person that was programming.
• 05:38 In this particular case, you have to watch out for somebody if you've got lots of
• 05:41 people with skills and somebody gets disgruntled, they,
• 05:43 if you have to allow the trusted location on your network and somebody throws a bad
• 05:47 file in there it will not prompt you about Macro it's just gonna run.
• 05:51 So, this is something that you need to keep in mind as you're
• 05:53 dealing with things.
• 05:54 Generally you are better not to do this and keep on clicking this thing that say okay,
• 05:58 because at least then you know there's a Macro rather than just running it and
• 06:02 go Full host source.
• 06:04 But you'll notice in this case here, I've set up a trusted location here.
• 06:07 I would suggest you do the same for your GoSkills document, so
• 06:10 you don't get prompted with it and we'll go from there.

Lesson notes are only available for subscribers.