VBA Option Explicit

Option Explicit in Excel VBA

In VBA it’s all about variables. In order to store data, we need variables with a suitable data type. You can question me why do you need variable when you can directly add the value to the spreadsheet itself. This is mainly because of the multiple users of the workbook, if it is handled by one person then you can directly reference the value to the sheet itself. By declaring variables we can make the flexible code to store the data. In this article, we are not discussing VBA variables and their data type but we are discussing the option “Option Explicit”.

What is Option Explicit in VBA?

I hope you have come across the blue lined word “Option Explicit” at the top your module when you before any macro started in that module.

For at the start of learning VBA, I too didn’t understand what was that and to be very frank I didn’t even think about this at all. Not only for me or you but it is also the same for everybody at the start. But we will see the importance of this option explicit word in VBA now.

“Option Explicit” is our mentor in declaring the variable. By adding this word it allows us to declare the variable mandatory process.

For an example look at the below code for understanding.

Code:

Sub Example1()

     i = 25

     MsgBox i

End Sub

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

Now I will add the word “Option Explicit” at the very beginning of the VBA code.

Now I will run the code and see what happens. If you are practicing with me press the F5 key to run the code.

We got compile error and it says “Variable not defined”. 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” it 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 added the variable mentor “Option Explicit” manually in your module when you insert the new module you won’t get this variable mentor by default.

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

Because we can make this word mandatory in all the modules of VBA by doing simple setting. Follow the below steps to adjust the settings.

Step 1: Go to Visual basic editor.

Step 2: Go to TOOLS and click on Options.

Step 3: The moment you click on Options you will see the below window.

Step 4: Under this window Go to Editor and tick the option “Require Variable Declaration”.

Step 5: Click on OK to close the window.

From now onwards whenever you add new module it automatically inserts the word “Option Explicit” by default.

Option Explicit is your Saver

VBA Option Explicit in excel helps us in many ways, right from making the declaration of variable mandatory it will help us until the execution. Look at the below code.

Code:

Sub Example2()
Dim CurrentValue As Integer

CurentValue = 500

MsgBox CurrentValue

End Sub

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

It says “Variable not defined” and highlighted 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 I have missed out one spelling i.e. “r”. It says “CurrentValue” instead of “CurrentValue”. Since I have made the variable declaration mandatory by adding the word “Option Explicit” in excel VBA it has highlighted me the typo error I have made.

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

 

Say hello to the newly appointed variable mentor!!!

Recommended Articles

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

• VBA Wait Function Examples
• TimeValue Function in VBA Excel
• DatePart in Excel VBA
• Create Class Modules in VBA
• JOIN Function in VBA
• Worksheets Objects in VBA
• How to Call Subroutine in VBA?