excel-vba VBA Best Practices ALWAYS Use "Option Explicit"
Example
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.
Example :
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 myvariable :
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()"
