Table of Contents
Option Explicit
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 a command button on your worksheet and add the following code lines:
Dim myVar As Integer
myVar = 10
Range(“A1”).Value = mVar
Result when you click the command button on the sheet:
Clearly 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.
Result:
1. Click OK. Then Click on Reset (Stop) to stop the debugger.
2. Correct mVar so that it reads myVar.
Result when you click the command button on the sheet:
Now you know why you should always use Option Explicit at the start of your Excel VBA code. It avoids incorrectly typing the name of an existing variable.
Fortunately you can instruct Excel VBA to automatically add Option Explicit.
1. In the Visual Basic Editor click on Tools and then click on Options.
2. Check Require Variable Declaration.
Note: Option Explicit will not be added automatically to existing Excel files. Simply type in Option Explicit yourself if you want to use it.
