VBA Option Explicit

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

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.

Table of contents

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.

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.

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.


    example 2.2

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


    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.

    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!