How to Return Array From Function in VBA

VBA is a proprietary intermediate language. The full abbreviation of VBA is Visual Basic for Applications, an advanced programming language based and developed by Microsoft.

We can use VBA in different applications such as Access, Excel, Word, Outlook, and PowerPoint.

This article will introduce how to return an array from a function in VBA with an example.

Return Array From Function in VBA

There are many situations where we may want to return an array from a function in VBA. The syntax to return an array from the function is shown below.

# VBA
Function arrayData() As Variant
End Function

We declared a function as a Variant array because it is more comfortable to work with. Let’s go through an example and try to create an array inside a function and return it using the sub in VBA, as shown below.

# VBA
Function arrayData() As Variant
    Dim myArray As Variant
    ReDim myArray(1 To 2, 1 To 3)

    myArray(1, 1) = "Name"
    myArray(1, 2) = "Iqra"
    myArray(1, 3) = "Hasnain"
    myArray(2, 1) = "Name"
    myArray(2, 2) = "Ben"
    myArray(2, 3) = "Stoke"

    arrayData = myArray

End Function
Sub test()
    Dim arr As Variant

    arr = arrayData()

    MsgBox arr(1, 2)
End Sub

Output:

As you can see from the above example, we can easily return an array using a function in VBA using the array as a Variant. But when we get the array inside the sub-procedure and display it using MsgBox, it will be the reference of the array we returned from the function but not the actual array.

So, if we try to change the array, we will also change the reference of the array we got from the function. But if the purpose is only to return the array using a function, we can do it this way.