Option Explicit - To use or not.

Pacman52

Active Member
Joined
Jan 29, 2009
Messages
416
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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/
 
Upvote 0
Solution
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.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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. :)
 
Upvote 0
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... ;)
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top