Using the Locals Window

Quick reference

• 00:05 So now that we know about variables, it's important to understand the de-buttoning
• 00:09 tools we can actually use with them.
• 00:11 Why?
• 00:12 Cuz you're going to use a lot of variables in your code.
• 00:14 The more you code, the more you're going to find uses for them.
• 00:16 You're going to use them over and over and over again.
• 00:19 And having a tool set that allows us to understand what the state of those
• 00:22 variables is at any given time is really important.
• 00:25 My personal favorite tool for working with variables is the Locals window.
• 00:29 And you can see over here it's docked on the right hand side.
• 00:31 I got it by saying View > Locals Window, and pushed it over here.
• 00:35 And you'll notice it right now, it is inspiringly empty.
• 00:37 And that's because there's no code running.
• 00:39 This Window only ever populates when code is actually active and going.
• 00:44 So, what I'm gonna do is I'm gonna go back and
• 00:46 take a look at this particular routine here.
• 00:48 This is original.
• 00:49 And this shows us the state of the macro that we recorded after making the small
• 00:53 cleanup modifications but not with any variables in it whatsoever.
• 00:57 And I'm gonna Step Into this and
• 00:58 you're gonna notice that as soon as I say Step Into, it highlights the line
• 01:02 that says next thing I'm gonna do is enter into Sub Original.
• 01:05 But you'll notice the windows for Locals is shown up and
• 01:08 it's gonna module one listed in here.
• 01:10 This is the code of module one.
• 01:10 And it says there is no variables declared at a module level.
• 01:14 Okay no problem.
• 01:16 Well, what happens if I step in a little bit more?
• 01:20 Nothing, nothing is going on here.
• 01:22 And the reason is is because the Locals window is all about
• 01:25 working with variables.
• 01:26 And there are no variables in this particular piece of code.
• 01:28 So no matter what I do stepping through here,
• 01:30 it's not gonna show anything in the Locals window.
• 01:32 So I'm just gonna go and say Reset on this one because you know how this one works,
• 01:35 we don't need to actually look at that.
• 01:38 What I want to do now is just scroll back up and show you that I made some small
• 01:41 modifications to the RollForward macro that we've been working with.
• 01:44 We still have the Dim wsTarget As Worksheet that we have here, and
• 01:47 it's bound to all of the wsTarget pieces here that are pushing back to it.
• 01:53 I add a new variable for Dim sSheet As String.
• 01:56 sSheet is holding a text value called, or is going to be assigned,
• 02:00 a text value called schedule.
• 02:02 Why schedule?
• 02:02 Because that's the name of the worksheet and when I set wsTarget,
• 02:06 I'm going to say worksheets, what is the variable value for sSheet schedule.
• 02:12 So, give me the schedule worksheet, we're going to assign that to wsTarget and
• 02:16 then we'll operate on wsTarget throughout.
• 02:20 Now, let's take a look at what's happening in the state of these variables now.
• 02:24 When I go and
• 02:25 click Step Into, you'll notice the window looks a little different right away.
• 02:29 We have a wsTarget variable and sSheet.
• 02:32 wsTarget has nothing in it.
• 02:34 wsSheet is just quote quote, which of course for text,
• 02:36 everything between the quotes, that means it's pretty much got nothing in it.
• 02:40 And if we step now, you'll notice that it steps right over the dimension lines,
• 02:43 because they're already read right away.
• 02:47 And it says sSheet = "Schedule".
• 02:49 Well, right now it's still blank.
• 02:51 But when I execute this line,
• 02:53 you'll notice that it does take the value of Schedule.
• 02:56 Now, what's interesting about this, let's figure that I'm about 90 lines down,
• 03:00 I've done all kinds of stuff, and
• 03:01 I don't wanna come all the way back up to this top line to reset this.
• 03:04 This is actually not a read-only area.
• 03:07 I could come back in here and go and
• 03:08 call this whatever the worksheet name is that I want.
• 03:11 So I just typed in a new value between the quotes and hit enter.
• 03:14 In this case,
• 03:14 I actually do want this to be schedule because that's the name of the worksheet.
• 03:19 I don't want to cause an error if I try and
• 03:20 work without it, so I can enter it back in here.
• 03:22 So you can change variables in the Locals window if you need to.
• 03:27 wfTarget, you'll notice, is still nothing it hasn't actually had this line of code
• 03:31 executed and assigned anything to it.
• 03:34 So lets go and click on Step Into.
• 03:37 And look at that.
• 03:38 We know have a new little plus sign beside wsTarget.
• 03:42 So I'm gonna click on this and expand it.
• 03:45 And look at that.
• 03:46 It gives me a whole bunch of properties of the wsTarget worksheet object.
• 03:52 So this is kind of neat because when I actually go through and
• 03:56 look at this now, I can see that this has a code name.
• 03:59 So I can refer to this as wsTarget.CodeName would give me Sheet1.
• 04:03 I can see that there's other things that are actually going on in here as well
• 04:07 is outlining enabled that is apparently is not.
• 04:09 Is calculation enabled?
• 04:11 Yes it is, it's true.
• 04:12 So this is kinda interesting.
• 04:13 I can take a look at some different pieces though here.
• 04:16 We could look at, let me see here,
• 04:18 I don't know what else is in here that looks interesting?
• 04:20 Query tables might be something that you're interested in looking at.
• 04:24 Or list objects, these are actually official Excel tables.
• 04:27 Now there aren't any on this worksheet so if I were to drill into list objects,
• 04:31 it tells me there's count of zero list objects.
• 04:34 It tells me the index of the actual worksheet itself.
• 04:37 1, that means that Schedule is in the first position of all the index tabs.
• 04:41 So I can learn new things about the object model here.
• 04:43 I'm not gonna get everything, but I can learn some new things.
• 04:46 From this particular window about how I can actually talk to the object model.
• 04:51 The cool thing is that this shows up when you create your variables as soon
• 04:54 as you assign them, you can see what's going on.
• 04:56 It's live, so as you step through your code, these things will all update.
• 05:00 You can change them.
• 05:01 You can drill down.
• 05:02 You can look at new things and discover things.
• 05:05 This is actually one of the ways that I like to discover some of the objects and
• 05:08 properties and methods that I can actually get access to is by using a Locals window.
• 05:12 But the biggest key part?
• 05:13 It allows you to see what's in your variable at any time as you're stepping
• 05:17 through and looking at your code.
• 05:18 And that's huge as you start working with more variables.

Lesson notes are only available for subscribers.