VBA Function Return Array

This article will demonstrate how to return an Array using a VBA Function.

VBA Function Return Array

When using functions to return arrays, I strongly recommend declaring arrays with type variant:

    Function ReturnArray() As Variant

    End Function

Variant Arrays are easier to work with. Array size becomes less of a concern.

Function Return Array Examples

Here is an example of a function that returns an array:

Function ReturnArray() As Variant
    Dim tempArr As Variant
    
    'Create New Temp Array
    ReDim tempArr(1 To 3, 1 To 2)
    
    'Assign Array Values
    tempArr(1, 1) = "Steve"
    tempArr(1, 2) = "Johnson"
    tempArr(2, 1) = "Ryan"
    tempArr(2, 2) = "Johnson"
    tempArr(3, 1) = "Andrew"
    tempArr(3, 2) = "Scott"
    
    'Output Array
    ReturnArray = tempArr
    
End Function
 
Sub TestTransposeArray()
    Dim outputArr As Variant
    
    'Call Return Function
    outputArr = ReturnArray()
    
    'Test Output
    MsgBox outputArr(2, 1)
 
End Sub

Notice we declared the Arrays with data type = variant to avoid size issues.

This example takes an array as an input, transposes the array, and outputs the new transposed array:

Function TransposeArray(MyArray As Variant) As Variant
    Dim x As Long, y As Long
    Dim maxX As Long, minX As Long
    Dim maxY As Long, minY As Long
    
    Dim tempArr As Variant
    
    'Get Upper and Lower Bounds
    maxX = UBound(MyArray, 1)
    minX = LBound(MyArray, 1)
    maxY = UBound(MyArray, 2)
    minY = LBound(MyArray, 2)
    
    'Create New Temp Array
    ReDim tempArr(minX To maxX, minY To maxX)
    
    'Transpose the Array
    For x = minX To maxX
        For y = minY To maxY
            tempArr(y, x) = MyArray(x, y)
        Next y
    Next x
    
    'Output Array
    TransposeArray = tempArr
    
End Function

Sub TestTransposeArray()
    Dim testArr(1 To 3, 1 To 2) As Variant
    Dim outputArr As Variant
    
    'Assign Array Values
    testArr(1, 1) = "Steve"
    testArr(1, 2) = "Johnson"
    testArr(2, 1) = "Ryan"
    testArr(2, 2) = "Johnson"
    testArr(3, 1) = "Andrew"
    testArr(3, 2) = "Scott"
    
    'Call Transpose Function
    outputArr = TransposeArray(testArr)
    
    'Test Output
    MsgBox outputArr(2, 1)

End Sub

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!