Option Explicit how to turn it off

[nehpets12]

When I record a new macro my first line always comes up with Option Explicit.

I know it is something I turned on a long time ago but I cant remember where it is or how to turn it off.

[phantom1975]

REQUIRE VARIABLE DELCARATION in the OPTIONS menu in VB.

[Tom Urtis]

Before you go without the Option Explicit statement, take a moment to carefully consider that decision.

The term "Option Explicit" means that the VBA code author must define the names of all the variables being used or referred to in the code. Not doing so is called "implicit variable declaration", where you can name a variable without explicitly defining it.

Why should you explicitly declare variables?

Option Explicit at the top of a module forces every variable to be declared. There are several reasons why this is a good idea to maintain:

- Spelling errors are identified.
- Your variables can be properly declared by you, not Excel, to have the appropriate memory and system resources assigned to accommodate them. Excel will assign Variant as the variable default type for undeclared variables, the most inefficient use of resources.
- Confusion is avoided if a variable is the same name as a property or method.

Consider the following code:

Sub ShowData()
FindData = InputBox("Please enter your search term:")
If FineData = "" Then Exit Sub
Range("A1").Value = FindData
End Sub

Without Option Explicit to guard against spelling errors, cell A1 will never have a value returned into it because of the spelling inconsistency between FindData (line 2) and FineData (line 3). The macro will always exit because FineData will always be thought of as empty.

This kind of mistake might be noticeable with a macro this size, but if it is part of a larger procedure these mistakes are hard to identify. Why take the chance?

Keeping Option Explicit as the default statement is highly recommended; it forces better code writing practice, and helps avoid coding errors.