Option Explicit

[PhilC]

I have to ask the question, why use Option Explicit?

I know I'm not a professional programmer and I've taught myself most of what I know about VBA, but it seems like Option Explicit would severly limit on the fly code creation and/ or merging code togeather. It also seems like it would create a lot of unnecessary code when a lot of variables are used in a project.

I don't see what the gain or upside is to using this command. Can someone explain why using Option Explicit is preferred to not specifically declairing variables?

Thanks,
Phil

[Aussiebear]

Option Explicit forces the User to declare all variables, and assists with error checking (typos) during the coding process. Most good programmers often suggest that those learning code should turn this option on, as a benefit to the coding process.

[PhilC]

Ahhh, I see. So as an advanced programmer do you still use the command? If you do, is it more for the purpose of catching typos as you code?

I always felt constrained by having to declare my variables, unless I flowcharted the program. I always tried to use variables that made sense to me and then put plenty of comments in to try to explain what the variables were for and how they were being used. Guess that's a hold over from when I taught myself code...:)

Anyway thanks for the explanation, it makes more sense now.

Phil:beerchug:

[Aussiebear]

I'm not that advanced, but Yes I always have it turned on

[Bob Phillips]

Brace yourselves!

Any developer who purports to know what they are doing and doesn't use Option Explicit, and who thinks that you shouldn't declare ALL variables, should be hung, drawn and quartered and then never let near an IDE again in their lives.

To not declare variables is a VERY BAD practice, modern languages insist on it. By using Option Explicit, you are getting the compiler to help you as it will flag up undeclared variables.

Aside from programming practice, there are two very good practical, selfish reasons for declaring variables. The first is a benefit of using Option Explicit, the second of declaring variables.

Say you have a variable that points to a range that holds interest rates, one you (intend to) call rngInterestRates. In your code you assign that variable to a worksheet range, and later on you want to test if it was previously assigned it. But being a bloke, you have fat fingers so you actually type something like


If rngIntersetRates Is Nothing Then


Note the slight mis-spelling.

Guess what, because you don't have Option Explicit, VBA will not complain, it will just treat them as two, separate variables, and that test will pass because that second variable never having been used before will always be nothing, even if you did setup the 'real' variable earlier. And it ensures that you have proper scope to your variables.

Secondly, you can use the IDE to auto-complete the variable names as you type them. In the example above, you could type
rngInter
and then hit Ctrl-Spacebar, and the IDE will complete it for you. If you have more than one similalrly named variable starting with with the string typed, you get a list to pick from. I even use this technique to check that I have recalled the variable name correctly. I try to use meaningful (at the time) names, but I still forget them. So I type the name all in lower-case, and then hit Ctrl-Spacebar, and if I have recalled the name correctly (and typed it correctly), the IDE changes it to the proper camel-case name, visually verifying that I have the correct variable name.

The VBA should always be setup so that the Options are 'Require variable declaration', this automatically sticks Option Explicit in modules.

It is anything but a noobies technique, it is one of the few tools we have to be more professional with VBA.

[Aussiebear]

So PhilC, in review.....

Subject: XLD, Aussiebear
Informative: 10, 0
Logical: 10, 0
Elegance: 10, 0

I'm definitely gaining ground on XLD don't you think?

[PhilC]

It is anything but a noobies technique, it is one of the few tools we have to be more professional with VBA.

Ok, ya got me on that one for sure. I guess I need to stop being lazy. I know that I should use the option explicit command but never really saw the point. I certainly appreciate your explanation. With that in mind I'll be adding that back into my coding practices.

[Bob Phillips]

As I said, you can force the IDE to put it in, you don't have to remember. In Tools>Options>Editor, uncheck Auto Syntax, and check the Require variable declaration.

[Simon Lloyd]

As I said, you can force the IDE to put it in, you don't have to remember. In Tools>Options>Editor, uncheck Auto Syntax, and check the Require variable declaration.I have to confess i have both of these like that:
1. because i am a bloke :)
2. because i hate excel complaining every time i click elsewhere to copy n paste........what? you all copy and paste don't you? or is it just me that copies flawed code to many different parts of my procedure? :yes

In fact for point 2 (uncheck Auto Syntax) i didn't even know you could stop the damnthing complaining until about 2 years ago when at a conference Simon during his (things i hate or love to control in excel) presentation pointed out that we should all rid ourselves of this blight!

I've been using Excel now for 6 years (using it for more than making lists!) and that was like a light bulb switching on. I guess what i'm saying (i'm hoping xld will concur) no matter how much you think you know about excel someone can always show you something new.

[Zack Barresse]

I completely agree with xld. Compiling your code is, IMHO, often understated. Variable declaration will give you far greater understanding of code, especially when you're trying to maintain it a year from now. Meaningful names are quite essential. For example, if you're debugging code and you read this:
Range("A1", Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0)).Formula = "=TODAY()+ROW()"
(not saying I've used it, just as an example), then you're not going to know right away what that range is for or supposed to be. If you're debugging thousands of lines of code, flipping between the worksheet and the VBIDE can be a pain in the back side. If instead you had the following code (again, just for examples sake):
Dim rngMonthDays As Range
Set rngMonthDays = Range("A1", Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0))
rngMonthDays.Formula = "=TODAY()+ROW()"
To me that seems to make a little more sense, and I can read it much easier and actually have an idea of what it's supposed to be doing. Maybe it's not the best example, but you can do this with literally any variable you want. Of course that would also mean you need to have some sort of standardized naming convention, which most programmers/developers do.

