Working with Arrays

---

An array is a group of values that are of the same data type. Although an array contains multiple values, VBA treats the array as one variable, which offers some flexibility when working with arrays. You can work with the entire array at once or just one element in the array at a time. To work with an individual element of an array, you specify the element's index number, which represents the element's position within the array. If you visualize an array as a list, the index number is the element's position in the list.

Arrays have two boundaries, a lower and an upper. The default lower boundary for arrays is 0, which can lead to a bit of confusion at times. With a starting index number of 0, the first element in the list would be at position 0. This means you are always working with an index number that is actually one less than the actual position in the array.

If you prefer to start counting from 1, you can change the lower boundary of an array using the Option Base statement. The Option Base statement must be placed in the declarations section of a module before any arrays are defined and before any procedures. The Option Base statement can take only an argument of 0 or 1, as shown in this example:

Option Base 1 

Creating an Array

You create an array using the same procedure you use to define a variable. Use a Dim, a Private, a Public, or a Static statement followed by the array name and the date type that is to be stored in the array. The only difference between creating an array and creating a variable is that you must define at least an upper boundary in the array declaration. (You can also specify a lower boundary, as you'll see shortly.) To create a string array that could hold the names of the 12 months, for example, you would use the following statement:

Dim strMonths(11) as String

Remember, unless specified otherwise, arrays start with a default lower boundary of 0. Therefore, to hold the twelve months, you set an upper boundary of 11 (0 to 11 provides you with twelve elements). If you had included the Option Base 1 statement in the declarations section then you could declare the array like this:

Dim strMonths(12) as String

Besides using the Option Base statement, you can also set the lower boundary of an array to a value other than 0 or 1 by explicitly declaring it when you create the array using a To clause. For example, if you wanted to declare an array that included only the three months of summer, you could use the following statement:

Dim strSummerMonths(6 To 8) as String

Creating Multidimensional Arrays

All the arrays we have talked about so far have been simple, single-dimensional arrays. You can also create multidimensional arrays with up to 60 dimensions, although you will probably never find a reason to use more than 3 or 4 dimensions.

To declare a multidimensional array, you separate the boundaries of each dimension by commas. A two-dimensional array that could hold a value for each month over 10 years could be declared with the following statement:

Dim intDecadeArray(9, 11) as Integer

Visualizing multidimensional arrays might seem daunting, but some could be straightforward. For example, one use of a two-dimensional array would be as a table, where the first dimension represents the rows of the table while the second dimension represents the columns, much like the appearance of a worksheet in Excel.

Expanding to a third dimension is not much harder. Continuing with the table or worksheet example, you could use a third dimension to keep track of multiple tables or worksheets. For example, if a garden supply store were open sixteen hours a day, you could create an array such as the following:

Dim curHourlySales(12, 31, 16) as Currency

---