What are Variables?

Quick reference

• 00:05 The next step in our coding journey is to learn about variables.
• 00:09 So what's a variable anyway?
• 00:11 Well, essentially,
• 00:12 it's a piece of memory that's curve out to hold something specific, okay?
• 00:16 Just a temporary container.
• 00:18 Inside that container we can put things.
• 00:20 We can put in values or
• 00:22 text strings or even entire objects like workbooks or worksheets or ranges.
• 00:26 So a variable is a very dynamic and
• 00:29 useful tool to be able to store something in memory.
• 00:32 If you think back to your calculator you might remember actually doing a little bit
• 00:36 of an equation and then hitting memory plus to store that value for later.
• 00:40 Then you could do some more mathematics, you can actually use memory
• 00:43 recall to bring it back and actually have it put into your solution.
• 00:47 The same thing is true with variables, its exactly the way they work.
• 00:50 You can basically stuff something in it or hold on to it and
• 00:52 bring it back when you need it a little bit later.
• 00:54 So it's a really, really useful concept to actually have.
• 00:57 Why do we need these guys at all?
• 00:59 There's actually quite a few reasons.
• 01:00 The number one reason is that we can actually target a specific object and
• 01:05 then we can actually refer to that object or that variable over and
• 01:09 over again very easily.
• 01:11 A lot easier than typing the whole thing out of worksheets 1.range A5.
• 01:15 That becomes a big long piece that we would have to keep on targeting over and
• 01:19 over again.
• 01:20 Every time we do that, BBA has to go through and has to say, okay.
• 01:24 Okay well first I'm gonna find worksheet one, then I'm gonna find the range,
• 01:27 then I'm gonna do whatever I need to.
• 01:29 If we could just say I wanna assign that range to an object, it now already knows
• 01:33 which worksheet it lives in it doesn't have to go look anymore.
• 01:35 It can go directly that the range, that's really, really useful.
• 01:39 Another component is it allows us for actually writing more efficient code.
• 01:44 Instead of actually having to go through and type out worksheets, open parenthesis,
• 01:50 quote, control panel, quote, closed quotes, and closed parenthesis.
• 01:53 We could actually assign that to a variable.
• 01:56 Called something like WSCP.
• 02:00 Typing those four characters, WSCP is gonna be a lot shorter than typing in
• 02:04 worksheets open bracket, you get the idea.
• 02:07 So, if we're trying to be more efficient as we're writing,
• 02:09 this becomes very very useful.
• 02:12 Another thing that's really cool about variables is that once we assign
• 02:15 something into that memory, we can go back and change those properties at any time.
• 02:19 So we could change what the text string says, or
• 02:21 we could change what the value is.
• 02:23 Or we could go in and we could actually modify properties of a worksheet, for
• 02:27 example, or a range if it were actually stored inside a variable.
• 02:30 So this is something that's really, really useful to us.
• 02:34 I wanna show you practical example of where this stuff actually
• 02:36 becomes important.
• 02:38 I wanna think back to the code that we wrote in order to do our row for
• 02:41 macro, after we went through and we made some modifications to it.
• 02:45 We got streamed line down to look like this, so it's a lot shorter and
• 02:48 a lot more readable than what was originally recorded through the recorder.
• 02:53 But there's one thing that' s not really spoken about here and
• 02:55 that is, the ranges are very loose.
• 02:59 They're basically ranges from where.
• 03:02 If I were to go back and fully qualify this o, what we're actually looking at
• 03:07 is this, it's not just range E6 to E10 dot copy is, where does that range live?
• 03:13 It lives on the active worksheet.
• 03:16 So every one of these guys, if I replace this code as ActiveSheet.Range,
• 03:19 ActiveSheet.Range all the way down this thing.
• 03:21 And now what I want you to think about is when this macro is running let's
• 03:24 pretend its running in slow motion, it gets through three line, and
• 03:28 then whoever's sitting in front of the keyboard gets impatient and
• 03:31 they click a different worksheet.
• 03:33 What happens?
• 03:34 Well the active sheet changes so this is no longer bound to the Worksheet,
• 03:41 the schedule, or the control panel, whatever we were working on.
• 03:43 It's now on a different Worksheet, and it's gonna be trying to figure out and
• 03:46 finish the rest of the macro.
• 03:47 It doesn't know it changed, it doesn't care, so
• 03:49 it's gonna run through those commands.
• 03:51 What will happen in this case?
• 03:52 We had a schedule on our first worksheet, if we actually went to the second
• 03:55 worksheet, which was completely blank, and we ran our macro.
• 03:59 Well, it's just gonna run it on the blank worksheet.
• 04:00 It's not gonna do anything on the original one.
• 04:02 So this is a challenge.
• 04:03 This is where variables actually help us out in a big way.
• 04:06 Because if we go and jump over here to looking at a slightly different version,
• 04:10 what I've done here, as I said,
• 04:13 let me have a new variable with this wsTarget as worksheet.
• 04:16 I'm going to set it as the active sheet,
• 04:18 and then I'm going to refer back to wsTarget over and over again.
• 04:22 Well the difference here is that the wsTarget
• 04:26 is Picks up the active sheet at the beginning of the routine.
• 04:30 After that it keeps calling back to this piece that it's actually got
• 04:33 stored in memory.
• 04:35 So it's not saying it's going to look at the active sheet every time,
• 04:38 it says wsTarget is equal to this sheet that is currently active.
• 04:43 And it can, therefore, use that throughout the rest of the routine.
• 04:45 So now if somebody goes and clicks on a different Worksheet in the middle of this
• 04:49 it's still referring back to wsTarget.
• 04:51 What was that when it was set?
• 04:52 It was the active Worksheet that was whatever the control panel or
• 04:55 the schedule was at the time.
• 04:57 So this is where using variables can actually help us.
• 04:59 We can capture it once we know what we're looking at every single time.

Lesson notes are only available for subscribers.