Option Explicit - To use or not.

[Pacman52]

Hi all, I've been working on a new workbook for my wife to record her sales and expenses throughout the year. The VBA elements are pretty basic and ultimately the workbook is only going to be used by her.

I've been doing the final testing on all the forms and code to tidy it all up and remove any test code used whilst building it and just noticed I have missed off Option Explicit on most of the modules, question is do I really need to use it as the code all works fine without it.

I know why is used and may just add it in as I go through the various modules anyway but just wanted to get your thoughts on using it or not.

Thanks Paul

 

[Joe4]

Always use it!
It really helps in compiling your code and debugging it, and will greatly reduce the chances of errors.

Note that you can set it so that Option Explicit always is added to your modules, by default.
This link shows you how to change that setting: https://www.wallstreetmojo.com/vba-option-explicit/

 

[Pacman52]

Always use it!
It really helps in compiling your code and debugging it, and will greatly reduce the chances of errors.

Note that you can set it so that Option Explicit always is added to your modules, by default.
This link shows you how to change that setting: https://www.wallstreetmojo.com/vba-option-explicit/

Thanks Joe - you make a fair point about debugging and thanks for the link to set it to be a default setting as well.

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[offthelip]

Just to argue the case against using option explicit.

1: One of the problems with VBA is that “typing” of variables is not transferred from a worksheet range. This means that all variables which are read from or written to the worksheet must be variants. This means that even if the range has only got numbers in it, the array which you load it into memory must be a variant array.

This means that a lot of variables which you might want to define as text , dates, numbers etc have to be variants because they are loaded from or written to the worksheet. So the declaration statement conveys no useful information and is in some ways is misleading. Thus to my way of thinking it is a waste of space and also it clutters up the start of a module so that any declarations which really are necessary are hidden

2: Using Option Explicit does catch a lot of typos, but it doesn’t catch all of them, since if the typo happens to coincide with another variable it will happily compile, but with the wrong variable. If you are using option explicit you are very unlikely to spot that the wrong variable has been used. However if you are in the habit of checking the spelling of variables yourself you will find this error much more rapidly.

3: Using sensible names for variables is much more useful in defining what a variable is than a declaration statement, for example

For i = 1 to 100 , it is obvious what the variable “i” is and it doesn’t need to be declared.

4: The time taken declaring all the variables in lots of module would be better spent writing a good description of what the module does and adding good comments to the code

5: Getting in the habit of typing the variable names in correctly is a very good habit to get into, it is a habit I got into years ago when coding in languages which didn’t have the “option explicit” facility, which means it is only about once or twice a year do I ever end up with an error that option explicit would have found

6: Excel VBA does a very good job of defining variables sensibly by default. On more than one occasion I have written code on this or other forums (without using option explicit) which the Op has then tried to define the variables and succeeded in breaking the code by getting the declarations wrong! If you really understand typing of variables then using option explicit is unlikely to be detrimental to your code , but if you are not sure about the types then EXCEL VBA is likely to do a better job of defining the types for your variables.

 

[Alex Blakenburg]

And on the for side , declaring the variables gives you access to intellisense on those variables and save you a lot of coding time.
eg for declared variables
• you can Type part of the variable and you can use intellisense to autocomplete.
• If the variable is an object then when you type the period after it you will get intellisense for its properties and methods.
So one of the ways that it reduces typos is by you not actually having to type it all (pick if from the drop down list)

 

[Joe4]

Just to argue the case against using option explicit.

1: One of the problems with VBA is that “typing” of variables is not transferred from a worksheet range. This means that all variables which are read from or written to the worksheet must be variants. This means that even if the range has only got numbers in it, the array which you load it into memory must be a variant array.

This means that a lot of variables which you might want to define as text , dates, numbers etc have to be variants because they are loaded from or written to the worksheet. So the declaration statement conveys no useful information and is in some ways is misleading. Thus to my way of thinking it is a waste of space and also it clutters up the start of a module so that any declarations which really are necessary are hidden

2: Using Option Explicit does catch a lot of typos, but it doesn’t catch all of them, since if the typo happens to coincide with another variable it will happily compile, but with the wrong variable. If you are using option explicit you are very unlikely to spot that the wrong variable has been used. However if you are in the habit of checking the spelling of variables yourself you will find this error much more rapidly.

3: Using sensible names for variables is much more useful in defining what a variable is than a declaration statement, for example

For i = 1 to 100 , it is obvious what the variable “i” is and it doesn’t need to be declared.

4: The time taken declaring all the variables in lots of module would be better spent writing a good description of what the module does and adding good comments to the code

5: Getting in the habit of typing the variable names in correctly is a very good habit to get into, it is a habit I got into years ago when coding in languages which didn’t have the “option explicit” facility, which means it is only about once or twice a year do I ever end up with an error that option explicit would have found

6: Excel VBA does a very good job of defining variables sensibly by default. On more than one occasion I have written code on this or other forums (without using option explicit) which the Op has then tried to define the variables and succeeded in breaking the code by getting the declarations wrong! If you really understand typing of variables then using option explicit is unlikely to be detrimental to your code , but if you are not sure about the types then EXCEL VBA is likely to do a better job of defining the types for your variables.

I think that may be the first time I have ever seen a programmer come out AGAINST using Option Explicit...

However, my biggest problem is I am not a great typist, so I am very prone to typos. And I can look at it all day long and not see it.
When I make a typo, it tells me that I have an "undeclared variable", which tells me immediately I made a mistake.

So for me (and I imagine a lot of other "less than perfect" programmers), it has been very helpful over the years.

 

[RoryA]

One of the problems with VBA is that “typing” of variables is not transferred from a worksheet range

That is only true if using arrays, and not really surprising.
Your #2 makes no sense to me. The fact that you are using Option Explicit can't really make you less likely to spot the same typo.
#3 I agree with your premise but not your example. Using i as a variable name doesn't make it clear to me at all what the variable is for, but I don't see how that relates to this discussion anyway. Option Explicit only makes you declare variables. It doesn't make you give them a more specific type than Variant, nor does it help you determine what they are for particularly.
#4 I don't see why you can't do both.
#5 may well be true for you, but doesn't make it a good general practice in my opinion
#6 Excel doesn't define anything sensibly. Everything just becomes a Variant. You can do that yourself just by using Dim.

Having said all that, in a situation where the code is already written, tested, and working, I don't see much point in going back and adding it in.

 

[RoryA]

I think that may be the first time I have ever seen a programmer come out AGAINST using Option Explicit

I think it's probably the second time I have, but the first was someone who seems to want to violate every programming principle I've ever heard of...

 

[Joe4]

Having said all that, in a situation where the code is already written, tested, and working, I don't see much point in going back and adding it in.

That is a very good point. It is most helpful during code development.
If you already have working code that has been thoroughly tested and approved, it is far less important at that point, and probably don't need to add it in at that point.

I use the caveat "thoroughly tested and approved", because just because it doesn't return any errors doesn't necessarily mean it is working correctly (so you don't want to get a false sense of security with that). You could have a typo where you mistyped a variable that you are using, and it won't return an error, but doesn't return the expected result either!