In the VBA Editor window, from the Tools menu select "Options":
Then in the "Editor" tab, make sure that "Require Variable Declaration" is checked:
Selecting this option will automatically put
Option Explicit at the top of every VBA module.
Small note: This is true for the modules, class modules, etc. that haven't been opened so far. So if you already had a look at e.g. the code of
Sheet1before activating the option "Require Variable Declaration",
Option Explicitwill not be added!
Option Explicit requires that every variable has to be defined before use, e.g. with a
Dim statement. Without
Option Explicit enabled, any unrecognized word will be assumed by the VBA compiler to be a new variable of the
Variant type, causing extremely difficult-to-spot bugs related to typographical errors. With
Option Explicit enabled, any unrecognized words will cause a compile error to be thrown, indicating the offending line.
If you run the following code :
Sub Test() my_variable = 12 MsgBox "My Variable is : " & myvariable End Sub
You will get the following message :
You have made an error by writing
myvariable instead of
my_variable, then the message box displays an empty variable.
If you use
Option Explicit , this error is not possible because you will get a compile error message indicating the problem.
Now if you add the correct declaration :
Sub Test() Dim my_variable As Integer my_variable = 12 MsgBox "My Variable is : " & myvariable End Sub
You will obtain an error message indicating precisely the error with
Note on Option Explicit and Arrays (Declaring a Dynamic Array):
You can use the ReDim statement to declare an array implicitly within a procedure.
Be careful not to misspell the name of the array when you use the ReDim statement
Even if the Option Explicit statement is included in the module, a new array will be created
Dim arr() as Long
ReDim ar() 'creates new array "ar" - "ReDim ar()" acts like "Dim ar()"