Setting Variables

Quick reference

• 00:05 Every variable that we create, when a routine is started, it comes into scope
• 00:10 and it begins its life what we consider to be empty there's nothing in it.
• 00:14 It may have a value of blank if it's text, it may have a value of zero if it's
• 00:18 a number, or it may have just nothing if it's an object.
• 00:22 The way that we assign something actually really depends on what type of
• 00:27 variable it's going to be or what type it is.
• 00:30 So, if you're working with values, values are assigned by basically just saying,
• 00:34 give me the variable name equals whatever the value is.
• 00:37 So if I have an integer, that I've set up called iValue I
• 00:40 could just set that to be 50 by saying iValue = 50.
• 00:44 If I've got something that's text,
• 00:46 let's say that I've got a variable called sUser that has text.
• 00:51 That I do the same as a value except that I have to actually wrap the text that I
• 00:55 wanna pass in in quotes.
• 00:56 So I could have sUser = "Ken" and I'd have Ken in quotes.
• 01:01 If I was assigning texts from somewhere else, maybe it was from another variable,
• 01:05 I would say sUser equals whatever username or application dot username.
• 01:08 Is a way of actually getting a text string as well.
• 01:10 That would return the username for the application.
• 01:12 Objects are different.
• 01:15 Objects are actually assigned using a keyword, a Set statement.
• 01:19 So in this particular case, if we had something like wsTarget and
• 01:23 we wanted to set it to be equal to the active worksheet,
• 01:25 we would actually need to use this line.
• 01:28 Set wsTarget = Activesheet.
• 01:30 If you tried to set sUser = "Ken" it would give you an error.
• 01:34 And if you tried to say wsTarget = Activesheet it would also give you
• 01:37 an error, so this is kind of a key thing that becomes important.
• 01:41 If it's an object, you need to set it.
• 01:43 If it's not, you can just say =.
• 01:46 So, what we can with this one now, is we can all right,
• 01:48 we'll Set wsTarget = Activesheet, we could try it differently.
• 01:52 Let's say we want to set it to a specific worksheet.
• 01:54 Set wsTarget = ActiveWorkbook.Worksgeets(1),
• 01:58 or just .Worksheets(1), ActiveWorkbook is implied.
• 02:03 This would actually set it to the first worksheet in the workbook,
• 02:06 no matter which was active.
• 02:08 If I wanted to then go and set a range to
• 02:13 say A5 to be 67 on that target worksheet, I could actually refer to it to say.
• 02:19 Set rngTest that's my range that I set up is gonna be wsTarget.Range ("A5:B67").
• 02:25 Now key part that I wanna talk here is remember we talked about how we can make
• 02:30 things shorter coding?
• 02:32 wsTarget in this case is equivalent to ActiveWorkbook.Worksheets(1) which
• 02:37 would you rather type?
• 02:38 And I'll tell you personally wsTarget is way more in my wheelhouse for
• 02:41 that kinda thing.
• 02:43 Now the question is how do you remember all the stuff?
• 02:46 Well that's actually the purpose of what this slide is all about.
• 02:51 We have all of our different variable data types that we have on the left hand side
• 02:55 with our second column tells us what do we actually use those for.
• 02:58 And the third column tells us how do we assign them.
• 03:00 So for boolean, integer, long, double, date, strings,
• 03:03 all of those can be set just by saying my variable equals whatever it is.
• 03:08 Any objects, you're gonna use the set keyword.
• 03:11 The variants we'll get into those later but basically the variant it depends
• 03:15 because a variant could be an integer, or could be an object or worksheet.
• 03:19 So that one depends on what the real data type is that's actually gonna
• 03:22 be manifesting itself.
• 03:25 Once you've actually got this in there, how do you refer to them?
• 03:29 How do you use them?
• 03:30 Well, that's the next big step in this whole thing.
• 03:32 And this is actually the easy part of this whole deal.
• 03:35 If we take a look at the following routine, we can see that we've dimensioned
• 03:38 two new variables, sNewName as a String, and iIndex as an Integer.
• 03:42 The next line down says iIndex = 1, and
• 03:44 you'll notice I have comment after that on the same line as my code and
• 03:48 that's okay as long as the comments at the end it's all good.
• 03:51 So iIndex = 1, we've set that particular variable,
• 03:54 sNewName = Schedule, we set that.
• 03:56 These are both values, a string and integer, they're not objects.
• 04:00 And then I've got worksheets iIndex, well iIndex is the value of 1 that's
• 04:04 being passed into worksheets, so this is the first worksheet.Name = sNewName.
• 04:09 So I can actually pass these variables in and out as much as I need to do it,
• 04:13 which is great.
• 04:13 So let's go and
• 04:14 take a look at how that's gonna play in with the solution that we're building.
• 04:19 All right, so we have created our variable, so
• 04:21 what we're gonna do now is because it's a worksheet, that's an object.
• 04:25 We're going to use the set keyword, set wsTarget =,
• 04:30 and I'm gonna go with worksheets schedule.
• 04:35 This is the name of the worksheet as we can see down on the bottom here.
• 04:38 And I want you to notice that I've typed in everything lowercase.
• 04:40 The reason why is when I hit Enter now,
• 04:42 if I've done everything correct the casing should automatically change.
• 04:46 This is one of the reasons why I actually use my lowercase Case preface of a data
• 04:50 type and then an upper case letter to actually give it the name.
• 04:53 Because when I type it all in lower case and
• 04:55 hit enter I can see right away if it's actually validated.
• 04:58 Now, this is good we have our variable dimension.
• 05:01 We've set something to it.
• 05:03 But it's still not being used because everything refers to ActiveSheet in here.
• 05:07 So what I'm gonna do is I'm gonna replace all of the ActiveSheets with wsTarget
• 05:11 instead.
• 05:12 So here's what I'm gonna do.
• 05:13 I'm gonna highlight all this code.
• 05:15 I'm going to press CTRL + H that is my find and replace and
• 05:18 I'm going to change activesheet, and I'm going to change it too wsTargets.
• 05:25 It's replacing on the selected text not the whole module, that's cool.
• 05:27 We'll say replace, and eight replacements were made and
• 05:31 you can see that even though, again, I typed it in all in lower case,
• 05:35 up in the top here, it's actually giving me the proper casing over here.
• 05:39 The question is, did it work?
• 05:42 That's always the big question, isn't it?
• 05:43 So let's press, Roll Forward.
• 05:47 And indeed it has.
• 05:48 Everything looks like it's working quite nicely.
• 05:50 So that's how we go and we actually retrofit these guys in to go and
• 05:54 use a variable.
• 05:55 The big difference here, I'm referring to this specific worksheet.
• 05:59 So now, notice it's on February.
• 06:01 If I go over to sheet two and I come back into my routine and I run it.
• 06:09 That's wonderful.
• 06:11 We'll say debug on this.
• 06:12 Why did it do this?
• 06:14 Because it's tried to select a worksheet, and
• 06:16 unfortunately wsTarget is actually not active.
• 06:19 It needs to be active, so we'll go and we'll throw an activate statement in here.
• 06:25 We'll just go up, We'll say OK.
• 06:28 We'll try that line.
• 06:29 Now it activates the worksheet.
• 06:31 Now let's see if it goes through and let's me activate and it does.
• 06:35 So this is one of the key things that happens when you're actually building
• 06:38 your macros as well.
• 06:38 You'll end up running Hunting it, you'll hit an error.
• 06:41 It'll actually highlight the line for you and you have to recognize what's going on.
• 06:44 This is something you will find in order to select a cell,
• 06:47 the worksheet has to be active.
• 06:48 That's a big component.
• 06:50 Once you know that, it's an easy thing to fix.

Lesson notes are only available for subscribers.