Arrays

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.

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.

Passing Arrays In

It is possible to pass an array into a subroutine or function.
Arrays are always passed in by reference (ByRef).
This means that any changes to the array will be passed back to the calling routine.
You can pass both dynamic and fixed arrays to subroutines and functions.

Dim myarray() As String         'dynamic array  
Dim myarray(1 to 3) As String   'fixed array  
Call MyMethod(myarray)

Dynamic Arrays

If you run the subroutine DynamicArray1 you can see that the values in the myDynamicArray are actually changed.

Public Sub DynamicArray1() 
Dim icount As Integer 
Dim myDynamicArray() As Long 

   ReDim myDynamicArray(1 To 10) 
   For icount = LBound(myDynamicArray) to UBound(myDynamicArray) 
      myDynamicArray(icount) = icount 
   Next icount 

   Call DynamicArray2(myDynamicArray) 

   For icount = LBound(myDynamicArray) to UBound(myDynamicArray) 
      Debug.Print myDynamicArray(icount) 
   Next icount 
End Sub 

Public Sub DynamicArray2(ByRef myInternalArray() As Long) 
Dim icount As Integer 

   For icount = LBound(myInternalArray) to UBound(myInternalArray) 
      myInternalArray(icount) = myInternalArray(icount) * 2 
   Next icount 
End Sub

The myInternalArray parameter must be declared with ByRef.
ByRef is the default so it could be omitted but it is good practice to include it.
You will get a compile error if the myInternalArray parameter is declared with ByVal.
The data type of the arrays must be the same. In this case we are using the Long data type.
You will get a compile error if the data types are different.

Fixed Arrays

These are also known as Static Arrays.
Fixed arrays can also be passed in to subroutines and functions.

Public Sub FixedArray1() 
Dim myFixedArray(1 to 5) As Long 
   
   Call FixedArray2(myStaticArray) 
End Sub 

Public Sub FixedArray2(ByRef myInternalArray() As Long) 
End Sub

The myInternalArray parameter must be passed in using ByRef.
The myInternalArray parameter declaration is the same whether you are passing in a dynamic array or a fixed array.
For some odd reason when passing in fixed arrays the numerical data types do not have to match ??

Any Data Type

It is possible to pass an array of any data type into a subroutine or function.
This can be achieved by declaring the parameter as Variant. [ Not an array of Variants, ie Variant() ]
Creating a subroutine (or function) that accepts a single Variant parameter will allow you to pass in arrays of any data type.
This places the whole array into a single Variant variable called myInternalArray.
You can use the ISARRAY function to test the argument.

Public Sub AnyDataType1() 
Dim myIntegerArray() As Integer 
Dim myLongArray() As Long 
Dim myDoubleArray() As Double 
Dim myStringArray() As String 

   Call AnyDataType2(myIntegerArray) 
   Call AnyDataType2(myLongArray) 
   Call AnyDataType2(myDoubleArray) 
   Call AnyDataType2(myStringArray) 
End Sub 

Public Sub AnyDataType2(ByRef myInternalArray As Variant) 
   If (VBA.IsArray(myInternalArray) = True) Then 
   End If 
End Sub

The benefit of declaring an array as a variant means it can be easily passed into procedures and returned from functions.
When you are declaring your arrays as Variants, you can erase the array by just assigning it to Empty.
In order to test if a variant (array) variable has been initialised, ie whether it is equal to "Nothing" use "If vArray IS Nothing Then"
Remember to always initialise your variant array to Empty. It is also possible to create an array and populate it with arrays, (an array of arrays).

Passing Arrays Out

It is possible to pass arrays out of subroutines and functions.
An array can be returned from a function (or property).
The receiving array must have the same data type as the arrays being returned from the function (or property)
If a function returns an array (i.e. a Variant) include the word Array in the name of the function
If the data types do not match then a compile error will be thrown.
If the function returns an explicitly typed array then the variable must also be a matching explicitly typed array.

Returning a String Array

This function returns an array of Long integers.
The function declaration returns an arrays of type Long.
The variable "myvalues" is also declared as an array of type Long.

Public Sub AsDataType 
   Dim myvalues() As Long 
   myvalues = GetNumbers_ReturnLong() 
End Sub 

Public Function GetNumbers_ReturnLong() As Long() 
   Dim mynumbers() as Long 
   ReDim mynumbers(1 to 2) 
   mynumbers(1) = 10 
   mynumbers(2) = 20 
   GetNumbers_ReturnLong = mynumbers 
End Function

Returning a Variant Array

This function returns an array which can be of any type

Public Sub AsVariant 
   Dim myvalues() As Variant 
   myvalues = GetNumbers_ReturnVariant() 
End Sub 

Public Function GetNumbers_ReturnVariant() As Variant() 
   Dim mynumbers() as Long 
   ReDim mynumbers(1 to 2) 
   mynumbers(1) = 10 
   mynumbers(2) = 20 
   GetNumbers_ReturnVariant = mynumbers 
End Function

Returning as a Variant

It is possible to return an array of any dimension and any data type.
This can be achieved by declaring the return value as just a Variant.

Public Sub AsVariant 
   Dim myvalues As Variant 
   myvalues = GetNumbers_ReturnLong() 
End Sub 

Public Function DynamicArray() As Variant 
Dim myIntegerArray() As Integer 
Dim myLongArray() As Long 
Dim myDoubleArray() As Double 
Dim myStringArray() As String 

   DynamicArray = myIntegerArray 
   DynamicArray = myLongArray 
   DynamicArray = myDoubleArray 
   DynamicArray = myStringArray 
End Function

Remember

You cannot pass a Variant data type to a subroutine that is expecting an explicitly typed array.
You cannot pass an array as an optional parameter. However this can be done if you declare the parameter as Variant.
Declaring your parameter as Variant() does not allow you to pass an array of any data type.
The upper and lower bounds of an array cannot exceed the range of a long data type (2,147,483,648).
A variant array is simply an array of elements associated to a variable whose data type is Variant.
You can actually define arrays with up to 60 dimensions, although most people only need 2 or 3.
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.