An array is a group of elements of the same type that have a common name.
An array is a collection of elements that use the same name but are distinguished by an index value.
Arrays contain a sequence of variables.
An array variable is a collection of variables that use the same name, but are distinguished by an index value.
The individual items within an array are called elements.
The numerical value used to access individual elements is called the index.

Reasons for using an Array above a Collection

There are several reasons for using an array over a Collection or a Scripting Dictionary.
*) It is a lot easier to sort an array
*) The entire contents of the array can be viewed in the Watch window.
If you are just saving objects you probably don't care about the order.
Since a Collection or Scripting Dictionary object resize automatically you don't have to keep track or ever resize.

Different Types of Arrays

There are several different types of arrays:
Fixed Size - These remain the same size.
Dynamic Size The size of these can change at run-time by using the ReDim statement.
Rectangular Arrays - These are really 2 dimensional. The number of rows is identical to the number of columns.
Jagged Arrays - These are not really 2 dimensional and represent a 1 dimensional array of 1 dimensional arrays. Each element in the main array can hold an array of varying length.

Array Function

The Array function can be used to create an array from an explicit list of constants.
If you want to use the Array function to declare a fixed array then you must declare the array as a Variant.
When the Array function is used, the variables have the variant datatype.

Dim arValues As Variant 
arValues = Array(1, 2, 3, 4)

Matrices and Vectors

These are fixed size arrays which can be used to represent rectangular blocks of values or numbers.
Arrays with one dimension are called Vectors.
Arrays with two dimensions are called Matrices.

Copying Arrays

To assign one array to another, you must individually assign the elements, for example
If ArrayName1 and ArrayName2 are both defined as arrays then both the following are valid:

Dim ArrayName1() As String 
Dim ArrayName2() As String

ArrayName1 = ArrayName2
ArrayName1() = ArrayName2()


The upper and lower bounds of an array cannot exceed the range of a long datatype (2,147,483,648).
A variant array is simply an array of elements associated to a variable whose datatype is Variant.
You can actually define arrays with up to 60 dimensions, although most people only need 2 or 3.
You should always include parentheses when referring to an array. You don't have to include the parentheses when you refer to an array variable, except when you declare it, resize it or refer to an individual element but it helps make your code more readable.
Always specify both the lower and upper bound of an array "Dim myArray(1 To 10) As String".
If you don't include a lower bound when declaring the size, it is determined by the Option Base setting in the module.

© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopNext