Declaring

Fixed arrays have a fixed size which cannot be changed at run-time.
These are also known as Static Arrays.
An array is declared by including parentheses after the array name or identifier.
An integer is placed within the parentheses, defining the number of elements in the array.

Fixed Array - Integer Data Type Array

You can declare a fixed array using the Dim statement.
It is good practice to always declare your arrays with an explicit data type.
This line creates an array of Integers with 4 items.
All VBA arrays (by default) start at zero.

Dim myArray(3) As Integer 

Debug.Print myArray(0)    '= 0  
Debug.Print myArray(1)    '= 0  
Debug.Print myArray(2)    '= 0  
Debug.Print myArray(3)    '= 0

Fixed Array - Variant Data Type Array

This line creates an array of Variant with 4 items.

Dim myArray(3) As Variant 

Debug.Print myArray(0)    '= Empty  
Debug.Print myArray(1)    '= Empty  
Debug.Print myArray(2)    '= Empty  
Debug.Print myArray(3)    '= Empty

Fixed Array - Variant Data Type

You can declare a fixed array as a single variant data type and then use the ARRAY function.
This function declares and adds the values in one statement.

Dim avNumbers As Variant 
avNumbers = Array(10,20,30)

An array created with a single variant data type will always create an array of Variants.

Default Values

After declaring an array it will contain default values.
Numeric arrays will contain the number 0.
String arrays will contain the Empty String "".
Variant arrays will contain the Empty keyword.
Object arrays will contain the Nothing keyword.

Explicit Lower Bound

It is possible to explicitly declare the lower bound of your fixed array.
The following two lines are equivalent and both these arrays contain 101 elements.
The index values range from 0 to 100.

Dim myArray(100) As Integer 
Dim myArray(0 To 100) As Integer

If you wanted to declare an array containing exactly 100 elements you could do this in a number of different ways.

Dim myArray(99) As Integer 
Dim myArray(0 To 99) As Integer 
Dim myArray(1 To 100) As Integer

The first two lines are equivalent. The first element is myArray(0) and the last element is myArray(99).
The last line has slightly different index numbers. The first element is myArray(1) and the last element is myArray(100).
In fact you could define the index numbers to be any range you like as long as the first number is less than the second number.

Dim myArray(101 To 200) As Integer 
Dim myArray(-49 To 50) As Integer

When you explicitly declare the lower bound of your array this will override the Option Base statement.

Never Use Option Base 1

If you want all your arrays to start at the index number 1 (instead of 0) you can explicitly declare the lower bound to be 1.
More details can be found on the Option Base 1 page.
The following line of code will declare an array containing exactly 100 elements with the index values from 1 to 100.

Dim myArray(1 To 100) As Integer

Dynamic Size

Dynamic arrays do not have a fixed size and can be resized at run-time.
These are also known as Variable Arrays or Variable-Length Arrays.
This type of array is very useful when you don't know the exact size of the array at design time.
You can declare a dynamic array using the Dim statement.
When you need to define the size of the array you can use the ReDim statement.
You can declare a dynamic array by using an empty set of parentheses.
Dynamic arrays are always declared with empty parentheses.
The parentheses must always be attached to the variable, never the data type.

Dynamic Array - String Data Type Array

These two lines create an array that can contain 4 string elements.

Dim aStrings() As String      'this is a String Array  
ReDim aStrings(3)

Dynamic Array - Variant Data Type Array

These two lines create an array that can contain 4 elements of any data type.

Dim aVariants() As Variant    'this is a Variant Array  
ReDim aVariants(3)

Dynamic Array - Variant Data Type

A Variant that is not declared as an array can still contain an array.
The first line creates a single variable (not an array) that can contain any data type.
The second line declares an array containing 4 elements and assigns it to this single variable.

Dim avNumbers As Variant      'this is just a Variant  

ReDim avNumbers(2)            'creates an array of Variants  
ReDim avNumbers(3) As Long    'creates an array of Longs

This approach declares your array as a single variable and then creates the array.
When you declare as a Variant Data Type you can define the data type when the array is created.

Default Values

Explicit Lower Bound

It is possible to explicitly declare the lower bound of your dynamic array.
The following two lines are equivalent and both these arrays contain 101 elements.
The index values range from 0 to 100.

ReDim myArray(100) 
ReDim myArray(0 to 100)

A dynamic array does not have a pre-defined number of elements
Resizing arrays at run-time can be very inefficient. If adding a lot of data dynamically do not resize the array after every new element but after every 10 or so.

Never Use Option Base 1

Dim myArray(1 To 100) As Integer

Variant Data Type

This method uses a Variant data type to contain the whole array.
This is different to an array of Variant data types.
A Variant that is not declared as an array can still contain an array.
The first line creates a single variable (not an array) that can contain any data type.
The second line declares an array containing 4 elements and assigns it to this single variable.

Dim arValues As Variant 
ReDim arValues(3)

Testing for Empty

Using this method (without the parentheses) allows you to check for Empty and assign Empty to an array.
The Empty data type is the default value when you declare a variable as a Variant.

Dim arValues As Variant 
If (arValue = Empty) 
   Debug.Print "Empty" 
End If

Testing for Null

Using this method (without the parentheses) allows you to assign "Null" to the array when you want to represent no data.

Dim arValues As Variant 
arValues = Null 
If (arValue = Null) 
   Debug.Print "Null" 
End If

Returning Arrays from Functions

One reason you might use a Variant data type to contain your array is when you want to return an array from a function.
Passing Arrays Out

ARRAY Function

When you declare an array as a Variant you can initialise it using the ARRAY function.
This lets you quickly populate it using a series of comma-delimited values.
When using the ARRAY function the array always starts at zero. Any Option Base statement will be ignored.

Dim myArray As Variant 
myArray = Array(1, 2, 3, 4) 
myArray = Array("one", "two", "three", "four") 
myArray = Array("50, "text", false, -4.6)