Why Option Explicit?

[frabulator]

I have been programing in VBA Excel for a little over a decade now, and have never found a situation where using Option Explicit was necessary to run my code.

Why do I always see that people say you have to use it when writing macros? Or, that it is a 'noob mistake' to leave it out?

Is it really that necessary in Excel?

As I said, I have never ran into an issue in writing add-ins and custom macros that needed Option Explicit to fix a bug or an issue I was having. Could some one explain to me why it is important, when to use it, and what it exactly achieves? Personally, I see no point in extra work for no gain, but I would like to be proven wrong!

~Frab

 

[MARK858]

Not necessary but helpful as when coding as it detects spelling errors in your variables, therefore if your code will error when run or you have missed declaring a variable (obviously only helpful if you choose to declare your variables)

 

[MARK858]

Bill Jelen's take on it

When It Makes Sense to Use Option Explicit

It seems that I have caused some angst over the years because of my controversial stance that Option Explicit is not useful in VBA. I was reminded of this recently when I did a Skype session at Excel Weekend of Brazil. I've written a book on Excel VBA for QUE. That book has been translated to...

www.mrexcel.com

 

[myall_blues]

Like you I'm self-taught and have been coding in VBA for a long time.

I find it useful because if you use the Excel VBA editor, option explicit forces you to declare the variables, and once that's done the editor takes care of all the formatting and highlights any errors. For example, if I declare a variable such as:
Dim lastRow as long
then in the subsequent code I can type lastrow in any mix of caps or lowercase I like and it will correct it. And as @MARK858 has already pointed out if I make a typo it will give me an error at compile time instead of an unexpected outcome when the code executes.

 

[Georgiboy]

IMO it's good practice, it helps me with debugging due to the variables being forcefully defined 'properly', for example:

If you run the below, the error arrives on the msgbox line and may leave you thinking, why? Especially if you assigned the value to 'rNum' earlier in the code.

Sub test2()
    rNum = "Hi"
    ' lots of other code...
    MsgBox rNum + 1 ' code fails here
End Sub

However, if you run the below then the error arrives when assigning the value to 'rNum', this makes it more logical to me as to what is causing the error before you get to the msgbox part:

Sub test4()
    Dim rNum As Integer
    
    rNum = "Hi" ' code fails here
    ' lots of other code
    MsgBox rNum + 1
End Sub

I find using it more beneficial than not using it.

 

[Joe4]

Not necessary, but highly recommended.
I turn it on and use it all the time.
I am not a good typist, and it catches all my variable typos, and saves me a bunch of debugging headaches!