Use of Option Explicit in Excel VBA

At times, we have frequently asked that what Option Explicit Statement is? in the Excel VBA and what it does? and what is the use of it?

We are going  to explain all the queries here:

Option Explicit is a statement used in VBA programming and it always declares at the top of the code. When Option Explicit appears in a file, you must explicitly declare all variables by using the Dim or ReDim statements. If you try to use an undeclared variable name, an error occurs at compile time.

We strongly recommend to use Option Explicit at the start of your Excel VBA code. Using Option Explicit forces you to declare all your variables.

For example, place the following code lines in you VBA Editor and run the program:

Option Explicit

Sub Option_Explicit()

    Dim myVar As Integer
    myVar = 10
    Range("A1").Value = mVar

End Sub

As a result you will get a "Compile error: Variable not defined" error message pop-up:

This error pops-up as, cell A1 does not contain the value 10. That is because we accidentally misspelled myVar. As a result, Excel VBA places the value of the undeclared, empty variable mVar into cell A1.

When using Option Explicit, the code lines above generate an error because we did not declare the variable mVar.

To fix such error, follow the following steps:

1. Click OK on the error box (as shown in above screenshot) and Stop the debugging.
   
2. Correct the variable name from mVar to myVar so that it reads what we have declared.
3. Run the program or code again

You'll get following result on the excel sheet, on which you have run this program:

Now, I hope you know why you should always use Option Explicit at the start of your Excel VBA code as it avoids incorrectly typing the name of an existing variable.

Fortunately, you can instruct Excel VBA to automatically add Option Explicit.