And to top all of that off, one of my favorite features - intellisense. Another very cool feature of VBA. :)

[Simon Lloyd]

.......And to top all of that off, one of my favorite features - intellisense. Another very cool feature of VBA. :)Yeah, but don't you hate it when your code doesn't compile or maybe stops at a point and then for some strange reason only known to xld and the boffins at MS intellisense stops working?, reload the workbook and all is well!?

[Zack Barresse]

Nope. There is generally a very good reason as to why intellisense has stopped working. If my code doesn't compile, then I've probably done something wrong. I ALWAYS use Option Explicit. :)

[PhilC]

Variable declaration will give you far greater understanding of code, especially when you're trying to maintain it a year from now.

Hi Zack,

Not to be argumentative but, declaration of a variable really only gives you understanding of what that variable was created for, and what the intended use of the variable is. It doesn't provide you with the understanding of why that variable needs to be in a specific portion of code, nor what it is used for in a portion of code. The only thing the declaration can really tell you what type of information will be stored in that variable, nothing more.

Comments are the documentation of code. It is the comment that tells you why a variable is used and for what purpose. This is why I originally asked the question. I always ask about the purpose of something that seems to serve no real purpose.

Consider, if I declare that A is a string. Without a comment all you know is that it is a string. Kinda like saying “no matter where you go, there you are.” It doesn’t seem to have much of a purpose nor does it give any hints as to what it is being used for. However if I put a comment somewhere in the code (without declaring the variable) that describes what A is and how it is being used in that portion of code and why it is being used then I know far more than I would had I declared the variable.

However, don’t misunderstand my intent here. I applaud your use of Option Explicit and the underlying reasons that you use it. If you do feel like it helps you understand code better that is a bonus. I certainly would not wish to undermine good code practices. I must admit I do get a bit lazy when it comes to coding and if I don’t have to type a half a dozen characters without good reason then I really don’t want to type them unless it brings some sort of benefit to the code.

XLD really answered my question by simply stating that, "...it is one of the few tools we have to be more professional with VBA.” With that in mind, and recalling my previous programming experience and knowledge, variable declaration has been around for many, many years. It was very helpful when I flowcharted programs to streamline the logic for a given process or function and I simply drifted away from that practice when I stopped flowcharting.

Professionalism is really the key here. Firemen drive Red fire engines. Why Red? Red is the established and accepted/ professional color for firemen. I do know there is history there about the original choice but the end result is that Red is the professional color for firemen. So, Option Explicit, is the professional tool for VBA code
Thank you all for your responses, I certainly appreciate it.:beerchug:

Phil C

[Zack Barresse]

It is true that, on its own, variable declaration doesn't necessarily tell you what the variable is or does. But a good naming convention can indeed tell you just that. In your example, the variable "A" doesn't tell you squat. We can all agree to that. But a variable of "rngAddressList" tells you what, that it is a range and that it should consist of a list of addresses. Pretty explanatory to me. So, dependent on design, variables can tell you a LOT about what the code is trying to do.

I echo everything Bob says as well as intellisense and elegance. To me it is cleaner and much easier reading with (well written) variables. I personally do not comment what variables mean, I hinge that on the name of the variable. If you declare variables such as "A", I would say it's an unwise naming convention, and not 'professional' either.

And I wish you would tell all fire departments that firemen drive RED trucks. We have some in Oregon that are blue, orange, green.... ugh! :p

[PhilC]

OOPS, I also forgot to thank XLS for the "uncheck Auto Syntax".

For years I've fought with VBA complaining when I clicked or tried to cursor anywhere. I often put in garbage just so I can get off the line to find what I was looking for.

So, Thank you very much XLD!! I love learning new things about VBA!

PhilC

[PhilC]

Ouch!! That just isn't right...LOL A blue fire truck... I'm speechless...

And I'm glad you understood the gist of what I was saying!! :)

I never liked typing out long variables, but in contrary I did type out verbose comments... Go figure

I did have a written variable declaration page that I kept by the computer but never used the option explicit command. Now, I think I'll be using the option explicit command.

I do still use as few characters as possible for a variable and I discovered a cool feature I didn't realize before with variable declaration in VBA. You can right click the variable and select definition to jump you up to the definition of the varible where I do have the comment about the variable's intent.

The most important thing I've learned in code is to be consistent. So if I use "I" as a for next loop counter, then I always use "I" as a for next loop counter.

I think it's really about personal style when creating a naming convention for variables, UNLESS you are in collaboration with others. In that case the naming convention needs to be a more common, agreed upon structure. So that when I talk about "I", we all know that I'm talking about a for next loop.

Just my thoughts... Phil

[Bob Phillips]

I do still use as few characters as possible for a variable and I discovered a cool feature I didn't realize before with variable declaration in VBA. You can right click the variable and select definition to jump you up to the definition of the variable where I do have the comment about the variable's intent.

You can do that more simply by putting the cursor in the variable, and hitting Shift-F2. This is also true of a call to another procedure.



I think it's really about personal style when creating a naming convention for variables, UNLESS you are in collaboration with others. In that case the naming convention needs to be a more common, agreed upon structure. So that when I talk about "I", we all know that I'm talking about a for next loop.


I always use i, j, k for my loops, they are disposable variables, and quite honestly everybody understands them, so I feel no guilt in not using a meaningful name there.