What is the fastest way to copy a range to a 2D array?
 

I do not want a variant array.

What is the fastest way to copy a range to a 2D array?
 

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.

What is the fastest way to copy a range to a 2D array?
 

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.

What is the fastest way to copy a range to a 2D array?
 

Just a heads up:

The OP said:
I do not want a variant array.

I don't know why the OP has this requirement, but with your suggestion:

Sub equiangular()
Dim r As Range
Set r = Range("A1:C13")
ReDim arr(1 To 13, 1 To 3)
arr = r.Value
MsgBox TypeName(arr)
End Sub

produces Variant()

If you give
ReDim arr(1 To 13, 1 To 3) as anything but variant, you get a type mismatch
error

I think you have to loop if the OP doesn't want a variant array.

In all versions of excel, picking up a range from a worksheet in one command
requires a variant and in xl2000 and later, that can also be a variant
array.

--
Regards,
Tom Ogilvy

"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.

What is the fastest way to copy a range to a 2D array?
 

Aside from the fact that the OP said he does not want a variant array,
my simple minded test suggests that the following is faster that what
you posted:

Dim r As Range
Set r = Range("A1:C13")
Dim arr2
arr2 = r.Value

Gary''s Student wrote:
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

What is the fastest way to copy a range to a 2D array?
 

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.

What is the fastest way to copy a range to a 2D array?
 

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, the
following will transfer the data from A1:C4 to a 4x3 array of type
Integer() (if each element of the range is an integer):

Dim arr() As Integer
Assign Range("A1:C4"),arr

Alan Beban

What is the fastest way to copy a range to a 2D array?
 

Hi Alan,

Thanks for your info. The code just assign the range values to the array
one by one. I just wonder if there's any more efficient way to do this.

Alan Beban wrote:
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, the
following will transfer the data from A1:C4 to a 4x3 array of type
Integer() (if each element of the range is an integer):

Dim arr() As Integer
Assign Range("A1:C4"),arr

Alan Beban

What is the fastest way to copy a range to a 2D array?
 

I have just benchmark the code. Your method is the fastest. Thanks all.

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.

What is the fastest way to copy a range to a 2D array?
 

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.

What is the fastest way to copy a range to a 2D array?
 

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.