FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

VBA Option Explicit

Updated on June 11, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA Option Explicit

Declaration of variables is very important in VBA. Option Explicit makes a user mandatory to declare all the variables before using them. Any undefined variable will throw an error while executing the code. We can write the keyword option explicit or enable it for all codes from options by enabling variable declaration.

In VBA, it is all about variables. To store data, we need variables with a suitable data type. You can question us why you need variables when you can directly add the value to the spreadsheet itself. It is mainly because of the multiple users of the workbook. If one person handles it, you can directly reference the value to the sheet. Therefore, we can make a flexible code to store the data by declaring variables.

What is VBA Option Explicit?

We hope you have come across the blue-lined word “Option Explicit” at the top of your module before any Macro starts in that module.

option explicit 1

At the start of learning VBA, we also didn’t understand what that was, and to be very frank, we didn’t even think about this. So not only for you or me, but it is also the same for everybody at the start. But we will see the importance of this word now.

“Option Explicit” is our mentor in declaring the variable. By adding this word, it makes the variable declaration a mandatory process.

You can download this VBA Option Explicit Excel Template here – VBA Option Explicit Excel Template

For example, look at the below code for understanding.

Code:

Sub Example1()

     i = 25

     MsgBox i

End Sub
option explicit example 1

If we run this code, we will get the variable “I” value in the message box in VBA.

option explicit example 1.1

Now, we will add the word “Option Explicit” at the beginning of the VBA code.

option explicit example 1.3

Now, we will run the code and see what happens. If you practice with us, press the F5 key to run the code.

option explicit example 1.4

We got a compile error, saying, “Variable not defined.” That is why we have not declared the variable “i,” but straight away, we have assigned the value to it as 25.

Since we have added the word “Option Explicit,” it forces us to declare the variable compulsorily.

In the above code, the alphabet “i” is undeclared, so we have added the variable controller word “Option Explicit” which prevents us from using the undeclared variables.

The moment you add the word “Option Explicit” at the top of the module, it is applicable for all the macros in that particular module to declare the variables mandatorily.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

How to Make the Variable Declaration Mandatory?

If you have manually added the variable mentor “Option Explicit” in your module when you insert the new module, you will not get this variable mentor by default.

option explicit example 2.5
option explicit example 2.4

If you think every time you need to add the word “Option Explicit” for all the new modules manually, then you are wrong.

Because we can make this word mandatory in all the modules by doing a simple setting, follow the below steps to adjust the settings.

  1. Go to Visual basic editor.

  2. Go to TOOLS and click on “Options.”


    option explicit example 2.1

  3. When you click “Options,” you will see the window below.


    option explicit example 2.2

  4. Under this window, go to “Editor” and tick the option “Require Variable Declaration.”


    option explicit example 2.3

  5. Click on “OK” to close the window.


    Whenever you add a new module, it automatically inserts the word “Option Explicit” by default.

    option explicit example 2.6

Option Explicit is your Saver

Option Explicit helps us in many ways. Making the declaration of variables mandatory will help us until the execution. Look at the below code.

Code:

Sub Example2()
Dim CurrentValue As Integer

CurentValue = 500

MsgBox CurrentValue

End Sub
example 3.1

We have declared the variable “CurrentValue” in the above code as an integer. In the next line, we have assigned the value of 500 to it. So, if we run this code, we should get 500 because of a message box. But see what happens.

example 3.3

It says “Variable not defined” and highlights the second line.

If we closely look at the second line, there is a slight spelling mistake. My variable name is “CurrentValue,” but in the second line, we have missed out on one spelling, i.e., “r.” So, it says “CurrentValue” instead of “CurrentValue.” Since we have made the variable declaration mandatory by adding the word “Option Explicit” in Excel VBA, it has highlighted the typo error we have made.

So, when we correct the spelling and run the code, we will get the result as follows.

example 3.2

Say hello to the newly appointed variable mentor!

Recommended Articles

This article has been a guide to VBA Option Explicit. Here, we discuss the Option Explicit in VBA and how to make variable declaration mandatory with details. You can learn more about VBA from the following articles: –

Reader Interactions

Comments

  1. Arjun Singh says

    Super Sir…Thanks a lot for your kind support, really you are our Hero.

    • Dheeraj Vaidya says

      Thanks for your kind words!