VBA Option Explicit

“The VBA Option Explicit statement is used to declare the variables mandatory while writing codes. It is a secure and easy option to maintain your variables”.

It is possible that while writing the code, the VBA developer may forget to define a variable or mistakenly re-write the same variable, or may misspell a variable. The code will not show an error, and it will run, but we will not get the desired output. Take an example of a multi-national company where the developer has multiple lines of code. If any variable is mistakenly forgotten or misspelled, it would become an unmanageable job to examine a thousand lines of code repeatedly and rectify the code.

To dodge the above problems, Option Explicit Statement is a great solution. Option Explicit makes it compulsory to declare all the variables on the top of the program and it even highlight the undefined variables used in your code.

The VBA Option Explicit statement enables the user to define all the variables at one point so that in the future, if the macro throws an error, you can directly cross check at the beginning of your module. With the help of Option Explicit, you can also declare the global variables.

Advantages of Option Explicit

1. The Option Explicit statement avoids typing errors while using variables. For instance, if you have declared the Option Explicit statement at the top of your code and attempted to access an undefined variable, it would show a runtime error.
2. The Option Explicit statement forces the user to declare all the variables.
3. The Option Explicit statement prevents the use of an undeclared variable that throws an error at the compile time.
4. Variant data type consumes more time, eventually making your macro slower. Sometimes, the user doesn’t declare the variable type and later uses an undefined variable in the code. For such cases, the VBA compiler picks the default variable, i.e., the variant data type. To prevent this, Option Explicit is a great solution.
5. For the undefined variable, the compiler implicitly selects the Variant data type of any variable. The Variant data type consumes the highest space of 16 bytes of data in the list of data types. Imagine every time you forget to declare your variable; it will reserve 16 bytes of memory. Therefore, Option Explicit prevents this problem and saves memory.

Methods to Implement Option Explicit

In VBA programming, there are two ways to use the Option Explicit statement.

Method #1

The first method is simple and easy to use. To use this write a statement option explicit on the top of our VBA subprocedure and continue with the rest of our code such as shown below.

As you can see in above image, the Option Explicit statement is highlighted with a blue font color. In the above code, we have declared a variable and if we run the code we get a compile error as an output since the variable is not declared.

Method #2

Many users may forget to mention the option Explicit every time they write a VBA code. VBA has provided another option using which you can set the Option Explicit as a default statement at the top of every page of your code. All you need to do is follow some set of instructions, after which VBA will automatically insert this statement to all of your modules.

Step 1: Open the VBA Developer tab. From the menu of the VB Editor click on Tools Tab. A window will pop-up (as shown below), Click on Options.

Step 2: Next, the Options wizard window will open. You will notice five different tabs in the top.

Step 3: In the Editor tab, you will notice the option for “Require Variable Declaration” and tick the checkbox and click on OK.

Once you are done with the above steps, restart your VBA module to implement the changes in the Visual Basic Editor window. After that you will notice for evert VBA module the Option Explicit statement will automatically appear at the top of the VB Editor.

Excel VBA Option Explicit – Example #1

Write a VBA code and demonstrate the steps to implement Option Explicit in the form of a command button.

VBA is an easy language and you can even make it easier by writing the statement before line of code. It permits us to instruct excel to make required to define variables at the top of the module.

Following are the below steps to implement VBA Option Explicit statement in your code:

Step 1: Open your Excel worksheet, and from the Excel, ribbon headers go to the Developer tab.-> Controls section-> click on the Insertion options.

Refer to the below image:

Step 2: The window will appear carrying all the Controls. Select the first control option from the ActiveX Controls section, i.e., the command button.

Note: A command button is an event used to insert a command control.

Step 3: Using your mouse cursor, draw the command button at any position in the excel worksheet. Drag the mouse pointer in a rectangular shape, and the command button will be drawn.

Refer to the below image:

Step 4: Double click on the command button, and it will open the VB editor window.

Step 5: As shown in the below image, write the code to display a Msgbox of value 5 whenever anyone clicks on the command button.

Code:

STEP 6: Now, again to the Developer tab, click on the Design tab to enable the button’s event.

Step 7: Click again on the command button. This time a MsgBox will pop up displaying the value 80.

Refer to the below image:

Step 8: Now, we will manually write the option explicit statement on the top of your code.

Code:

Step 9: Again, click on the command button, and this time, instead of the MsgBox you will notice the compile error pop up.

Step 10: Since we have not declared our variable, it shows compile error. It has also highlighted the variable that has not been declared. Correct the code define the variable at the top of your module.

Code:

Step 11: Click on the command button and, this time, it will show the Msgbox displaying the output.

Excel VBA Option Explicit – Example #2

Sometimes in VBA, the code runs fine, but we don’t have the desired variable. Then the developer has to re-read the entire code and find the mistake. It mostly happens because of using wrong undefined variables. In this example, we will see an occurrence and later correct it using the Option Explicit statement.

Following are the below steps to implement VBA Option Explicit statement in your code:

Step 1: Go to the VBA developer tab either by clicking the shortcut keywords Alt +F11 or click on developer window -> visual basic editor.

Step 2: The VB Editor will appear. The next step is to create a module. Right-clicking on the VBA Project-> Go to Insert-> Go to Module.

Step 2: The VBA module will be inserted. The Next step is to declare and initialize the variable with a value of 10. The code will as follow:

Code:

In the above program, we have defined our variable with Integer data types using the Dim statement. Next, in the range A1 we have put the value var. So technically, for cell A1 in the worksheet, we should have a value of 50.

Step 3: Press F5 key to run your macro and fetch the output.

As you see in the output, we don’t have any value for cell A1, and our code also hasn’t given us any error. The program is all correct, but still, we are getting an error. If you look very closely, you will notice that we have misspelled the variable in the program. These things are very easily bypassed.

Step 4: We will apply the Option Explicit, and you will immediately see the difference and importance of this statement.

Code:

Step 5: Press F5 key to run your code again and this time VBA will throw an error i.e., ‘Compile error: Argument not optional’

Refer to the below image:

Step 6: Now, find the variable and correct the mistake.

Code:

Step 7: Run the code by pressing the F5 key or and finally you will have the output. As a result, the value 50 will be inserted in sheet1 cell A1 for your Excel worksheet.

Refer to the below image:

Things to Remember

1. Option Explicit statement makes it mandatory for the developer to define every variable in his code.
2. When you insert an option explicit statement at the top of your module, VBA adds a separator line below it, and then you can begin writing your code.
3. For every undefined variable the Option Explicit returns a compile error.
4. If you don’t declare the variables, the Option Explicit statement highlights them.
5. The Option Explicit statement can be entered manually or the user also activate it as the default option.

---