Implicit and Explicit declaration of variables in VBA

.

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

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

Option Explicit Variable 2

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

0 Comments

Trackbacks/Pingbacks

  1. Learn Excel Macro Important : Declaring multiple Variables with one Dim Statement - [...] Hello friends,   As you know in VBA variables are declared using Dim Statement. You have also learnt about…

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest