- Computers & Software»
- Computer Science & Programming»
- Programming Languages
VBA: How to Define, Dimension, & Loop through an Array
An array is a variable that is capable of holding multiple data elements.
An array can be single dimensional or multidimensional.
This module will cover single dimensional arrays.
The storage capability of a single dimensional array can best be compared to that of a single row or column of data in Excel.
The Struture of an Array
Each item in an array has an index number associated with it. In a single dimensional array this the index number is a single integer specifying a data elements co ordinance in the array.
When thinking of a single dimensional array as single column or data, you can think of the index number as the row number. The only difference is by default the Index numbers begins at zero a proceeds upward to the end of the array.
Specifying the size of an array
Before you can use a variable as an array you must specify its size. There are two methods for doing this.
You can specify a variables size when defining it or you can do it later subsequently using redim.
When Defining Variable
Dim MyStr(5)as string
After Defining Variable
Redim allows you to specify or change the size of your array after is has been declared. The example above would resize the a string variable "MyStr" to have 6 (0 to 5) slots for holding data.
Loading an Array
Looping through an Array
Ubound is used to return the highest in number in you array. In the example case 5.
Lbound is used to return the lowest index number in your array. In the example case 0.
Looping through an array is easy using Ubound and Lbound
Below is an example for a loop that would go from x=0 (LBound) to x=5 (UBound). Returning the values of the array in cells A1:A6.
For x=LBound(MyStr) to Ubound(MyStr)