How does one copy 1D array to another 1D array and 2D array?

Question

Suppose I have an array (1,2,3,4), then I want to copy it to another 1D array and 2D array. I use this sub-routine:

Sub CopyArray()
    Dim Arr1(), Arr2()
    ReDim Arr3(1 To m, 1 To n)

    Arr1 = Array(1, 2, 3, 4)

    For i = 1 To 4
        Arr2(i) = Arr1(i)
        Arr3(1, i) = Arr1(i)
    Next i

End Sub

It kept getting an error "subscript out of range". I also tried

Sub CopyArray()
    Dim Arr1(), Arr2()

    Arr1 = Array(1, 2, 3, 4)

    For i = 1 To 4
        Arr2(i) = Arr1(i)
    Next i

End Sub

or

Sub CopyArray()
    Dim Arr1()
    ReDim Arr3(1 To m, 1 To n)

    Arr1 = Array(1, 2, 3, 4)

    For i = 1 To 4
        Arr3(1, i) = Arr1(i)
    Next i

End Sub

but none of them worked. How does one copy 1D array to another 1D array and 2D array properly?

Also if you search google there are lot of answers for this :) — Siddharth Rout
– Siddharth Rout, Commented Jul 29, 2016 at 9:03

Siddharth Rout · Accepted Answer · 2016-07-29 10:14:58Z

3

Simplest way to copy one array to another in your case would be to declare the arrays as Variant. This way you will not have to loop

Example of 1D array

Sub CopyArray()
    Dim x As Variant
    Dim y As Variant

    x = Array(1, 2, 3)

    y = x '<~~ Directly clone the array

    For i = LBound(y) To UBound(y)
        Debug.Print y(i)
    Next i
End Sub

Example of 2D Array

Sub CopyArray()
    Dim x As Variant
    Dim y As Variant

    x = Array(1, 2, 3)

    y = Application.Transpose(x) '<~~ Transpose it

    For i = LBound(y) To UBound(y)
        Debug.Print y(i, 1)
    Next i
End Sub

answered Jul 29, 2016 at 10:14

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

Comments

Rémi · Accepted Answer · 2016-07-29 09:17:16Z

1

First your i has to start at 0 because it is where arrays start. For example Arr1(1) = 2 and not 1 in your code. Secondly you have to Redim your second array so it has the same size as your first one. Here is a piece of code to help you out:

Dim Arr1(), Arr2()

Arr1 = Array(1, 2, 3, 4)
ReDim Arr2(0 To 3)

For i = 0 To 3
    Arr2(i) = Arr1(i)
Next i

answered Jul 29, 2016 at 9:17

Rémi

3723 silver badges8 bronze badges

2 Comments

Tom Over a year ago

Arrays don't have to start at 0. They run from whatever to whatever depending on the boundaries that you set.

Rémi Over a year ago

@Tom I lacked of clarity on this one. I was referring to the index which starts at 0 when you don't set it to a different value.

Anastasiya-Romanova 秀 · Accepted Answer · 2016-07-29 09:48:29Z

The following code works fine:

Sub CopyArray()
    Dim Arr1(), Arr2(3)

    Arr1 = Array(1, 2, 3, 4)

    For i = 0 To 3
        Arr2(i) = Arr1(i)
    Next i

End Sub

Note that the declaration does not specify the number of elements in the array. Instead, it specifies the upper bound of the array. If your module does not contain an Option Base statement, the lower bound is assumed to be zero. So, the declaration above, Dim Arr2(3), is the same as

Dim Arr2(0 To 3) As Variant

You may check that Arr1(1) is equal to 2, not 1 as you might think. Try to add the following line below the Next i to check each element of Arr1:

Debug.Print Arr1(0), Arr1(1), Arr1(2), Arr1(3)

For the best programming practice, you should always explicitly specify both the lower and upper bound for the array, either in the Dim or a ReDim statement. So your first code should be

Sub CopyArray()
Dim Arr1(), Arr2(), Arr3()

Arr1 = Array(1, 2, 3, 4)

ReDim Arr2(UBound(Arr1))
ReDim Arr3(1 To 1, 0 To 3)

For i = LBound(Arr1) To UBound(Arr1)
    Arr2(i) = Arr1(i)
    Arr3(1, i) = Arr1(i)
Next i
    Debug.Print Join(Arr2, ",")
    Debug.Print Arr3(1, 0), Arr3(1, 1), Arr3(1, 2), Arr3(1, 3)
End Sub

Note: The function Debug.Print writes values to the Immediate Window to see the output of your code. To view this window select View ►Immediate Window from the menu or use keyboard shortcut CtrlG.

Pierre Chevallier · Accepted Answer · 2016-07-29 10:18:26Z

1

Here you have two things to keep in mind.

Firstly, you have to give a dimension to Arr2 via ReDim, for example you can do

ReDim Arr2(ubound(Arr1))

Before the For loop, since it has no dimension beforehand: Dim Arr2() declares an array that you have to change in dimension afterwards.

Secondly, since arrays start at 0 in VBA, you are having an issue populating the data from Arr1 that ranges from 0 to 3 and Arr3 that ranges from 1 to 4 (from the declaration ReDim Arr3(1 To m, 1 To n)). A good practice would have to use either all arrays starting at 0 or 1 but not both.

You can enforce the array dimension by using the Option Base 1 command (here look at the documention from MSDN ) and then all your arrays will start at 1.

As a consequence you can have it in two ways:

1- Using Base 1

Option Base 1

Sub CopyArray()

    Dim Arr1(), Arr2(), Arr3()

    Arr1 = Array(1, 2, 3, 4)

    'Since I assume you already have values for m and n, I give values for the code to work in our example
    m = 1
    n = 4

    ReDim Arr3(m, n)
    ReDim Arr2(ubound(Arr1))

    For i = LBound(Arr1) To UBound(Arr1)
        Arr2(i) = Arr1(i)
        Arr3(1, i) = Arr1(i)
    Next i

End Sub

2- Keeping the 0 lower bound

Sub CopyArray()

    Dim Arr1(), Arr2(), Arr3()

    Arr1 = Array(1, 2, 3, 4)

    'Same as above
    m = 1
    n = 4

    ReDim Arr3(m, n)
    ReDim Arr2(ubound(Arr1))

    For i = LBound(Arr1) To UBound(Arr1)
        Arr2(i) = Arr1(i)
        Arr3(1, i) = Arr1(i)
    Next i

End Sub

Notice that there is little difference between both codes, but one has all its arrays starting at 1 and not 0.

I'd recommend you to read also Lbound and Ubound for the lower and upper bound of arrays.

edited Jul 29, 2016 at 10:18

answered Jul 29, 2016 at 9:45

Pierre Chevallier

7649 silver badges27 bronze badges

2 Comments

Anastasiya-Romanova 秀 Over a year ago

Both of your code doesn't work. The same error as the OP's occurred.

Pierre Chevallier Over a year ago

Thanks, I added value to m and n for this example to work.

Collectives™ on Stack Overflow

How does one copy 1D array to another 1D array and 2D array?

4 Answers 4

Comments

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related