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:

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.

Leave A Comment

Excel meets AI – Boost your productivity like never before!

At Formulas HQ, we’ve harnessed the brilliance of AI to turbocharge your Spreadsheet mastery. Say goodbye to the days of grappling with complex formulas, VBA code, and scripts. We’re here to make your work smarter, not harder.

Related Articles

The Latest on Formulas HQ Blog