excel-vba ALWAYS Use "Option Explicit"


Example

In the VBA Editor window, from the Tools menu select "Options":

enter image description here

Then in the "Editor" tab, make sure that "Require Variable Declaration" is checked:

enter image description here

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 Sheet1 before activating the option "Require Variable Declaration", Option Explicit will 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 :

enter image description here

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.

enter image description here

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 :

enter image description here


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()"