{"id":29,"date":"2014-01-24T16:13:11","date_gmt":"2014-01-24T23:13:11","guid":{"rendered":"https:\/\/blogs.ubc.ca\/coetoolbox\/?page_id=29"},"modified":"2016-09-26T09:50:58","modified_gmt":"2016-09-26T16:50:58","slug":"arrays","status":"publish","type":"page","link":"https:\/\/blogs.ubc.ca\/coetoolbox\/vba\/arrays\/","title":{"rendered":"Working with Arrays"},"content":{"rendered":"<p>An array is an indexed list of data. To define an array in VBA, define an array of a particular data type:<\/p>\n<p style=\"padding-left: 30px;\"><span style=\"color: #0000ff;\">Dim<\/span> <span style=\"color: #800000;\">myArray(10)<\/span> <span style=\"color: #0000ff;\">As Double<br \/>\n<span style=\"color: #0000ff;\">Dim<\/span> <\/span><span style=\"color: #800000;\">myMatrix(2, 5)<\/span> <span style=\"color: #0000ff;\">As Integer<\/span><\/p>\n<h2>1. Indexing<\/h2>\n<ul>\n<li>The default indexing begins at 0.<\/li>\n<li>You can type <strong>Option Base 1<\/strong> at the top of the module to change the first index to 1.<\/li>\n<li>You can also define array indexing by defining the range specifically:<\/li>\n<\/ul>\n<p style=\"padding-left: 30px;\"><span style=\"color: #0000ff;\">Dim<\/span> myArray (<strong><span style=\"color: #800000;\">1 To 10<\/span><\/strong>) As Double<\/p>\n<h2>2. Array Values<\/h2>\n<p>You can insert values into the array one by one:<\/p>\n<p style=\"padding-left: 30px;\">myArray(1) = 11<br \/>\nmyArray(2) = 22<br \/>\nmyArray(3) = 33<\/p>\n<p>Or you can use loops:<\/p>\n<p style=\"padding-left: 30px;\">For i = 1 to 10<br \/>\nmyArray(i) = i<br \/>\nNext i<\/p>\n<p style=\"padding-left: 30px;\">For i = 1 to 2<br \/>\nFor j = 1 to 5<br \/>\nmyMatrix(i, j) = i*j<br \/>\nNext j<br \/>\nNext i<\/p>\n<p>Alternatively, the <strong>Array function<\/strong> allows you to list values for each array element.<\/p>\n<p>data = <span style=\"color: #0000ff;\">Array<\/span>(12.3, 13.4, 16.5, 13.8, 7, 2.9, 24.2, 5.5, 8, 9.1)<\/p>\n<h2>3. Dynamic Arrays<\/h2>\n<p>To define dynamic arrays, give the size later using <span style=\"color: #000000;\"><strong>ReDim<\/strong><\/span>.<\/p>\n<p style=\"padding-left: 30px;\">Dim myArray() As Double<br \/>\n<span style=\"color: #0000ff;\">ReDim<\/span> myArray(10)<\/p>\n<p>Use the <strong>ReDim Preserve<\/strong> statement to change the size of an array while keeping its current values.<\/p>\n<p style=\"padding-left: 30px;\"><span style=\"color: #0000ff;\">ReDim Preserve<\/span> myArray(11)<br \/>\nmyArray(11) = InputBox(\u201cPlease enter the 11th value\u201d)<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>An array is an indexed list of data. To define an array in VBA, define an array of a particular data type: Dim myArray(10) As Double Dim myMatrix(2, 5) As Integer 1. Indexing The default indexing begins at 0. You can type Option Base 1 at the top of the module to change the first [&hellip;]<\/p>\n","protected":false},"author":22982,"featured_media":0,"parent":2,"menu_order":5,"comment_status":"open","ping_status":"closed","template":"page-templates\/full-width.php","meta":{"footnotes":""},"class_list":["post-29","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/29","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/users\/22982"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/comments?post=29"}],"version-history":[{"count":8,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/29\/revisions"}],"predecessor-version":[{"id":1501,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/29\/revisions\/1501"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/2"}],"wp:attachment":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/media?parent=29"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}