{"id":896,"date":"2018-06-30T19:07:20","date_gmt":"2018-06-30T19:07:20","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=896"},"modified":"2024-03-16T13:26:01","modified_gmt":"2024-03-16T13:26:01","slug":"variables","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/training\/variables\/","title":{"rendered":"Variables"},"content":{"rendered":"\n<p>Because VBA is a programming language, it shares common principles and elements with other languages. One of them is variables.<\/p>\n\n\n\n<p>A variable is a reserved place in a computer memory that can be used by a program. Excel stores there the value assigned to that variable.<\/p>\n\n\n\n<h2 class=\"format wp-block-heading\">Declaring Variables<\/h2>\n\n\n\n<p>When you declare the variable, you can define what kind of data you want to store there. For example:<\/p>\n\n\n\n<h3 class=\"format wp-block-heading\">With data type<\/h3>\n\n\n\n<pre class=\"wp-block-preformatted lang:vb decode:true\">Dim myVal As Integer<\/pre>\n\n\n\n<p>It means that you reserve 2 bytes for the integer value.<\/p>\n\n\n\n<h3 class=\"format wp-block-heading\">Without data type<\/h3>\n\n\n\n<p>If you declare value without <a href=\"https:\/\/officetuts.net\/excel\/training\/data-types\/\">data type<\/a><\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:vb decode:true\">Dim myVal<\/pre>\n\n\n\n<p>VBA will automatically create its default type called&nbsp;<strong>Variant.<\/strong>&nbsp;This data type can be very handy because it changes its type depending on what you actually do with the variable. For example, if you enter \u201e56\u201d, it can behave as a number or as a text string.<\/p>\n\n\n\n<h2 class=\"format wp-block-heading\">Declaring multiple variables at once<\/h2>\n\n\n\n<p>When you want to declare multiple variables, you can create each declaration in a new line.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:vb decode:true\">Dim first As Integer\nDim second As Double\nDim third As Single<\/pre>\n\n\n\n<p>But there is another faster method where you need only one line of code.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:vb decode:true\">Dim first As Integer, second As Double, third As Single\n<\/pre>\n\n\n\n<p>If all of your variables are of the same type, you still have to declare each variable separately.<\/p>\n\n\n\n<div>\n<div class=\"toolbar\">\n<pre class=\"lang:vb decode:true \">Dim first As Integer, second As Integer, third As Integer\n<\/pre>\n<p>The following code will be valid.<\/p>\n<pre class=\"lang:vb decode:true \">Dim first, second, third As Integer<\/pre>\n<p>But only the&nbsp;<em>third<\/em> variable will be treated as an integer. The first and second variables will be treated as Variants.<\/p>\n<h2 class=\"format\">Option Explicit<\/h2>\n<p>It\u2019s a good practice to declare all your variables at the beginning of your code, not only for practical reasons but also because the macro will run faster. If you don&#8217;t do this it is easier to make a mistake.<\/p>\n<p>Look at this simple code:<\/p>\n<pre class=\"nums:true lang:vb decode:true \">Public myVal As Integer\n \nSub mySub()\nmyVal = 45\nmyVar = myVal + 3\nMsgBox myVal\n \nEnd Sub<\/pre>\n<p>We expect from the&nbsp;<em>myVal<\/em>&nbsp;variable to display the value 48. But the result is:<\/p>\n<figure><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-897\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/msgbox.png\" alt=\"\" width=\"154\" height=\"154\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/msgbox.png 154w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/msgbox-150x150.png 150w\" sizes=\"(max-width: 154px) 100vw, 154px\" \/><\/figure><p><\/p>\n<p>That because there is a mistake in the code. Instead of&nbsp;<em>myVal = myVal + 3<\/em>&nbsp;there is&nbsp;<em>myVar = myVal + 3<\/em>. It can be easily overlooked and the result will be wrong.<\/p>\n<p>If you declare the<\/p>\n<pre class=\"lang:vb decode:true \">Option Explicit<\/pre>\n<p>at the very beginning of your code, Excel will require from you to declare each variable you use. Now when you execute your code you get the error informing you that the variable is not defined.<\/p>\n<figure><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-898\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/compile-error.png\" alt=\"\" width=\"259\" height=\"184\"><\/figure><p><\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Because VBA is a programming language, it shares common principles and elements with other languages. One of them is variables. A variable is&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[188],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Variables<\/title>\n<meta name=\"description\" content=\"Learn about declaring variables in VBA programming language. This article covers data types and how to reserve memory space for values.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/training\/variables\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Variables\" \/>\n<meta property=\"og:description\" content=\"Learn about declaring variables in VBA programming language. This article covers data types and how to reserve memory space for values.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/training\/variables\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-30T19:07:20+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-16T13:26:01+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/msgbox.png\" \/>\n<meta name=\"author\" content=\"Tomasz Decker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tomasz Decker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/training\/variables\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/training\/variables\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"Variables\",\"datePublished\":\"2018-06-30T19:07:20+00:00\",\"dateModified\":\"2024-03-16T13:26:01+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/training\/variables\/\"},\"wordCount\":356,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/training\/variables\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/msgbox.png\",\"keywords\":[\"added\"],\"articleSection\":[\"training\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/training\/variables\/\",\"url\":\"https:\/\/officetuts.net\/excel\/training\/variables\/\",\"name\":\"Variables\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/training\/variables\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/training\/variables\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/msgbox.png\",\"datePublished\":\"2018-06-30T19:07:20+00:00\",\"dateModified\":\"2024-03-16T13:26:01+00:00\",\"description\":\"Learn about declaring variables in VBA programming language. This article covers data types and how to reserve memory space for values.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/training\/variables\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/training\/variables\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/training\/variables\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/msgbox.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/msgbox.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/training\/variables\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Variables\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Variables","description":"Learn about declaring variables in VBA programming language. This article covers data types and how to reserve memory space for values.","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:\/\/officetuts.net\/excel\/training\/variables\/","og_locale":"en_US","og_type":"article","og_title":"Variables","og_description":"Learn about declaring variables in VBA programming language. This article covers data types and how to reserve memory space for values.","og_url":"https:\/\/officetuts.net\/excel\/training\/variables\/","article_published_time":"2018-06-30T19:07:20+00:00","article_modified_time":"2024-03-16T13:26:01+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/msgbox.png"}],"author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/training\/variables\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/training\/variables\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"Variables","datePublished":"2018-06-30T19:07:20+00:00","dateModified":"2024-03-16T13:26:01+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/training\/variables\/"},"wordCount":356,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/training\/variables\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/msgbox.png","keywords":["added"],"articleSection":["training"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/training\/variables\/","url":"https:\/\/officetuts.net\/excel\/training\/variables\/","name":"Variables","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/training\/variables\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/training\/variables\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/msgbox.png","datePublished":"2018-06-30T19:07:20+00:00","dateModified":"2024-03-16T13:26:01+00:00","description":"Learn about declaring variables in VBA programming language. This article covers data types and how to reserve memory space for values.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/training\/variables\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/training\/variables\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/training\/variables\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/msgbox.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/msgbox.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/training\/variables\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Variables"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/896"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=896"}],"version-history":[{"count":4,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/896\/revisions"}],"predecessor-version":[{"id":13843,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/896\/revisions\/13843"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=896"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=896"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=896"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}