WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Option Explicit

VBA Option Explicit

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel VBA Option Explicit

Declaration of variables are 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 execution of the code, we can write the keyword option explicit or we can enable it for all codes from options by enabling to require variable declaration.

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.

What is VBA Option Explicit?

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

option explicit 1

At the start of learning VBA, I too didn’t understand what that was, and to be very frank, I didn’t even think about this at all. 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 I run this code, we will get the value of a variable “I” in the message box in VBA.

option explicit example 1.1

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

option explicit example 1.3

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

option explicit example 1.4

We got a 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.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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.

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.

Step 1: Go to Visual basic editor.

Step 2: Go to TOOLS and click on Options.

option explicit example 2.1

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

example 2.2

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

example 2.3

Step 5: Click on OK to close the window.

From now onwards, 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. 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

example 3.1

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 a result of a message box. But see what happens.

example 3.3

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 on 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 the typo error I 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 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
  • VBA TimeValue
  • Class Modules in VBA
  • Call Sub in VBA
3 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Option Explicit Excel Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More