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, Nov 01 '17 at 13:12

Paul Ogilvie · Answer 1 · 2017-11-01T16:16:00.863

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 01 '17 at 16:16

answered Nov 01 '17 at 13:21

Paul Ogilvie

25,048
4
23
41

thank you for your answer but my first array in the function is dynamic.. – user3786134 Nov 01 '17 at 13:29
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? – shrivallabha.redij Nov 01 '17 at 13:54
There is no difference if the array is dynamic. I adapted the example using a dynamic array. – Paul Ogilvie Nov 01 '17 at 16:16

score 1 · Accepted Answer · answered Nov 01 '17 at 13:29

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.

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](https://stackoverflow.com/a/8844294/11683). — GSerg, Jul 20 '21 at 19:50

assign one array to another in vba

2 Answers2