Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the fastest way to copy a range to a 2D array?
I do not want a variant array.
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fastest way to select large range (e.g. B3:F1002)? | Excel Discussion (Misc queries) | |||
copy one array formula to an array range | Excel Programming | |||
Fastest way to find item in an array. | Excel Programming | |||
fastest sorting routine for 2-D array of long values | Excel Programming | |||
How Can I copy all value of the array into the range? | Excel Programming |