CODEWITHSUNDEEP
arraysexcelvba
user3786134
user3786134

Reputation: 361

assign one array to another in vba

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.

Upvotes: 1

Views: 13748

Answers (2)

Paul Ogilvie
Paul Ogilvie

Reputation: 25286

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

Upvotes: 4

Brandon Barney
Brandon Barney

Reputation: 2392

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.

Upvotes: 1

Related Questions