Returning an array from a function in VBA

Question

I having trouble getting this to work. I just want to return an array from a function, the code i've tried is below.

Sub 
   Dim storeData As Variant: Set storeData = getData
   Debug.Print storeData(1)
End Sub

Function getData() As Variant
   Dim arr(2) As Variant
    arr(1) = "ergreg"
    arr(2) = "1005"
    getData = arr
End Function

No errors are thrown but nothing is printed to the immediate window

@ChrisBull see answer below (your Debug.Print storeData(1) will only print the 2nd of the 3 elements in your array) — Shai Rado
@Comintern I didn't have Option Explicit set, another lesson learnt — ChrisBull

Shai Rado Shai Rado · Accepted Answer · 2016-09-04T13:59:27

If you want to print all the array elements, you need to add a For loop to the Debug:

Dim storeData As Variant
Dim i As Long
storeData = getData

For i = LBound(storeData) To UBound(storeData)
    Debug.Print storeData(i)
Next i

Quick Note: Dim arr(2) As Variant , means arr has 3 elements (starting from 0), you are assigning values only to the second and third elements.

Returning an array from a function in VBA

2 Answers