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.

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 myarray1(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 DynamicArray(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 see a compile error if you try to pass an array ByVal.
The datatype of the arrays must also match
You will receive a compile error if they are different
The size of the array cannot be included in the declaration

Public Sub StaticArrays1() 
Dim myStaticArray(1 to 5) As Long
   Call StaticArray2(myStaticArray)
End Sub

It doesn't matter whether you are passing in a static or dynamic array the declaration is the same.

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

For some odd reason when passing in static arrays the numerical data types do not have to match ??

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

You can pass arrays to subroutines and functions

Public Sub MySubroutine(ByRef sMyArray() As String) 
End Sub

You can pass fixed and dynamic arrays
When arrays are declared explicitly they must be passed in explicitly

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

You cannot pass a Variant datatype to a subroutine that is expecting an explicitly typed array.

You cannot pass an array as an Optional parameter to a subroutine
If you need this type of functionality declare the parameter as Variant and use the IsArray function to test the argument.

Declaring as Variant

It is possible to pass an array of any datatype 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 that accepts Variant parameter will allow you to pass in arrays of any datatype.

You can however place a whole array into a variant, resulting in a single variant variable containing the whole array.
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). This array type must be variant.


Declaring your parameter as Variant() does not allow you to pass an array of any datatype.
You cannot pass an array as an optional parameter. However this can be done if you declare the parameter as Variant.

© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited