Locked lesson.
About this lesson
Entry level steps to allow the user to record macros in Excel.
Exercise files
There are no related exercise files for this lesson.
Quick reference
Setting up the Macro Environment
How to set up the environment for working with Macros and VBA.
When to use
When you have a brand new Excel install or when you have not recorded macros in Excel before.
Instructions
Displaying the Developer tab
Excel 2007:
- Office icon --> Options --> Popular --> “Show Developer Tab in the Ribbon”
Excel 2010 & higher
- Right click any Ribbon tab --> Customize the Ribbon
- Check the box next to Developer tab on the right side of the screen
Trust Center Configuration
Go File --> Options --> Trust Center --> Trust Center Settings
Review “Macro Settings”
- Should be set to “Disable all macros with notification”
- Do not “Trust access to the VBA project object model”
To allow specific folders to run without prompting:
- Go to Trusted Locations --> Add New Location
- Be warned: anything here will run without asking you!
Requiring Variable Declaration
This is a key step for new coders (impact will be shown in the Variables section)
- Go to Developer Tab --> Visual Basic Editor --> Options
- Check “Require Variable Declaration”
- 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.