Using Arrays

Problem

You don't know the VBA syntax for declaring arrays .

Solution

You declare arrays just as you do variables, except you specify the size of the array in the declaration, as shown in Example 2-7.

Example 2-7. Declaring arrays

Dim y(1 To 4) As Double
Dim x(4) As Double

Dim M(1 To 8, 1 To 8) As Double
Dim N(8, 8) As Double

Arrays are declared with their size specified in parentheses following the array name.

Discussion

When you use the To keyword in an array size specification, the first number is the lower bound and the second number (the one following To) is the upper bound of the array. You can skip the To keyword and simply put the desired size (i.e., the number of array elements) in parentheses following the array name. In this latter approach, the first array index, the base, is 0 unless you use the Option Base statement.

Use the Option Base statement at the beginning of a code module (before your procedures) to specify the default base for arrays. For example, Option Base 1 sets the base to 1, while Option Base 0 sets the base to 0.

You can declare multidimensional arrays by separating each dimension's size, or upper and lower bounds, by commas in the array declaration, as shown in the last two lines of Example 2-7. VBA supports multidimensional arrays of up to 60 dimensions.

To actually access an array's elements in your code statements, write the array name followed by the index to the array element enclosed in parentheses. Example 2-8 shows how to access array elements.

Example 2-8. Accessing array elements

y(3) = 2.983

M(1, 2) = 4.321

Separate the array indices by commas for multidimensional arrays.

See Also

The array declarations discussed here are static declarations in that the size of the arrays is specified in the Dim statement. In some special cases, you may not know the required size of an array and may need to allocate memory for the array dynamically. In such cases you can declare the array as discussed earlier but leave the parentheses following the array name blank (include the parentheses, just don't put anything between them). Then in your code you'll have to dynamically allocate memory for the array of the size you desire. Use ReDim statements to set, or reset, the size of an array. For the most part, I'll stay away from dynamically allocated arrays except in special circumstances. You can learn more about dynamic arrays by reading the VBA help topic "Declaring Arrays." Press F1 in the VBA IDE to access the VBA help guide.