{"id":12163,"date":"2024-02-03T05:00:56","date_gmt":"2024-02-03T05:00:56","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=2464"},"modified":"2024-02-03T05:00:56","modified_gmt":"2024-02-03T05:00:56","slug":"option-explicit-declaration-of-variables-in-macro","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/","title":{"rendered":"Implicit and Explicit declaration of variables in VBA"},"content":{"rendered":"

Hello friends,
\n 
\nIn the Article related to variables in VBA<\/a>, I had explained following things:
\n
\n1. What is Variable ?
\n2. Importance of Variable in Excel Programming
\n3. Important Rules of Variables, you must know, before declaring a Variable
\n4. Different Types of Variables
\n5. How to Declare \/ Initialize and Assign value to a variable
\n5. Scope and Lifetime of an Excel Variable<\/a><\/font>
\n 
\nWhile 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:
\n 
\n
\n1. What is Implicit and Explicit variable declaration?
\n2. What is the difference in it?
\n3. What is pros and cons of using Implicit or Explicit options in variable declaration.<\/strong><\/font>
\n
\nLike 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.
\n 
\nVBA 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.
\n 
\nBy 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<\/font>. 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.
\n 
\nFor example:<\/font> In your VBA code you have used a statement like tempval=6<\/font>. Since tempval<\/font> is not a reserved char in VBA, VBA will consider this as a variable of type variant<\/font> 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<\/font> 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<\/font> instead of tempval<\/font>. VBA code will not throw any error. It will again do the same thing. It will consider temval<\/font> as another variable. Since you are thinking that you have used only one variable tempval<\/font>, 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.<\/font>
\n <\/p>\n

Use of Option Explicit<\/h2>\n

You can prevent this by declaring a variable before using it. In VBA a variable can be declared using Dim <\/font> statement. To know more about variables click here to read this article.<\/a>.
\nDim tempval as Integer<\/font>
\n 
\nJust declaring this variable will not help. at the top of the module where you are using the variable need to type the below statement:
\n 
\nOption Explicit<\/font>
\n 
\nThis 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<\/font>. 
\nComing to the above mentioned issues, when compiler encounters the misspelled variable temval<\/font>, 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.
\nNow we know using Option Explicit<\/font> will solve the above problem.
\n <\/p>\n

Note: (Important)<\/h2>\n

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.<\/a>. Once it is declared it will be used through out of the Excel Workbook programming.
\n 
\nWhile 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.
\n
\n

\"Option

Option Explicit Variable<\/p><\/div><\/p>\n

Steps:<\/h3>\n

1. Open VB Code Editor of the Excel Workbook
\n2. Go to Tools –> Options…(refer the above image)<\/i>
\n3. Check the Checkbox “Require Variable Declaration” on the window.(refer the above image)<\/i>
\n4. Click Ok.
\n
\n

\"Option

Option Explicit Variable 2<\/p><\/div><\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[5204],"tags":[],"yoast_head":"\nImplicit and Explicit declaration of variables in VBA - Let's excel in Excel<\/title>\n<meta name=\"description\" content=\"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.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Implicit and Explicit declaration of variables in VBA\" \/>\n<meta property=\"og:description\" content=\"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.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2024-02-03T05:00:56+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/10\/implicit-explicit-1.png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Implicit and Explicit declaration of variables in VBA\",\"datePublished\":\"2024-02-03T05:00:56+00:00\",\"dateModified\":\"2024-02-03T05:00:56+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/\"},\"wordCount\":903,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"VBA Programming\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/\",\"name\":\"Implicit and Explicit declaration of variables in VBA - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2024-02-03T05:00:56+00:00\",\"dateModified\":\"2024-02-03T05:00:56+00:00\",\"description\":\"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.\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"VBA Programming\",\"item\":\"https:\/\/vmlogger.com\/excel\/vba-programming\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Implicit and Explicit declaration of variables in VBA\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Implicit and Explicit declaration of variables in VBA - Let's excel in Excel","description":"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.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/","og_locale":"en_US","og_type":"article","og_title":"Implicit and Explicit declaration of variables in VBA","og_description":"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.","og_url":"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2024-02-03T05:00:56+00:00","og_image":[{"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/10\/implicit-explicit-1.png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Implicit and Explicit declaration of variables in VBA","datePublished":"2024-02-03T05:00:56+00:00","dateModified":"2024-02-03T05:00:56+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/"},"wordCount":903,"commentCount":0,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["VBA Programming"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/","url":"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/","name":"Implicit and Explicit declaration of variables in VBA - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2024-02-03T05:00:56+00:00","dateModified":"2024-02-03T05:00:56+00:00","description":"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.","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/option-explicit-declaration-of-variables-in-macro\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"VBA Programming","item":"https:\/\/vmlogger.com\/excel\/vba-programming\/"},{"@type":"ListItem","position":3,"name":"Implicit and Explicit declaration of variables in VBA"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12163"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=12163"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12163\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12163"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12163"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12163"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}