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.

Dynamic Arrays

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

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.

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.

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).

Important

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.