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
– Shai Rado, Commented Sep 4, 2016 at 14:02
@Comintern I didn't have Option Explicit set, another lesson learnt — ChrisBull
– ChrisBull, Commented Sep 4, 2016 at 14:08

SandPiper · Accepted Answer · 2016-09-04 21:14:04Z

2

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.

edited Sep 4, 2016 at 21:14

SandPiper

2,9145 gold badges38 silver badges59 bronze badges

answered Sep 4, 2016 at 13:59

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ChrisBull Over a year ago

Thanks for replying, I have the base set as 1. And i don't need to print all the values, just needed the one for now.

ChrisBull · Accepted Answer · 2016-09-04 14:01:40Z

-1

My mistake - Thanks @GSerg for finding it.

Just need to remove the 'Set'. So simple

answered Sep 4, 2016 at 14:01

ChrisBull

4676 silver badges24 bronze badges

Collectives™ on Stack Overflow

Returning an array from a function in VBA

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related