{"id":244112,"date":"2022-08-24T10:44:46","date_gmt":"2022-08-24T10:44:46","guid":{"rendered":"https:\/\/vmlogger.com\/excel\/?p=244112"},"modified":"2022-08-24T14:28:45","modified_gmt":"2022-08-24T14:28:45","slug":"a-complete-guide-to-array-in-excel-vba","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/","title":{"rendered":"A Complete guide to Array in Excel VBA"},"content":{"rendered":"

A complete guide to learning Array in Excel VBA programming. If you have done programming in any language C, VB, Java, Python, etc., you must have definitely heard and known of Array. The array is a data structure that stores data in a sequence. Each of its elements can be accessed via its sequential index number. <\/p>\n

Array in VBA is also no different. The only difference, between the array of any other language and VBA, is – Array in VBA not only stores sequential data but also stores the number of dimensions and number of elements in each dimension. We will discuss more in detail other differences and the common thing about array in VBA and other languages.<\/p>\n

\n

Topics Covered<\/h3>\n
    \n
  1. What is Array in Excel VBA<\/li>\n
  2. How to declare Array in VBA<\/li>\n
  3. Different ways of declaring an array in Excel VBA<\/li>\n
  4. Dim and ReDim an Array in Excel VBA<\/li>\n
  5. What is Option Base Statement<\/li>\n
  6. Type of methods of array<\/li>\n<\/ol>\n<\/div>\n

    Like any other variable, you can declare one array by using the Dim<\/span> statement. In the case of array declaration, your array variable should have open and close parenthesis ()<\/strong>, unlike any other variable type. This is the sign that the declared variable is of array type and not scalar type.<\/p>\n

    Syntax:<\/h3>\n
    \r\nDim VariableName( [ subscript ] ) [ As type ]<\/code>\r\n<\/pre>\n
    Dim<\/strong> Dim is a keyword to define any Variable in Excel VBA. It is a short form of Dimension<\/strong>. It is used to Declare<\/strong> a variable in VBA.<\/p>\n
    VariableName<\/strong> any valid variable name you would like to declare. <\/p>\n
    subscript<\/strong> Subscript is used to define the upper bound limit of your array or both upper and lower bound limit. In VBA it is allowed to provide a lower bound limit as well. It is optional<\/strong> <\/p>\n
    Type<\/strong> This is the data type of elements inside the declared array, you are going to store. It is optional<\/strong>. The default type is Variant<\/strong>.<\/p>\n
      \n
    1. Example 1:<\/strong>
      \nTo define a lower bound and upper bound, you need to use the word to<\/strong> in between two limits as shown in the below code. The first limit<\/p>\n
      \r\nDim Arr(5 to 10) As Integer\r\n<\/pre>\n<\/li>\n
    2. Example 2:<\/strong>
      \nOnly with an upper bound limit.<\/p>\n
      \r\nDim Arr(10) As Integer\r\n<\/pre>\n<\/li>\n
        \n
        In the above example 2, Arr<\/em><\/strong> is an Array variable to store Integer type of data with upper limit of 10.<\/p>\n
         In the declaration there is no lower limit defined. Only the upper limit is defined. <\/p>\n
        In this case, what should be the lower limit?<\/strong> In such a case default lower limit, that is Zero – 0, is applied. Zero is the default lower limit of an array<\/strong> – if the lower limit is not already defined OR there is no Option Base statement<\/em><\/strong> set in the module where the array is declared. You can refer to the below picture where the default lower limit is Zero – 0<\/strong><\/p>\n
        \"Default
        Default Lower Bound<\/p><\/div>\n
         What is Option Base Statement in Excel VBA Module?<\/h2>\n
        This is a statement that can be defined at a module level. Using this statement, you can define the lower bound of all arrays declared in that module where there is no lower bound defined already.<\/p>\n
        \r\nOption Base 1\r\n<\/pre>\n
        The above statement should be written at the top of the module. Note that the Option Base statement can set only 2 values 0 or 1. You can not define any other value here other than 0 or 1.<\/p>\n
        You can refer to the below image to see how the default lower bound is changed by using the Option Base statement in the same array.<\/p>\n
        \"Default
        Default Lower Bound in Array<\/p><\/div>\n
        Declare an Array Variable with upper and lower bound<\/h2>\n
        It is possible to declare an array variable with both lower bound and upper bound specified. In this case, the Option base statement or default value of the lower limit will have no impact on the lower limit of the array.<\/p>\n
        Option Base 1\r\n\r\nSub arrayExample()\r\n        Dim Arr(5 To 10) As Integer\r\n        Debug.Print \"Lower Bound: \" & LBound(Arr)\r\nEnd Sub\r\n<\/pre>\n
        You can see in the below picture that after running the above code, the lower bound of the array is set to 5 which is defined in the array declaration. <\/p>\n
        \"Custom
        Custom Lower Bound<\/p><\/div>\n
        \n
        Important<\/h3>\n
          \n
        1. When lower bound limit is defined at the time of declaration then Option Base statement has not effect on the lower bound limit. This is the reason, it is a good VBA programming practise to define your Array variable with both lower and upper bound limit. This way even if some one modifies the Option Base value, your array will remain unaffected.<\/li>\n\n
        2. You must have noticed in other programming languages, the array has a default lower limit to 0 or 1 and you can not define or change it yourself. This is one of the significant differences between array in VBA and other languages.<\/li>\n<\/ol>\n<\/div>\n
          Note that in Excel VBA, your lower and upper limit in the array can be negative numbers too. Excel VBA array would work perfectly for negative lower and upper bound limits.<\/p>\n
          \r\nSub arrayExample()\r\n        Dim counter  As Integer\r\n        Dim Arr(-5 To 10) As Integer\r\n        Debug.Print \"Lower Bound: \" & LBound(Arr)\r\n        counter = 0\r\n        For Each Item In Arr\r\n            counter = counter + 1\r\n            Debug.Print counter\r\n        Next\r\nEnd Sub\r\n<\/pre>\n
          In above example, Arr<\/em><\/strong> variable can hold total 16 integers ( -5 to 0 to 10 = 10+5+1 = 16). Refer to the below image<\/p>\n
          \"Negative
          Negative Upper and Lower Bound<\/p><\/div>\n
          The Upper bound of an array should always be bigger than the lower bound limit. Therefore, In the case of negative bounds, the upper bound integer value should be bigger than the lower limit because of simple maths. The bigger the negative number, the lower the value it has.<\/p>\n
          The following code will declare an array that can hold 6 integer values where the index will start from -15 and goes until -10. (-15, -14, -13, -12, -11, -10).<\/p>\n
          \r\nSub arrayExample()\r\n        Dim counter  As Integer\r\n        Dim Arr(-15 To -10) As Integer\r\n        Debug.Print \"Lower Bound: \" & LBound(Arr)\r\n        counter = 0\r\n        For Each Item In Arr\r\n            counter = counter + 1\r\n            Debug.Print counter\r\n        Next\r\nEnd Sub\r\n<\/pre>\n
          Declaring an Array by Assigning an Array Values<\/h2>\n
          If you have a variant type of variable then you can assign an Array value directly to the variable and that will be converted as an array variable. <\/p>\n
          \r\nSub arrayTwo()\r\n        Dim arr As Variant\r\n        arr = Array(1, 2, 3, 4, 5)\r\n        \r\n        For Each Item In arr\r\n            Debug.Print Item\r\n        Next\r\nEnd Sub\r\n<\/pre>\n
          In the above example, you can see that there is no upper limit defined anywhere. After assigning the value, the array variable will set the upper bound limit as the total number of items inside the array values. You do not need to specify it specifically. <\/p>\n
          Declaring an Array without any upper bound limit<\/h2>\n
          In Excel VBA, it is possible to declare an array variable without any lower or upper bound limit. <\/p>\n
          \r\nSub arrayWithoutLimit()\r\n        Dim arr() As Variant\r\n        Debug.Print (UBound(arr))\r\nEnd Sub\r\n<\/pre>\n
          In the above example, you can see there is no upper or lower limit defined inside the parentheses. In such a case, if I try to get the upper or lower limit of the variable, then it will throw Subscript out of range<\/code> error as shown in the below image. You can not assign any value to any of its indexes. It will throw the same error.\n<\/p>\n
          \"Subscript
          Subscript out of range<\/p><\/div>
          \nIn the above example, if you notice, array variable is defined of type Variant<\/strong>.<\/p>\n
          To use this variable, you need to initialise the array variable first. Since this is a type Variant<\/strong>, initialisation can be done in two ways: <\/p>\n
            \n
          1. You can assign an existing array that already has some values or upper limit defined like shown below: <\/li>\n
            \"Initialize
            Initialize an Array by Assigning Value<\/p><\/div>\n
            In such a case you don’t need to define the upper limit specifically. The Upper Bound limit is set automatically by the total number of items stored in the array you assign it to.<\/p>\n
            \n
            Important note:<\/h3>\n
            Above method is possible, only when the array type is defined as Variant<\/strong>. In case of any other type, above code will throw  Type mismatch error<\/code>.<\/p>\n
            \"Type
            Type Mismatch Error<\/p><\/div>\n<\/div>\n
          2. The second method is to use ReDim<\/strong> keyword to size or resize a predefined array. Let’s take a look in detail : What is it and how does it work?<\/li>\n
              \n
               What is ReDim<\/code> an Array <\/h2>\n
              As the name suggests, ReDim is a keyword that is used to declare an Array variable again. For an already defined variable, if you use the Dim keyword again, VBA would not accept it but if you want to redefine your array variable during execution of the program, you can use the ReDim keyword to do so.<\/p>\n
              This is a very useful way of defining an array when the size of the array is not known at the start of the program. Or you want to change the size of the array at run time.\n<\/p>\n
              Based on the above definition, it is clear that ReDim can be done anytime during the program execution. That means, it is possible that the array already has some values inside. So the question is what happens to the existing data in the array? Let’s take a look.\n<\/p>\n
              Syntax:<\/h3>\n
              ReDim [ Preserve ] VariableName ( subscript ) [ As type ]<\/code><\/p>\n
              \nReDim:<\/strong> is the keyword to size or resize an already declared array variable. This is to redeclare an existing variable.
              \nPreserve<\/strong> is an optional keyword. It is used to indicate what should be done with the existing data in an already defined array while re-declaring it.
              \nVariableName<\/strong> name of variable which you want to redeclare it.
              \nsubscript<\/strong> Is the new upper and lower bound limit you want to change.\n<\/p>\n
              In the above syntax, the only part you are NOT familiar with is the keyword Preserve<\/em><\/strong>. Rest are same as Dim statement explained above.<\/p>\n
              Declaring an Array without Preserve<\/h2>\n
              While re-defining your array variable Without Preserve keyword, you will lose all the existing data inside the array. This is the default behaviour.<\/p>\n
              \r\n\r\nOption Base 1\r\n\r\nSub Default()\r\n    Dim arr() As Variant\r\n    arr = Array(1, 2, 3, 4, 5)\r\n    Debug.Print (\"Before ReDim: \" & UBound(arr))\r\n    Debug.Print (readArray(arr))\r\n    ReDim arr(8) As Variant\r\n    Debug.Print (vbNewLine & vbNewLin & \"After ReDim: \" & UBound(arr))\r\n    Debug.Print (readArray(arr))\r\nEnd Sub\r\n\r\nFunction readArray(arr() As Variant)\r\n    Dim msg\r\n    For Each Item In arr\r\n        msg = msg & Item & \", \"\r\n    Next\r\n    Debug.Print (\"Items: \" & msg)\r\nEnd Function\r\n\r\n<\/pre>\n
              After running the above code you can see that array variable arr<\/strong> has all values stored and its upper bound limit is set to 5. But after you ReDim the same array variable to increase the size of the array to 8 without Preserver Keyword, then new Array has upper bound limit as 8 but all the 5 items stored are lost. You can refer to the below picture.<\/p>\n
              \"ReDim
              ReDim without Preserve<\/p><\/div>\n
              Declaring an Array with Preserve<\/h2>\n
              It is used when you want to preserve the existing data in the array as it is. I have used the same piece of code as above. The only difference is the i have used Preserver keyword while ReDim the variable.<\/p>\n
              This time you can see all the 5 items are stored as it is in the new variable. Other 3 spots in array has its default value.<\/p>\n
              \r\nSub Default()\r\n    Dim arr() As Variant\r\n    arr = Array(1, 2, 3, 4, 5)\r\n    Debug.Print (\"Before ReDim: \" & UBound(arr))\r\n    Debug.Print (readArray(arr))\r\n    ReDim Preserve arr(4 To 8) As Variant\r\n    Debug.Print (vbNewLine & vbNewLin & \"After ReDim: \" & UBound(arr))\r\n    Debug.Print (readArray(arr))\r\nEnd Sub\r\n\r\nFunction readArray(arr() As Variant)\r\n    Dim msg\r\n    For Each Item In arr\r\n        msg = msg & Item & \", \"\r\n    Next\r\n    Debug.Print (\"Items: \" & msg)\r\nEnd Function\r\n<\/pre>\n
              \"ReDim
              ReDim with Preserve Keyword<\/p><\/div>\n
              \n
              How ReDim works?<\/h3>\n
                \n
              1. ReDim creates a new Array variable with the specified size mentioned in the Subscript<\/strong>.<\/li>\n
              2. Copies the existing data from old variable to new one [if Preserve<\/strong> keyword is specified else default values]<\/li>\n
              3. Remove the old variable and release the memory<\/li>\n<\/ol><\/div>\n
                Reducing the size of an Array with Preserve<\/h2>\n
                It is possible to reduce the size of an existing array which has data in every cell. As I explained above – ReDim creates a new Array with the expected size and then if Preserve Keyword is specified, It will copy the data from old to new Variable and then release the old variable.<\/p>\n
                Data copy process happens from lower limit to upper bound limit sequentially. This process stops the moment it reaches to the upper bound limit of the new array defined.<\/p>\n
                Therefore, you will see only those items which can fit in the new size and remaining will be ignored. You refer the below image<\/p>\n
                \"Reduce
                Reduce the Size of an Array<\/p><\/div>\n
                \n
                <\/i>Don’t miss this info<\/h2>\n
                  \n
                1. ReDim statement is used to size or resize an Array variable that is already formally defined in your program.<\/li>\n
                2. Preserve keyword can preserve all the existing data while re-sizing your array at run time.<\/li>\n
                3. You can not change the data type of an already declared array variable. <\/li>\n
                4. You can not change the dimension of an array using ReDim statement.\n
                5. Using ReDim statement, you can size the upper and lower bound limit of an array.<\/li>\n
                6. In the case of using Preserve <\/strong> keyword, you can not resize the lower bound limit of the array.<\/li>\n<\/ol>\n<\/div>\n
                  Types of methods on Excel VBA Array<\/h1>\n
                  To know about all the methods used on Excel VBA array refer to my next article here.<\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"
                  A complete guide to learning Array in Excel VBA programming. If you have done programming in any language C, VB, Java, Python, etc., you must have definitely heard and known of Array. The array is a data structure that stores data in a sequence. Each of its elements can be accessed via its sequential index […]<\/p>\n","protected":false},"author":45,"featured_media":244286,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"off","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1679,1675,5204],"tags":[],"class_list":["post-244112","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-macro-beginner","category-excel-macro-for-beginners","category-vba-programming"],"yoast_head":"\nA Complete guide to Array in Excel VBA - Let's excel in Excel<\/title>\n<meta name=\"description\" content=\"A Complete Guide to Excel VBA array. What is ReDim in excel VBA? Option Base statement. Resize an Array. Resize an array without losing data\" \/>\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\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A Complete guide to Array in Excel VBA\" \/>\n<meta property=\"og:description\" content=\"A Complete Guide to Excel VBA array. What is ReDim in excel VBA? Option Base statement. Resize an Array. Resize an array without losing data\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/\" \/>\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=\"2022-08-24T10:44:46+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-24T14:28:45+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2011\/11\/complete-guide-to-array.png\" \/>\n\t<meta property=\"og:image:width\" content=\"400\" \/>\n\t<meta property=\"og:image:height\" content=\"250\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/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=\"12 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"A Complete guide to Array in Excel VBA\",\"datePublished\":\"2022-08-24T10:44:46+00:00\",\"dateModified\":\"2022-08-24T14:28:45+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/\"},\"wordCount\":1986,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2011\/11\/complete-guide-to-array.png\",\"articleSection\":[\"Excel Macro Beginner\",\"Excel Macro Tutorial\",\"VBA Programming\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/\",\"name\":\"A Complete guide to Array in Excel VBA - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2011\/11\/complete-guide-to-array.png\",\"datePublished\":\"2022-08-24T10:44:46+00:00\",\"dateModified\":\"2022-08-24T14:28:45+00:00\",\"description\":\"A Complete Guide to Excel VBA array. What is ReDim in excel VBA? Option Base statement. Resize an Array. Resize an array without losing data\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/#primaryimage\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2011\/11\/complete-guide-to-array.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2011\/11\/complete-guide-to-array.png\",\"width\":400,\"height\":250,\"caption\":\"A Complete guide to VBA Array\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Macro Beginner\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-macro-beginner\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"A Complete guide to Array in Excel 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\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"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:\/\/x.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":"A Complete guide to Array in Excel VBA - Let's excel in Excel","description":"A Complete Guide to Excel VBA array. What is ReDim in excel VBA? Option Base statement. Resize an Array. Resize an array without losing data","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\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/","og_locale":"en_US","og_type":"article","og_title":"A Complete guide to Array in Excel VBA","og_description":"A Complete Guide to Excel VBA array. What is ReDim in excel VBA? Option Base statement. Resize an Array. Resize an array without losing data","og_url":"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/","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":"2022-08-24T10:44:46+00:00","article_modified_time":"2022-08-24T14:28:45+00:00","og_image":[{"width":400,"height":250,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2011\/11\/complete-guide-to-array.png","type":"image\/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":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"A Complete guide to Array in Excel VBA","datePublished":"2022-08-24T10:44:46+00:00","dateModified":"2022-08-24T14:28:45+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/"},"wordCount":1986,"commentCount":0,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2011\/11\/complete-guide-to-array.png","articleSection":["Excel Macro Beginner","Excel Macro Tutorial","VBA Programming"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/","url":"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/","name":"A Complete guide to Array in Excel VBA - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/#primaryimage"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2011\/11\/complete-guide-to-array.png","datePublished":"2022-08-24T10:44:46+00:00","dateModified":"2022-08-24T14:28:45+00:00","description":"A Complete Guide to Excel VBA array. What is ReDim in excel VBA? Option Base statement. Resize an Array. Resize an array without losing data","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/#primaryimage","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2011\/11\/complete-guide-to-array.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2011\/11\/complete-guide-to-array.png","width":400,"height":250,"caption":"A Complete guide to VBA Array"},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2022\/08\/a-complete-guide-to-array-in-excel-vba\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Macro Beginner","item":"https:\/\/vmlogger.com\/excel\/excel-macro-beginner\/"},{"@type":"ListItem","position":3,"name":"A Complete guide to Array in Excel 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":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"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:\/\/x.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\/244112"}],"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=244112"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/244112\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media\/244286"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=244112"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=244112"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=244112"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}