VBA Declaring an Array in VBA


Declaring an array is very similar to declaring a variable, except you need to declare the dimension of the Array right after its name:

Dim myArray(9) As String 'Declaring an array that will contain up to 10 strings

By default, Arrays in VBA are indexed from ZERO, thus, the number inside the parenthesis doesn't refer to the size of the array, but rather to the index of the last element

Accessing Elements

Accessing an element of the Array is done by using the name of the Array, followed by the index of the element, inside parenthesis:

myArray(0) = "first element"
myArray(5) = "sixth element"
myArray(9) = "last element"

Array Indexing

You can change Arrays indexing by placing this line at the top of a module:

Option Base 1

With this line, all Arrays declared in the module will be indexed from ONE.

Specific Index

You can also declare each Array with its own index by using the To keyword, and the lower and upper bound (= index):

Dim mySecondArray(1 To 12) As String 'Array of 12 strings indexed from 1 to 12
Dim myThirdArray(13 To 24) As String 'Array of 12 strings indexed from 13 to 24

Dynamic Declaration

When you do not know the size of your Array prior to its declaration, you can use the dynamic declaration, and the ReDim keyword:

Dim myDynamicArray() As Strings 'Creates an Array of an unknown number of strings
ReDim myDynamicArray(5) 'This resets the array to 6 elements

Note that using the ReDim keyword will wipe out any previous content of your Array. To prevent this, you can use the Preserve keyword after ReDim:

Dim myDynamicArray(5) As String
myDynamicArray(0) = "Something I want to keep"

ReDim Preserve myDynamicArray(8) 'Expand the size to up to 9 strings
Debug.Print myDynamicArray(0) ' still prints the element