Assigning an array to another array in one line

Question

I'm new to VBA, and I can't seem to work out a very simple concept - assigning one array to another, both being of equal size and type. Like in this example:

Option Explicit

Sub main()
    Dim arr1(2), arr2(2) As Double

    arr1(0) = 5
    arr1(1) = 10

    arr2 = arr1 'error here

    Debug.Print arr2(0)
    Debug.Print arr2(0)
End Sub

Running this returns an error

"Can't assign to array"

Now, I know I can iterate through every element with a For loop, but in some advanced cases, it is impractical to use - for example, I have a slow-loading function that returns an array, and because of that, I'd like to run it only once, taking it's whole return value and assigning to some other array, like this:

arr1 = Very_Slow_Function_That_Returns_An_Array()

But obviously, this won't work either, and will produce the same error. So, what can be done? Can someone give some advice on how to assign a whole array to another array without having to iterate through every element?

You are defining arr1 as a Variant and arr2 as a Double - That's a problem. — braX
– braX, Commented Mar 13, 2018 at 17:37
This us is a nearly-XY problem: you may want to inquire why ‘Very_Slow_Function_That_Returns_An_Array()’ is so slow it instead! — DisplayName
– DisplayName, Commented Mar 13, 2018 at 19:55

drec4s · Accepted Answer · 2018-03-13 17:28:42Z

2

You can assign an array to another in VBA like this:

Option Explicit

Sub main()

Dim arr1 As Variant
Dim arr2 As Variant

arr1 = Array(5, 10)

'Assign array1 to array2
arr2 = arr1

Debug.Print arr1(0)
Debug.Print arr2(0)
End Sub

answered Mar 13, 2018 at 17:28

drec4s

8,1178 gold badges35 silver badges56 bronze badges

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

1 Comment

J R Over a year ago

But then the data type of the arr2 is still a Variant. How do I convert it to Double without having to iterate through every element?

Darren Bartrup-Cook · Accepted Answer · 2018-03-14 10:52:55Z

1

These variations work:

Sub main()

    Dim arr1() As Double, arr2() As Double

    ReDim arr1(0 To 1)
    arr1(0) = 5
    arr1(1) = 10

    arr2 = arr1

End Sub

or

Sub main()

    Dim arr1() As Variant, arr2() As Variant

    arr1 = Array(5, 10)

    arr2 = arr1

End Sub

Third variation which is closest to OP code:

Sub main()
    Dim arr1(0 To 1) As Double, arr2() As Double

    arr1(0) = 5
    arr1(1) = 10

    arr2 = arr1 'no error here now.

End Sub

edited Mar 14, 2018 at 10:52

answered Mar 13, 2018 at 17:29

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

7 Comments

J R Over a year ago

But then the data type of the arr2 is still a Variant. How do I convert it to Double without having to iterate through every element?

Darren Bartrup-Cook Over a year ago

The first block of code will work as double. I'll update it.

J R Over a year ago

Wow. It does actually work like that. But why does it work when written this way, as opposed to my original example?

Darren Bartrup-Cook Over a year ago

I thought at first it could be because you declare arr2 as a double while leaving arr1 as a variant (you need to declare the data type of each variable), then thought it's because your array has three elements (0 to 2) and then discovered that your code will work if you declare both as doubles and leave the size of arr2 empty. I'll add as a third variation to my answer.

J R Over a year ago

I tried it now, and discovered that declaring as this -> Dim arr1(2) As Double, arr2() As Double , also works. But why? Can you explain why is it needed to omit the size of the arr2 to make this work?

|

Collectives™ on Stack Overflow

Assigning an array to another array in one line

2 Answers 2

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related