assign one array to another in vba

Question

In my project I work with arrays a lot and I would like to find a way to assign the return value of one function (which is array) to another newly declared array. I searched and found a way which suggests that I split the array and the rejoin it into another, but I would like a more precise way of handling this requirement... Let's say the function that is going to return the value:

Public Function GetVariables() As String()
Dim Vars() As String
GetVariables = Vars       
End Function

and the function using it:

Public Function popu()
    Dim Vars() As String
    Set Vars = GetVars()
End Function

but this gives me an error:

can not assign to array

i would be very grateful of your sincere help.

1. do not set a string array. 2. ^^^^^ 3. GetVars <> GetVariables — Scott Craner
– Scott Craner, Commented Nov 1, 2017 at 13:12

Paul Ogilvie · Accepted Answer · 2017-11-01 16:16:00Z

4

As a more elaborate example of your function/sub:

Public Function GetVariables() As String()
    Dim Vars() As String
    ReDim Preserve Vars(2)
    Vars(1) = "hello"
    Vars(2) = "world"
    GetVariables = Vars
End Function

Public Function popu()
    Dim Vars() As String
    Dim i
    Vars = GetVariables()
    For i = 1 To UBound(Vars)
        Debug.Print Vars(i)
    Next i
End Function

prints

hello
world

edited Nov 1, 2017 at 16:16

answered Nov 1, 2017 at 13:21

Paul Ogilvie

25.3k4 gold badges25 silver badges43 bronze badges

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

3 Comments

user3786134 Over a year ago

thank you for your answer but my first array in the function is dynamic..

shrivallabha.redij Over a year ago

How the first array is being built? When you are saying the first array is dynamic then at what point are you deciding you need to populate the second array?

Paul Ogilvie Over a year ago

There is no difference if the array is dynamic. I adapted the example using a dynamic array.

Brandon Barney · Accepted Answer · 2017-11-01 13:29:25Z

1

You're doing a few things wrong here. First, as others have noted in the comments, Set is a special keyword for assigning Object variables, and thus Set will cause an error here.

Next, you must be sure to properly assign back to your function call. Currently your code:

Public Function popu()
    Dim Vars() As String
    Set Vars = GetVars()
End Function

Is doing four things wrong.

Vars() is not equal to Vars().
GetVars() is not equal to Public Function GetVariables()
You shouldn't include an empty parentheses on a function call.
Arrays should be declared as Variant. You can declare an array as a String(), which creates a string array, but this is generally avoided. Addiitonally, you could declare your variable as a Variant() but this will cause issues if you ever try to return a null value (such as vbNullString, or Empty).

Therefore, your code should be:

Public Function GetVariables() As Variant
    Dim Variables As Variant
    GetVariables = Variables 
End Function

' Make sure your function has an explicit returntype, even if that return is variant.
Public Function popu() as Variant
    Dim Variables As Variant
    Variables = GetVariables
End Function

As Paul demonstrates in his answer, your array can be instantiated with dimensions pre-defined, and you can hardcode the values into your array. It definitely depends on your application, but you will want to read the documentation on arrays to understand the different uses and methods for creating them.

answered Nov 1, 2017 at 13:29

Brandon Barney

2,3921 gold badge11 silver badges18 bronze badges

1 Comment

GSerg Over a year ago

This answer is completely backwards. There is no reason to dumb down strongly typed arrays to Variant arrays. It is not a requirement to be able to assign to an array.

Collectives™ on Stack Overflow

assign one array to another in vba

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related