spot_img

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:

Without Option Explicit in Excel VBA

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.

Use Option Explicit

Result:

Variable not Defined

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:

With Option Explicit in Excel VBA

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.

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.

spot_img
Previous articleWorkbook and Worksheet Object in Excel VBA
Next articleRead Data from a Text File using Excel VBA