METBD 050VBA ArraysAn array is a variable that can have more than one value assigned to it at a time. The values generally have a similar characteristic. Each item in an array is called an element. Each element in an array is identified by a number called a subscript. We will be discussing one-dimensional arrays. These are arrays whose elements have one subscript. Arrays are useful for sorting information, for importing information from a text file, and for doing matrix operations within a program.Arrays elements can be specified when the array is defined, or by using a loop within a procedure. To specify the elements of the array at definition, use the Array keyword with the array name. The syntax is:Arrayname = ARRAY(item_1, item_2, … item_n)EXAMPLE: Write the code to create an array called WeekDays that contains the days of the week, Monday thru Friday.WeekDays = Array(“Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”)All of the elements in the array are similar because they are days of the week, but each is different from the others. The first element in the array is designated as Weekdays(0). The list of elements is:Weekdays(0) = “Monday”Weekdays(1) = “Tuesday”Weekdays(2) = “Wednesday”Weekdays(3) = “Thursday”Weekdays(4) = “Friday”Arrays have to be declared before they can be filled during the execution of the procedure. The keywords forarray definition are Dim and ReDim. Dim is used to define an array that has a constant number of entries. The ReDim option is used if the size of the array can be changed within the program. This is often the case when an unknown amount of data is to be read into the procedure from a text file. Use the Dim keyword to create an array. The syntax is:Dim arrayname (1 to n) As variabletypeThe starting number for an array is traditionally a 0 or a 1. The ending number, n, is the maximum number needed to save the data. The variabletype is any standard type, such as Integer, Single, String, etc.EXAMPLE: Write the line of code to create an array called MyNumbers that can contain up to 100 integers.Dim MyNumbers (1 to 100) As IntegerThe first element in this array is MyNumbers(1). An alternate method is to just specify the size of the array. The element subscripts will begin at 0 by default in this case. The following line of code demonstrates the use of this method:Dim MyNumbers (100) As IntegerJust note that the first element in this array is MyNumbers(0), not MyNumbers(1). It is also important to notethat each of the elements in MyNumbers is empty because they have not been filled by code. HO-050-VBA-Arrays.doc Page 1 of 23/27/02EXAMPLE: Write the procedure necessary to assign the first 100 integers to the MyNumbers array.Sub Fill_MyNumbers() Dim MyNumbers(1 To 100) As Integer ‘ Define Array For x = 1 To 100 ‘ Use For…Next to fill array MyNumbers(x) = x ‘ “X” element = x Next xEnd SubWhat is the designation for the first element in the array? What is its value?VBA has two functions used to identify the upper and lower limits of the array. They are UBound and LBound. For the previous example, UBound(MyNumbers) = 100 and LBound(MyNumbers) = 1.Sometimes, when importing data from a text file, you may not know how many elements will be needed. In this case, a redefinable array is necessary. Using the ReDim keyword, the syntax is the same as with the Dim keyword:ReDim MyNumbers (1 To 100) As IntegerEXAMPLE: Write a procedure necessary to create an array for 100 integers. While filling the array with 150 elements, resize the array. Trim the array to minimum size at the end of the filling process. Sub FillArray() ReDim iNumbers(1 To 100) As Integer UpperNo = UBound(iNumbers) For x = 1 To 150 iNumbers(x) = x If x = UpperNo Then ReDim Preserve iNumbers(1 To UpperNo + 100) End If ActiveCell.Value = iNumbers(x) ActiveCell.Offset(1, 0).Select Next x ‘ Last x = 151 ReDim Preserve iNumbers(1 To x - 1) End SubIn this code, when the array is redimensioned, the keyword Preserve is used to retain the existing values of the array. If Preserve is left out, the array elements are erased. If the procedure tries to put more data into an array than there is space allotted, an error will occur. Notice that if the loop reaches the limit of the array, the array is increased by 100 elements. At the end of the procedure, the array is trimmed so that there are no extra elements. Further looping will not have to pass through empty elements.EXAMPLE: Write the procedure to add the two matrices, A and B, shown here: The sum of the matrices should be saved in C and displayed in cells A1:A3 of theactive worksheet.Sub AddMatrices() A = Array(1, 2, 3) ‘ First element is A(0) B = Array(3, 2, 1) ‘ First element is B(0) Dim C(0 To 2) As Integer ‘ First element is C(0) [A1].Select For x = 0 To 2 ‘ Use element numbers for loop C(x) = A(x) + B(x) ActiveCell.Value = "C(" & x & ") = " & C(x) ActiveCell.Offset(1, 0).Select Next xEnd SubHO-050-VBA-Arrays.doc Page 2 of