An array is a variable used to hold a group of related values; it must be declared just as a variable is declared. An array is declared with a single name and the number of elements (values) that can be stored in the array.
Dim myArray(number of elements) As Type
You may also declare arrays using the Public or Private keywords to define the scope as you would with a regular variable declaration. If you do not specify a data type, then, like a variable, the array will be a variant type. Arrays may be declared as any available data type in VBA. All elements in arrays with numerical data types are initialized with the value 0. Elements of string arrays are initialized with an empty string. When specifying the number of elements, you must consider the lower bound of the array. The default lower bound is zero.
Dim myArray(10) As Integer
When you need multiple array declarations of the same size, use a constant to specify the size of the arrays in the declarations.
Dim myArrayl(ARRAYSIZE) As Integer
Dim myArray2(ARRAYSIZE) As Integer
Dim myArray3(ARRAYSIZE) As Integer
This way, if you have to edit the size of your arrays, you only need to change the value of the constant.
Thus, the integer array myArray declared above has 11 elements accessed with the indices 0 through 10. To override the default, set the lower bound of the array in the declaration.
Dim myArray(1 To 10) As Integer
The array myArray now has just 10 elements because the lower bound has been explicitly set to one.
Use the statement Option Base 1 in the general declarations section of a module to change the default lower bound of all arrays declared in the module to 1.
You can initialize a single element in the array as you would a variable, but you must include the index of the element you wish to change.
However, arrays are typically initialized inside a loop. To insert the spreadsheet's values of the first 10 cells of column A into an array, do the following:
Dim I As Integer
Dim myArray(10) As Integer
myArray(I) = Cells(I + 1, "A").Value Next I
Then use another loop to output the values of the array. The following loop squares the values stored in the array myArray before copying them to column B of the spreadsheet.
Cells(I + 1, "B").Value = myArray(I)A2 Next I
Now let's revisit the BubbleSort() procedure, this time using an array. The sub procedure BubbleSort2() works exactly like the BubbleSort() procedure, except that the tests and swaps are performed on the values in the set after they have been loaded into an array rather than just using the worksheet column.
Public Sub BubbleSort2() Dim tempVar As Integer Dim anotherIteration As Boolean Dim I As Integer Dim myArray(10) As Integer For I = 2 To 11
myArray(I - 2) = Cells(I, "A").Value Next I Do anotherIteration = False 'Compare and swap adjacent values For I = 0 To 9
If myArray(I) > myArray(I + 1) Then tempVar = myArray(I) myArray(I) = myArray(I + 1) myArray(I + 1) = tempVar anotherIteration = True End If Next I
Loop While anotherIteration = True Range("B1").Value = "Sorted Data" For I = 2 To 11
Cells(I, "B").Value = myArray(I - 1) Next I End Sub
After variable declarations, the values in column A of the worksheet are loaded into the array with a simple For/Next loop. The For/Next loop nested in the Do-Loop is just as it was in the BubbleSort() procedure, except now the Cells property has been replaced with the array named myArray. The looping variable in the For/Next loop now runs from 0 to 9 because the lower bound for the array is 0 not 1. When the first value is greater than the second, the values are swapped. Finally, the sorted values are written to column B in the worksheet.
Was this article helpful?