Explicit vs Implicit Variables

Quick reference

• 00:05 What I've been showing you until now is how to explicitly declare the data type in
• 00:09 a variable.
• 00:11 It's also true, though,
• 00:12 that you can actually dimension a variable without the data type at all.
• 00:15 It looks like this, where we go something like Dim x.
• 00:18 What actually happens when you do this is that it
• 00:21 defaults back to the default data type, which happens to be a Variant data type.
• 00:27 The challenge around this is that the variable type is determined at runtime.
• 00:31 It's not determined at design time when you're actually writing your code.
• 00:35 This means that depending on what actually happens in a routine,
• 00:38 if you have two different variables, and you're trying to compare the two
• 00:41 together to see if they're the same, that could actually cause you some problems.
• 00:44 Because they may end up coming up with different types depending on what's
• 00:48 going on.
• 00:48 It also means that as you're coding, it prevents you from using Intellisense.
• 00:52 Because if Excel doesn't know what the data type is,
• 00:54 it can't offer you the proper properties, methods, and
• 00:57 objects that actually go with all of the different components.
• 01:00 So ideally, I would tell you not to do this.
• 01:04 I also wanna show you though where we can do this by accident.
• 01:07 Because as you're actually building multiple variables out,
• 01:11 you can actually put multiple variables on the same line.
• 01:15 So, you could do something like this where you say Dim sEmployee (string),
• 01:20 sManager (string).
• 01:21 What's gonna happen, in this case, you're actually gonna get two variables,
• 01:24 sEmployee and sManager, they're both gonna be strings.
• 01:26 Everything looks good.
• 01:28 But you can also do this, Dim wsSource, wsTarget.
• 01:35 When you do that, you actually get two variant data types,
• 01:39 because there's no data type declared.
• 01:42 Okay, that might make sense.
• 01:43 But what happens when you do this?
• 01:48 You would think by looking at this that it would declare both iCount and
• 01:52 iIterations as integers, but unfortunately it doesn't.
• 01:57 It actually gives you iCount as a variant and iIterations as an integer.
• 02:02 So that's not really all that good a thing, okay?
• 02:05 We wanna try and avoid that if we possibly can.
• 02:08 So that's the first part, always make sure you declare the data types for
• 02:12 your variables.
• 02:13 There's also something else I wanna show you, though.
• 02:14 At the very beginning, I had you toggle a setting inside your VisualBasic editor to
• 02:18 force variable declaration.
• 02:20 And this is where we actually run into the concept, not of explicit versus implicit
• 02:23 data types, but actually explicit versus implicit variables as a whole.
• 02:28 If you ever open a code module, and at the top of it there is no
• 02:32 Option Explicit statement listed there, that means that
• 02:35 variables are created on the fly as variant data types when they need it.
• 02:40 This is scary stuff, it's a very dangerous practice.
• 02:43 What that means is as you're typing things out,
• 02:45 if you get a spelling mistake it leads to a brand new variable.
• 02:48 This means that you may get inconsistent data types,
• 02:51 it could be all kinds of things.
• 02:52 You may be trying to compare against a specific variable that's never had
• 02:56 anything changed in it,
• 02:57 because it's created a new variable somewhere down the line.
• 03:00 So I would tell your right off the bat if you ever open up Excel and
• 03:03 you don't see the Option Explicit line, go in to the visual basic editor options,
• 03:09 make sure you check the require variable declaration.
• 03:12 And just type Option Explicit at the top of your module.
• 03:14 That'll turn it on.
• 03:16 So just don't avoid doing that, okay?
• 03:18 That's really important.
• 03:19 This may be a bit painful at first.
• 03:21 You'll see what happens.
• 03:21 I'm gonna show you in a second what happens if you don't have
• 03:24 an Option Explicit statement.
• 03:25 But the big key here is you want to know about these errors at the design time,
• 03:30 not at runtime.
• 03:31 Because probably you're building your VBA code to give it to someone else.
• 03:35 The last thing you wanna do is have something blow up as it's running and
• 03:39 launch them into the Visual Basic editor if they've never seen code before.
• 03:42 That's scary, scary stuff.
• 03:44 We don't wanna do it.
• 03:45 So I would implore you, please make sure that you use the variable declaration.
• 03:49 Have an Option Explicit line at the top of every module, and actually use it.
• 03:54 Declare your variables up front.
• 03:55 It's a very good practice.
• 03:56 Let's go see what happens if you don't.
• 04:00 All right, here we have a very short piece of code, it's for demonstration only.
• 04:03 But you can see that we are dimming ll is long, we're saying ll = 1.
• 04:08 And we're asking for a message box to come back and tell us about ll.
• 04:12 And what's gonna happen here, is we can go and step into this, and
• 04:15 we can see that it sets the variable ll = 1.
• 04:18 And then it gives me back a message box, which tells me that it equals 0.
• 04:22 You go, what, how does that make sense?
• 04:25 So the challenge here is that we haven't declared any variables.
• 04:28 There's no Option Explicit line at the top here.
• 04:30 At least, that's not the challenge, but it is a problem here.
• 04:33 So I'm gonna type in this option explicit, we're gonna turn this on.
• 04:37 And now what I'm gonna do is, I'm gonna go through, I'm gonna try it again.
• 04:40 And as soon as I step into it, it comes back and it says, hey,
• 04:43 there's a compile error, your variable is not defined.
• 04:46 OptionExplicit forces you to declare your variables.
• 04:49 And it says, this variable's not defined.
• 04:50 And I say, but that's not true because I can see it right here.
• 04:53 And this is the challenge with this particular font, is that Ls and
• 04:58 1s look exactly the same.
• 05:00 This is actually l1 As Long.
• 05:02 This is l1 is the message box that it's calling for.
• 05:05 And this variable is ll.
• 05:07 If I were to go back now and change this to l1, it's very subtle.
• 05:11 What you'll see now is that, as we go through here,
• 05:15 you can say l1 = 1, MsgBox l1, now we get the value of 1.
• 05:21 So the challenge is that without the OptionExplicit line in effect,
• 05:25 what was happening is I dimensioned one variable.
• 05:28 I assigned a value to something where I spelled it incorrectly.
• 05:32 But because OptionExplicit wasn't there,
• 05:35 it said, I'll just create that variable on the fly for you.
• 05:39 I never assigned anything into what's being called here.
• 05:42 And yet, we're gonna read you back the original value out of
• 05:45 the variable that you declared.
• 05:47 So if you don't see this word at the top of your module, just type it in.
• 05:52 And immediately go into the VBA Tools Options and
• 05:55 check Require Variable Declaration.
• 05:58 Every new workbook you create from that point will always have the OptionExplicit
• 06:01 line there that's gonna stop this particular problem from happening.
• 06:04 You'd much rather know about this up front than have it return the wrong result when
• 06:08 the code is actually running.

Lesson notes are only available for subscribers.