{"id":16726,"date":"2023-11-06T18:05:57","date_gmt":"2023-11-06T18:05:57","guid":{"rendered":"https:\/\/officetuts.net\/excel\/?p=16726"},"modified":"2024-02-19T15:00:01","modified_gmt":"2024-02-19T15:00:01","slug":"get-the-length-of-an-array-in-vba","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/","title":{"rendered":"How to Get the Length of an Array in VBA"},"content":{"rendered":"\n<p>The VBA programming language provides a variety of tools for automating tasks in Excel and other Office products. One such tool is the array, a data structure that can hold multiple values. This tutorial will show you how to get the length of an array in VBA. This technique is useful when you need to loop through all the elements of an array or perform some computation based on the number of elements in the array.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 1: Declare an Array<\/h3>\n\n\n\n<p>Before we get the length of an array, let&#8217;s first declare one. VBA uses the <strong>Dim<\/strong> keyword to declare an array. The size of the array is provided in parentheses. Here&#8217;s an example of how to declare an array with 5 elements in VBA:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Dim arr(5) As Integer<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Step 2: Assign Values to the Array<\/h3>\n\n\n\n<p>Next, let&#8217;s assign some values to our array. This can be done using the array index, which starts from zero:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>arr(0) = 1\narr(1) = 2\narr(2) = 3\narr(3) = 4\narr(4) = 5<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Step 3: Get the Length of the Array<\/h3>\n\n\n\n<p>To get the length of the array, we will use the <strong>UBound<\/strong> function in VBA. &#8216;UBound&#8217; stands for &#8216;Upper Bound&#8217;, and it returns the highest index in the array. To get the size, you add 1 to the result (because the array index starts at zero). Here&#8217;s the code:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Dim length As Integer\nlength = UBound(arr) + 1<\/code><\/pre>\n\n\n\n<p>Now variable &#8216;length&#8217; holds the number of elements in the array.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Full Code<\/h2>\n\n\n\n<p>Here&#8217;s the full VBA code showing how to declare an array, assign values, and then get the array&#8217;s length:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Sub GetLength()\r\n    Dim arr(5) As Integer\r\n    arr(0) = 1\r\n    arr(1) = 2\r\n    arr(2) = 3\r\n    arr(3) = 4\r\n    arr(4) = 5\r\n    \r\n    Dim length As Integer\r\n    length = UBound(arr) + 1\r\n    MsgBox length\r\nEnd Sub\r<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"127\" height=\"133\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/get-the-length-of-an-array-in-vba.png\" alt=\"\" class=\"wp-image-16729\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p>Getting the length of an array in VBA is a simple process once you understand how arrays are indexed in VBA. This method can be useful in several scenarios, such as when iterating through the array elements or when performing computations based on the number of elements in the array.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The VBA programming language provides a variety of tools for automating tasks in Excel and other Office products. One such tool is the&#8230;<\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to Get the Length of an Array in VBA<\/title>\n<meta name=\"description\" content=\"Learn how to get the length of an array in VBA. The array is declared and values are assigned to it before computing the length.\" \/>\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\/vba\/get-the-length-of-an-array-in-vba\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Get the Length of an Array in VBA\" \/>\n<meta property=\"og:description\" content=\"Learn how to get the length of an array in VBA. The array is declared and values are assigned to it before computing the length.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/\" \/>\n<meta property=\"article:published_time\" content=\"2023-11-06T18:05:57+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-02-19T15:00:01+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/get-the-length-of-an-array-in-vba.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\/vba\/get-the-length-of-an-array-in-vba\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/10baa1deb54627da5d59757d6924066e\"},\"headline\":\"How to Get the Length of an Array in VBA\",\"datePublished\":\"2023-11-06T18:05:57+00:00\",\"dateModified\":\"2024-02-19T15:00:01+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/\"},\"wordCount\":303,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/get-the-length-of-an-array-in-vba.png\",\"articleSection\":[\"vba\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/\",\"url\":\"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/\",\"name\":\"How to Get the Length of an Array in VBA\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/get-the-length-of-an-array-in-vba.png\",\"datePublished\":\"2023-11-06T18:05:57+00:00\",\"dateModified\":\"2024-02-19T15:00:01+00:00\",\"description\":\"Learn how to get the length of an array in VBA. The array is declared and values are assigned to it before computing the length.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/#primaryimage\",\"url\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/get-the-length-of-an-array-in-vba.png\",\"contentUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/get-the-length-of-an-array-in-vba.png\",\"width\":127,\"height\":133},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Get the Length of an Array in VBA\"}]},{\"@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.\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/10baa1deb54627da5d59757d6924066e\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d92ef5a1e35bf0d44baf479313c76713?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d92ef5a1e35bf0d44baf479313c76713?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Get the Length of an Array in VBA","description":"Learn how to get the length of an array in VBA. The array is declared and values are assigned to it before computing the length.","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\/vba\/get-the-length-of-an-array-in-vba\/","og_locale":"en_US","og_type":"article","og_title":"How to Get the Length of an Array in VBA","og_description":"Learn how to get the length of an array in VBA. The array is declared and values are assigned to it before computing the length.","og_url":"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/","article_published_time":"2023-11-06T18:05:57+00:00","article_modified_time":"2024-02-19T15:00:01+00:00","og_image":[{"url":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/get-the-length-of-an-array-in-vba.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\/vba\/get-the-length-of-an-array-in-vba\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/10baa1deb54627da5d59757d6924066e"},"headline":"How to Get the Length of an Array in VBA","datePublished":"2023-11-06T18:05:57+00:00","dateModified":"2024-02-19T15:00:01+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/"},"wordCount":303,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/get-the-length-of-an-array-in-vba.png","articleSection":["vba"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/","url":"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/","name":"How to Get the Length of an Array in VBA","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/get-the-length-of-an-array-in-vba.png","datePublished":"2023-11-06T18:05:57+00:00","dateModified":"2024-02-19T15:00:01+00:00","description":"Learn how to get the length of an array in VBA. The array is declared and values are assigned to it before computing the length.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/#primaryimage","url":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/get-the-length-of-an-array-in-vba.png","contentUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/11\/get-the-length-of-an-array-in-vba.png","width":127,"height":133},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/vba\/get-the-length-of-an-array-in-vba\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"How to Get the Length of an Array in VBA"}]},{"@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."},{"@type":"Person","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/10baa1deb54627da5d59757d6924066e","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d92ef5a1e35bf0d44baf479313c76713?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d92ef5a1e35bf0d44baf479313c76713?s=96&d=mm&r=g","caption":"Tomasz Decker"}}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/16726"}],"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\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=16726"}],"version-history":[{"count":3,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/16726\/revisions"}],"predecessor-version":[{"id":16966,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/16726\/revisions\/16966"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=16726"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=16726"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=16726"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}