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

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.