ExcelBanter - View Single Post - What is the fastest way to copy a range to a 2D array?

equiangular

Thanks v. much.

Jon Peltier wrote:
Use a variant array to quickly capture the values from the sheet. Then use a
couple loops to populate a single array:

redim SingleArray(lbound(VariantArray,1) to ubound(VariantArray,1),
lbound(VariantArray,2) to ubound(VariantArray,2))
for i=lbound(VariantArray,1) to ubound(VariantArray,1)
for j=lbound(VariantArray,2) to ubound(VariantArray,2)
SingleArray(i,j) = CSng(VariantArray(i,j)
Next
Next

Use the resulting single array in AddPolyline.

Is this for a chart? If so, try this:

http://peltiertech.com/Excel/Charts/VBAdraw.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

"equiangular" wrote in message
...
Hi Jon,

I wanna write a sub that can draw a polyline with coordinates defined by a
selected range. I used your method first but got run time error on
Addpolyline(arr). I need to use single array to avoid the error.

Jon Peltier wrote:
arr is a variant array, which the OP didn't want. If you try to declare
arr() as anything but a variant, you get a type mismatch error in
arr = r.Value

To the OP: Why don't you want a variant array? They are the fastest way
to get data from a worksheet into VBA. If you need a typed array, you
could use a variant array to get the data, then transfer the data into
your typed array, and this would be much faster than looping cell by cell
to populate a typed array. I'm not sure if using the typed array makes
much difference if you have to process it into the typed array before
using the typed data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

"Gary''s Student" wrote in
message ...
Sub equiangular()
Dim r As Range
Set r = Range("A1:C13")
ReDim arr(1 To 13, 1 To 3)
arr = r.Value
End Sub

is one way
--
Gary's Student
gsnu200705

"equiangular" wrote:

I do not want a variant array.