VBA Option Explicit

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.

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

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

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

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

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.

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

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

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.

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.

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: -

• VBA Wait Function
• VBA TimeValue
• Class Modules in VBA
• Call Sub in VBA