An array is an indexed list of data. To define an array in VBA, define an array of a particular data type:

Dim myArray(10) As Double
Dim myMatrix(2, 5) As Integer

1. Indexing

• The default indexing begins at 0.
• You can type Option Base 1 at the top of the module to change the first index to 1.
• You can also define array indexing by defining the range specifically:

Dim myArray (1 To 10) As Double

2. Array Values

You can insert values into the array one by one:

myArray(1) = 11
myArray(2) = 22
myArray(3) = 33

Or you can use loops:

For i = 1 to 10
myArray(i) = i
Next i

For i = 1 to 2
For j = 1 to 5
myMatrix(i, j) = i*j
Next j
Next i

Alternatively, the Array function allows you to list values for each array element.

data = Array(12.3, 13.4, 16.5, 13.8, 7, 2.9, 24.2, 5.5, 8, 9.1)

3. Dynamic Arrays

To define dynamic arrays, give the size later using ReDim.

Dim myArray() As Double
ReDim myArray(10)

Use the ReDim Preserve statement to change the size of an array while keeping its current values.

ReDim Preserve myArray(11)
myArray(11) = InputBox(“Please enter the 11th value”)