Stats

Contributors: 1 Tuesday, February 14, 2017
Licensed under: CC-BY-SA
Not affiliated with Stack Overflow
Rip Tutorial: riptutorial@gmail.com
Roadmap: roadmap

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