VBA Copying, returning and passing arrays Returning Arrays from Functions
Example
A function in a normal module (but not a Class module) can return an array by putting () after the data type.
Function arrayOfPiDigits() As Long()
Dim outputArray(0 To 2) As Long
outputArray(0) = 3
outputArray(1) = 1
outputArray(2) = 4
arrayOfPiDigits = outputArray
End Function
The result of the function can then be put into a dynamic array of the same type or a variant. The elements can also be accessed directly by using a second set of brackets, however this will call the function each time, so its best to store the results in a new array if you plan to use them more than once
Sub arrayExample()
Dim destination() As Long
Dim var As Variant
destination = arrayOfPiDigits()
var = arrayOfPiDigits
Debug.Print destination(0) ' outputs 3
Debug.Print var(1) ' outputs 1
Debug.Print arrayOfPiDigits()(2) ' outputs 4
End Sub
Note that what is returned is actually a copy of the array inside the function, not a reference. So if the function returns the contents of a Static array its data can't be changed by the calling procedure.
Outputting an Array via an output argument
It is normally good coding practice for a procedure's arguments to be inputs and to output via the return value. However, the limitations of VBA sometimes make it necessary for a procedure to output data via a ByRef argument.
Outputting to a fixed array
Sub threePiDigits(ByRef destination() As Long)
destination(0) = 3
destination(1) = 1
destination(2) = 4
End Sub
Sub printPiDigits()
Dim digits(0 To 2) As Long
threePiDigits digits
Debug.Print digits(0); digits(1); digits(2) ' outputs 3 1 4
End Sub
Outputting an Array from a Class method
An output argument can also be used to output an array from a method/proceedure in a Class module
' Class Module 'MathConstants'
Sub threePiDigits(ByRef destination() As Long)
ReDim destination(0 To 2)
destination(0) = 3
destination(1) = 1
destination(2) = 4
End Sub
' Standard Code Module
Sub printPiDigits()
Dim digits() As Long
Dim mathConsts As New MathConstants
mathConsts.threePiDigits digits
Debug.Print digits(0); digits(1); digits(2) ' outputs 3 1 4
End Sub
