VBA Option Explicit

Excel VBA Option Explicit

Option Explicit in VBA is used to make defining variables mandatory while writing codes. It is possible that the developer may misspell a variable or forget to define a variable in his code. The code will not show an error and it will run but we will get a result. If we have multiples of code like thousands of lines of code and we have only very few variables which remain undefined or misspelled then it will be a very tiresome task to check each line of code over and over again. To not encounter such situation, VBA has provided with a solution called as Option Explicit Statement. This statement is present on the top of the code, therefore, it makes mandatory for any user to define the variable. Also, another important feature of this statement is that it highlights the variable which is not defined.

Methods to Use Option Explicit in VBA

Now there are two methods to use this function in VBA.

Method #1

The first method is that we can simply write a statement option explicit on the top of our subprocedure and proceed with the rest of our code such as shown in the image below.

We can see a blue statement written as Option Explicit above. Now if we run the code we get an error as follows.

Method #2

Now it does not mean that we have to write the statement Option Explicit on every page of our code. We can set some instructions for VBA which will automatically insert this statement to all of our modules. Follow the steps shown below:

Step 1: From the headers of the VB Editor click on Tools Tab, from the options which are shown click on Options as shown below.

Step 2: After clicking on Options the wizard box open as shown below.

Step 3: Select the option for “Require Variable Declaration” and then press OK.

Restart the module to see the changes in the VB Editor. Now in any module, Option Explicit option will be available in the VB Editor.

How to Use Excel VBA Option Explicit Statement?

We will learn how to use a VBA Option Explicit statement with few examples in Excel.

Excel VBA Option Explicit – Example #1

Now let us first use this VBA option explicit statement in a basic macro which is from a command button. Follow the below steps to use VBA Option Explicit Statement:

Step 1: Go to the Developer’s tab, find the Controls section and then click on Insert as shown below.

Step 2: Insert a new Command Button from Active X controls as shown below.

Step 3: Draw a command button over the sheet anywhere as shown below.

Step 4: Now double click on the command button which will open the VB Editor code window for the button as follows,

Code:

Private Sub CommandButton1_Click()

End Sub

Step 5: Now let us put a sample code in it as shown below.

Code:

Private Sub CommandButton1_Click()

A = 5
MsgBox 5

End Sub

Step 6: Now from the developer’s tab click on the Design Mode which will enable the button to be clicked.

Step 7: Now run the above code by clicking on the command button from the worksheet and see the result as follows.

Step 8: Now let us use the statement, Option Explicit before our subprocedure as shown below.

Code:

Option Explicit

Private Sub CommandButton1_Click()

A = 5
MsgBox 5

End Sub

Step 9: Now, click the command button again and we can see the compile error as follows.

It also even highlights the variable which has not been defined.

Excel VBA Option Explicit – Example #2

Now instead of writing this statement before every code let us instruct excel to make mandatory to define variables from the method explained above. Follow the below steps to use VBA Option Explicit statement.

Step 1: Insert a new module from the Insert tab as shown below.

We can see our module in the project tab of VBA as shown below.

Step 2: Now write the following code in the module we just inserted.

Code:

Sub Example2()

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

End Sub

We can see that we have written our code correctly and defined the variable using the Dim Statement so technically we should have A1 value in the worksheet as 10.

Step 3: Run the code by pressing F5 key and see the result.

There is no value in cell A1 and our code didn’t give us an error too. These things are very easily missed. If we look closely to our code we can see that I have misspelled my variable in the code.

Step 4: Now let us enable the option to require defining variables from the tools section.

Code:

Option Explicit

Sub Example2()

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

End Sub

Step 5: Run the code by pressing F5 key and see the result.

Now excel shows us the mistake that mVar variable is not defined.

Step 6: Now correct the mistake.

Code:

Option Explicit

Sub Example2()

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

End Sub

Step 7: Run the code by pressing the F5 key or by clicking on the Run button and see the result in sheet 1 cell A1 as follows.

Things to Remember

• Option Explicit statement makes VBA mandatory to check whether the user has defined every variable or not.
• It returns a compile error whenever an undefined variable is found.
• VBA Option Explicit statement also highlights the undefined variable.
• It is highly recommended to use Option Explicit statement in our every VBA Projects.

This is a guide to VBA Option Explicit. Here we discussed how to use Option Explicit Statement in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Save As
2. VBA XML
3. VBA DateValue
4. VBA Variable Declaration