Creating Variables

Quick reference

• 00:05 So knowing variables are useful is all good.
• 00:08 But how do you actually go about creating one?
• 00:11 Well, basically what we need to do is we need start
• 00:14 by defining what we call the variables dimensions.
• 00:18 Every variable needs two things in order to make it work.
• 00:21 It needs a unique name.
• 00:23 Okay, and these follow the same naming rules as your procedures and whatnot.
• 00:26 They've got to be unique,
• 00:27 you can't start with a number, no special characters except underscores.
• 00:31 So, that kind of thing.
• 00:32 It also needs a data type.
• 00:34 This is another really important piece around a dimension.
• 00:38 The code to accomplish this process basically gets rolled out into
• 00:41 this one line.
• 00:42 DIM, which is short of dimension, whatever your variable name is,
• 00:45 AS, whatever the type is, okay?
• 00:47 So, that's the straightforward thing, it's only four words, not a big one, and
• 00:51 two of them never change, okay?
• 00:52 So, some naming tips that I've got for you on this.
• 00:56 Try and use a short but descriptive name.
• 00:58 You don't want to overly abbreviate so it doesn't make any sense, but
• 01:01 you want something that at least is helpful to know what it is.
• 01:04 But you don't want something that's like 40 characters long to type because that
• 01:07 makes things kind of ugly.
• 01:10 It's optional to go through and preface a name with a shortened data type,
• 01:15 like WS for worksheet, or I for integer, or L for long.
• 01:18 This is a practice that's called Hungarian notation.
• 01:21 You'll find that there are people who absolutely love the concept,
• 01:24 there are people who absolutely hate it.
• 01:27 Personally, I actually do follow that particular syntax,
• 01:30 I find it makes my variables a little bit more identifiable on my code which is what
• 01:34 I'm actually looking for.
• 01:36 There's a lot of people who said don't use them you don't need to have you variables
• 01:39 identifiable you can fix your things out on your own.
• 01:41 So, it's totally up to to you whether you decide to follow that pact or not, I will.
• 01:45 You can come up with your own coding styles, that's totally up to you.
• 01:49 Now DIM is easy, the AS is easy,
• 01:53 coming up with a name is simple but what about the types?
• 01:55 What kind of data types are there?
• 01:57 Well, there's a whole bunch of them.
• 01:59 And here's where they are, here's a bunch of them.
• 02:00 This isn't all of them, but this is the ones that are most common.
• 02:03 Boolean for true or false values, integers for numbers within the scope that you
• 02:07 actually see there long for really big numbers, but they don't have decimals.
• 02:11 Double does have decimals, that's probably what you're going to use for currency and
• 02:15 things like that.
• 02:16 Dates and strings.
• 02:18 The two that are probably the most confusing around this are objects.
• 02:22 These can be things like application objects, it can be workbooks, worksheets,
• 02:25 ranges.
• 02:26 It can be tables, like list objects.
• 02:29 It can be all kinds of different things in there,
• 02:30 so this is one area that you'll end up playing around with things.
• 02:34 The other one is a variant.
• 02:36 And a variant data type is something that basically says, I don't know so
• 02:41 I'll let Excel decide at run time.
• 02:43 And I'm gonna show you a little bit later in our modules why that's a really
• 02:46 bad idea.
• 02:48 Now the question you're gonna wanna know is where do we create them?
• 02:52 And the answer to this is kind of like the old Facebook relations status,
• 02:55 it's complicated.
• 02:57 Generally what you wanna do is you wanna make sure that you always instantiate your
• 03:01 variable before you need it.
• 03:02 So, usually in the line above most often it's at the top of your routine.
• 03:07 So when you've got your sublime, you'll create most of your variables right there,
• 03:11 and then you'll go and do a bunch of different things.
• 03:13 You can instantiate your variable in the middle of your code,
• 03:16 right before you need it, that's totally fine.
• 03:18 I'll give an example of a particular routine that has variables defined.
• 03:22 It might look like this.
• 03:23 We've got the Sub ChangeWorksheetName().
• 03:25 The first line is Dim wsTarget As worksheet that's where we ask you to
• 03:28 mention the variable.
• 03:29 The next line is a set line where it is actually being used.
• 03:32 So the Dim line can't be after the Set line,
• 03:35 that would be bad because you can't set your variable to be something,
• 03:39 until it has already had its dimensions defined, okay, that is the big key there.
• 03:43 Does it need to be at the very top of the worksheet?
• 03:45 In this case the the Set statement is line two, so yes it does.
• 03:49 If you had 90 lines of code in here and
• 03:51 that Set wsTarget didn't happen until row 85 of that particular block of code,
• 03:56 you could have your Dim statement anywhere in above that, it's not a big deal.
• 04:00 Generally, I find that I cluster them near the top in
• 04:03 order to actually be able to find them easiest.
• 04:06 So what I'm gonna do now is I'm gonna hop over to Excel, and
• 04:08 we're gonna actually take the example that we've been working on.
• 04:10 I'm gonna put the dimension line in for that particular code, so
• 04:14 that we can get started putting the variable in.
• 04:19 All right, and here we are, we're inside our little Creating Variables.xlsm module.
• 04:26 Which you can see is based on the same expense schedule we have with the code we
• 04:29 had before.
• 04:30 So we're gonna start the process of changing this out.
• 04:33 The first thing we need to do, and this is the focus of this module,
• 04:36 is creating the variable lines.
• 04:37 So I'm gonna go and add a little bit of white space here.
• 04:39 Gonna hit tab, and
• 04:41 I'm going to create my variable which is gonna be dim to dimension it wsTarget.
• 04:46 So I'm prefixing it with ws for worksheet, it's totally optional.
• 04:49 Target because this is the worksheet I'm gonna be targeting.
• 04:52 So it's really come up with a name that works for you,
• 04:54 whatever your naming standard is.
• 04:55 Then we'll say as, and we'll give it a type.
• 04:58 So, w-o-r-k-s.
• 05:01 Hey, look at this.
• 05:02 So I got to worksheet in the intellisense.
• 05:04 Now, I am a really efficient coder,
• 05:06 or lazy coder depending on how you want to put it.
• 05:10 At this point, I can hit Tab and it will autocomplete the word for me, and
• 05:14 that's one of the big things that I love about this.
• 05:16 And now when I go and hit my down arrow, or hit Enter, you'll notice that
• 05:20 even though I typed Dim in all lowercase, it automatically corrects it for me.
• 05:24 This is actually something that I will now use is that auto-case
• 05:27 correction in order to validate that I've done my code correct.
• 05:30 So, this is all I wanna do for right now is I created the variable, and when we
• 05:35 look in our next module we'll go and we'll show how to actually set it and use it.

Lesson notes are only available for subscribers.