Hello friends,
 
In the Article related to variables in VBA, I had explained following things:

1. What is Variable ?
2. Importance of Variable in Excel Programming
3. Important Rules of Variables, you must know, before declaring a Variable
4. Different Types of Variables
5. How to Declare / Initialize and Assign value to a variable
5. Scope and Lifetime of an Excel Variable
 
While doing the Excel Macro programming you might have come across few names like Implicit or Explicit variable declaration. In this article I am going to explain you:
 

1. What is Implicit and Explicit variable declaration?
2. What is the difference in it?
3. What is pros and cons of using Implicit or Explicit options in variable declaration.

Like other Programming Languages one can declare variables to store data, value, array etc. temporarily. Instead of declaring variable to store information temporarily you can store them in the Excel spreadsheet itself, but this is not a good idea. People are intend to change the spreadsheet, variable data may get deleted or changed in the spreadsheet which will lead to crash of your program or provide unexpected result. Therefore this is not a good practice.
 
VBA allows a great deal of flexibility in declaring variable. Many time this flexibility leads to poor quality of coding. In this article I am going to guide you what kind of practices you should embrace while declaring a variable in VBA. This is a guidance to make you perfect in variable declaration techniques :). Though these practices may lead you writing more lines of codes but they will make your life easy and code faster.
 
By default in VBA, declaring a variable before using it in your program, is not necessary. It means, if you have not declared any variable before using it, VBA by default declares it as a variable of Variant Type. If the compiler encounters a word that it doesn’t recognize as one of the VBA reserved key or a property or method of a referenced typelib, it will create a new variable by that word.
 
For example: In your VBA code you have used a statement like tempval=6. Since tempval is not a reserved char in VBA, VBA will consider this as a variable of type variant and it will assign the value 6 to tempval. It will function properly. But this practice is not recommendable. Now we will see why this practice is not recommendable. Assume that you have a 1000 lines of code and your variable tempval is used at many places and involved in many of the calculations. At one place somewhere in your code by mistake you put the variable name as tempval instead of tempval. VBA code will not throw any error. It will again do the same thing. It will consider temval as another variable. Since you are thinking that you have used only one variable tempval, spelling mistake may be overlooked and VBA will also not throw any error. Therefore debugging of such issue becomes difficult. It will lead to crash of your program or resulting to an unexpected output. Although such issues are easy to fix when found but they are difficult to find. This is called Implicit method of using a variable.
 

Use of Option Explicit

You can prevent this by declaring a variable before using it. In VBA a variable can be declared using Dim statement. To know more about variables click here to read this article..
Dim tempval as Integer
 
Just declaring this variable will not help. at the top of the module where you are using the variable need to type the below statement:
 
Option Explicit
 
This statement make sure that all the variables used in that particular module is declared before being used. If any variable is used anywhere in the program without declaration then VBA will throw an error. This is called Explicit declaration of variable. 
Coming to the above mentioned issues, when compiler encounters the misspelled variable temval, VBA will throw an error about the same and becomes easy easy for you to fix this issue. Code will not be executed until it is fixed.
Now we know using Option Explicit will solve the above problem.
 

Note: (Important)

Declaration of Option Explicit is limited to that module only where it is written on the top. It means if you want to do Explicit declaration of variables then you need to place this statement “Option Explicit” at the top of the module code. This is not applicable for the Global Variables.. Once it is declared it will be used through out of the Excel Workbook programming.
 
While writting the code in any code module you may miss to place the “Option Explicit” in the declaration section. Or you may get lazy to place this declaration every time. There here is the solution: Using the below settings, you will get rid of this declaration to put in declaration section. As soon as you open any new code module automatically “Option Explicit” will be placed.

Option Explicit Variable

Steps:

1. Open VB Code Editor of the Excel Workbook
2. Go to Tools –> Options…(refer the above image)
3. Check the Checkbox “Require Variable Declaration” on the window.(refer the above image)
4. Click Ok.

Option Explicit Variable 2