How to copy VBA array into range of cells?

[curiousgeorge408]

What is the syntax for copying a VBA array into a range of cells?

I know I can copy into each cell individually in a for-loop. I am
wondering if there is a way to do it en masse in a single statement.

For example:

dim xdata(10) as variant
for i = 1 to 10: xdata(i-1) = i: next i
range("a1:a10") = xdata

That does not work the way that I want. It copies xdata(0) into each
of A1:A10 instead of copying xdata(1) into A2, xdata(2) into A3, etc.
I want the latter.

 

[Gary''s Student]

Sub justdoit()
s = Array(1, 2, 3)
Range("A1:C1") = s
End Sub

in a column, you should transpose

 

[Alan Beban]

What is the syntax for copying a VBA array into a range of cells?

I know I can copy into each cell individually in a for-loop. I am
wondering if there is a way to do it en masse in a single statement.

For example:

dim xdata(10) as variant
for i = 1 to 10: xdata(i-1) = i: next i
range("a1:a10") = xdata

That does not work the way that I want. It copies xdata(0) into each
of A1:A10 instead of copying xdata(1) into A2, xdata(2) into A3, etc.
I want the latter.

First, you should declare xdata Dim xdata(0 to 10) or Dim xdata(1 to
10), whichever you mean. I assume you mean 0 to 10, since for i=1
xdata(i-1)=0, although the loop then leaves xdata(10) [the eleventh
element] empty.

In any event, you're trying to copy a horizontal array into a vertical
range. The following will copy the first 10 elements of the array into
a1:a10:

range("a1:a10") = Application.Transpose(xdata)

Alan Beban

 

[curiousgeorge408]

In any event, you're trying to copy a horizontal array into a vertical
range. The following will copy the first 10 elements of the array into
a1:a10:
range("a1:a10") = Application.Transpose(xdata)

Thanks. Based on that, I discovered that the following works fine:

dim xdata(10,0) as variant
[...assign values to xdata(i,0)...]
range("a1:a10") = xdata

I am surprised that if xdata(10) specifies a row spanning 10 columns,
xdata(10,0), not xdata(0,10), specifies a column spanning 10 rows. Oh
well.

I also learned that the following is one way (best? only?) to copy a
range into a VBA array:

dim xdata as variant
xdata = range("a1:a10")

But that is equivalent to declaring xdata(1 to 10, 1 to 1).

(I finally found the information in my reference book.)

Thanks again.

 

[Alan Beban]

. . .
I am surprised that if xdata(10) specifies a row spanning 10 columns,
xdata(10,0), not xdata(0,10), specifies a column spanning 10 rows. Oh
well. . . .

Static arrays can be declared by specifying the lower bound and upper
bound of each dimension, or by specifying only the upper bound and
leaving the lower bound to be determined by the Option Base statement or
its default (which is 0).

So, for 1-dimensional arrays, the bounds of the array refers to the
number of the columns, since by definition there is only one row. Dim
xdata(10) is the equivalent of Dim xdata(1 to 10) or Dim xdata(0 to 10).
In all those cases, the 10 refers to the upper bound in the columns
"direction".

Correlatively, for 2-dimensional arrays, Dim xdata(10,0) is declaring
only the upper bound of each dimension, leaving to the Option Base
statement or its default, the determination of the lower bounds, and is
equivalent to Dim xdata(0 to 10, 0 to 0). Similarly, Dim xdata(0,10) is
the equivalent of Dim xdata(0 to 0, 0 to 10). By the way, since 0 is
specified as an upper bound, both declarations will fail if the Option
Base 1 statement is in effect.

Regards,
Alan Beban