Fixed Arrays
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.
Declaring a Fixed 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 with six elements.
All VBA arrays (by default) start at zero.
This array has the following elements: myArray(0), myArray(1), myArray(2), myArray(3), myArray(4) and myArray(5).
Dim myArray(5) As Integer
Default Values
After declaring an array it will contain default values.
Numeric arrays will contain the number 0.
String arrays will contain the empty sting "".
Variant arrays will contain the Empty keyword.
Object arrays will contain the Nothing keyword.
Populating a Fixed Array
Once the array has been declared it can be populated.
Dim myArray(3) As Long
myArray(0) = 50
myArray(1) = 100
myArray(2) = 150
myArray(3) = 200
Explicit Lower Bound
It is possible to explicitly declare the lower bound of your 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.
Option Base Statement
If you want all your arrays to start at the index number 1 (instead of 0) you can define a module level setting.
The following line of code will declare an array containing exactly 100 elements with the index values from 1 to 100.
Notice that we have not explicitly declared the lower bound to be 1.
Option Base 1
Dim myArray(100) As Integer
More details can be found on the Option Base 1 page.
Important
For more details about using Excel Ranges with arrays, refer to the Excel > Cells & Ranges > VBA Code > Working with Arrays page.
© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext