Return an array from function in VBA

Question

I want to code a function that accepts an array as an argument. Then the function fills some elements. Finally, the array filled by the function becomes visible from the caller.

sub main1()
    Dim A(1,3) as string
    F(A) '???How to get the value of each array element here?
    ...
End Sub

Function F(X(1,3) as string) string()
    X(1,1)=3.14
End Function

I looked at the following post but did not get it, somehow the questions are not the same.

return array from function in VBA

use the variant data type in your method signature Function Blah(myArray as Variant) as Variant — Sorceri
– Sorceri, Commented Jun 22, 2016 at 20:59
if you read the answers to the question you linked to carefully, you will see where you need to adjust your code. — Scott Holtzman
– Scott Holtzman, Commented Jun 22, 2016 at 21:00
@Sorceri, correct, defining F(X as VARIANT) AS VARIANT then assigning the call result to a Variant makes data appear inside the passed parameter!!! Thank you. I would mark this an answer if your comment as an answer. — NoChance
– NoChance, Commented Jun 22, 2016 at 21:11

Jeremy · Accepted Answer · 2016-06-22 21:20:22Z

3

One solution is to just pass the array ByRef

Sub main1()
    Dim A(1, 3) As String
    Call F(A)
    Debug.Print A(1, 1)
End Sub
Sub F(ByRef x() As String)
    x(1, 1) = 3.14
End Sub

edited Jun 22, 2016 at 21:20

answered Jun 22, 2016 at 21:09

Jeremy

4,8582 gold badges23 silver badges24 bronze badges

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

3 Comments

NoChance Over a year ago

Yes this works perfectly and I could use the original type without using a Variant. Very good.

Jeremy Over a year ago

@NoChance thanks. I also modified my answer slightly to change the Array populator as a Sub, not a Function as it returns no value.

NoChance Over a year ago

This is also correct, thanks again. It is good idea to send data byRef for efficiency but it is harmful to let the called program to change it. Anyway, it looks like the universe has managed to survive without my wisdom!

Collectives™ on Stack Overflow

Return an array from function in VBA

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related