Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select a single "column" of a VBA array?
Hi all
I have a function that creates a 2-D array within VBA and stores this as part of an object's data. With another function I would like to access just a specific "column" of that array. if the array was stored in an excel sheet then I could use worksheetfunction.index but this does not seem to work on a vba array (and the array is way too big to put into a spreadsheet plus that would be a ridiculously slow method). So, before I write another function to pull out the "column" I want into another array is there something already within VBA I can use? I am using Excel 2003. Thanks a lot Lloyd |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select a single "column" of a VBA array?
Actually you can use Index to return a specific column of an array...
Sub CreateSuperSizeMeArray() Dim arr(1 To 50000, 1 To 5) Dim varCol As Variant Dim j As Long Dim i As Long For i = 1 To 50000 For j = 1 To 5 arr(i, j) = i + j * 100 Next j Next i varCol = Application.Index(arr, 0, 3) MsgBox varCol(1, 1) & vbCr & _ Application.Index(Application.Index(arr, 0, 3), 25000, 1) & _ vbCr & varCol(50000, 1) End Sub ------------ Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "pinkfloydfan" wrote in message Hi all I have a function that creates a 2-D array within VBA and stores this as part of an object's data. With another function I would like to access just a specific "column" of that array. if the array was stored in an excel sheet then I could use worksheetfunction.index but this does not seem to work on a vba array (and the array is way too big to put into a spreadsheet plus that would be a ridiculously slow method). So, before I write another function to pull out the "column" I want into another array is there something already within VBA I can use? I am using Excel 2003. Thanks a lot Lloyd |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select a single "column" of a VBA array?
Just to add that this fails for an array 65536 rows.
Since the OP said it was too large to put on a worksheet. (which could mean it is larger than the limit) -- regards, Tom Ogilvy "Jim Cone" wrote in message ... Actually you can use Index to return a specific column of an array... Sub CreateSuperSizeMeArray() Dim arr(1 To 50000, 1 To 5) Dim varCol As Variant Dim j As Long Dim i As Long For i = 1 To 50000 For j = 1 To 5 arr(i, j) = i + j * 100 Next j Next i varCol = Application.Index(arr, 0, 3) MsgBox varCol(1, 1) & vbCr & _ Application.Index(Application.Index(arr, 0, 3), 25000, 1) & _ vbCr & varCol(50000, 1) End Sub ------------ Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "pinkfloydfan" wrote in message Hi all I have a function that creates a 2-D array within VBA and stores this as part of an object's data. With another function I would like to access just a specific "column" of that array. if the array was stored in an excel sheet then I could use worksheetfunction.index but this does not seem to work on a vba array (and the array is way too big to put into a spreadsheet plus that would be a ridiculously slow method). So, before I write another function to pull out the "column" I want into another array is there something already within VBA I can use? I am using Excel 2003. Thanks a lot Lloyd |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select a single "column" of a VBA array?
Basically the index is maxed at Long number limit. I don't think any array
can that more than that. I guess you can create your own 2D Matrix using double as index and go beyound the limit. And use Array of Arrays to store more data. Like have a MyArray(65536 , 65536) And put a lot of them in A collection(MyArray) And calculate the double index to map your matrix This is a bad example because array like that waste a lot of memory. Anyway, you get the idea. "Tom Ogilvy" wrote: Just to add that this fails for an array 65536 rows. Since the OP said it was too large to put on a worksheet. (which could mean it is larger than the limit) -- regards, Tom Ogilvy "Jim Cone" wrote in message ... Actually you can use Index to return a specific column of an array... Sub CreateSuperSizeMeArray() Dim arr(1 To 50000, 1 To 5) Dim varCol As Variant Dim j As Long Dim i As Long For i = 1 To 50000 For j = 1 To 5 arr(i, j) = i + j * 100 Next j Next i varCol = Application.Index(arr, 0, 3) MsgBox varCol(1, 1) & vbCr & _ Application.Index(Application.Index(arr, 0, 3), 25000, 1) & _ vbCr & varCol(50000, 1) End Sub ------------ Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "pinkfloydfan" wrote in message Hi all I have a function that creates a 2-D array within VBA and stores this as part of an object's data. With another function I would like to access just a specific "column" of that array. if the array was stored in an excel sheet then I could use worksheetfunction.index but this does not seem to work on a vba array (and the array is way too big to put into a spreadsheet plus that would be a ridiculously slow method). So, before I write another function to pull out the "column" I want into another array is there something already within VBA I can use? I am using Excel 2003. Thanks a lot Lloyd |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select a single "column" of a VBA array?
Tom,
Thanks for that; I have added a comment to my notes on indexing Arrays. The following is all I could come up with to "pull out" a column from a larger array. It does work almost instantly... Regards, Jim Cone San Francisco, USA -------------------------- Sub CreateSuperSizeMeArray_R1() Dim arr(1 To 66000, 1 To 5) As Long Dim arrCol() As Long Dim j As Long Dim i As Long For i = 1 To 66000 For j = 1 To 5 arr(i, j) = i + j * 100 Next j Next i 'Create single column array containing the 3rd column of arr. ReDim arrCol(LBound(arr, 1) To UBound(arr, 1), 1 To 1) For i = LBound(arr, 1) To UBound(arr, 1) arrCol(i, 1) = arr(i, 3) Next 'i MsgBox arrCol(1, 1) & vbCr & _ arrCol(33000, 1) & vbCr & _ arrCol(66000, 1) End Sub '------------ "Tom Ogilvy" wrote in message Just to add that this fails for an array 65536 rows. Since the OP said it was too large to put on a worksheet. (which could mean it is larger than the limit) -- regards, Tom Ogilvy "Jim Cone" wrote in message Actually you can use Index to return a specific column of an array... Sub CreateSuperSizeMeArray() Dim arr(1 To 50000, 1 To 5) Dim varCol As Variant Dim j As Long Dim i As Long For i = 1 To 50000 For j = 1 To 5 arr(i, j) = i + j * 100 Next j Next i varCol = Application.Index(arr, 0, 3) MsgBox varCol(1, 1) & vbCr & _ Application.Index(Application.Index(arr, 0, 3), 25000, 1) & _ vbCr & varCol(50000, 1) End Sub ------------ Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "pinkfloydfan" wrote in message Hi all I have a function that creates a 2-D array within VBA and stores this as part of an object's data. With another function I would like to access just a specific "column" of that array. if the array was stored in an excel sheet then I could use worksheetfunction.index but this does not seem to work on a vba array (and the array is way too big to put into a spreadsheet plus that would be a ridiculously slow method). So, before I write another function to pull out the "column" I want into another array is there something already within VBA I can use? I am using Excel 2003. Thanks a lot Lloyd |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select a single "column" of a VBA array?
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, another way is Sub CreateSuperSizeMeArray_R1() Dim arr(1 To 66000, 1 To 5) As Long Dim arrCol() As Long Dim j As Long Dim i As Long For i = 1 To 66000 For j = 1 To 5 arr(i, j) = i + j * 100 Next j Next i 'Create single column array containing the 3rd column of arr. newArray = SubArray(arr, 3, 3, 1, 66000) End Sub Jim Cone wrote: Tom, Thanks for that; I have added a comment to my notes on indexing Arrays. The following is all I could come up with to "pull out" a column from a larger array. It does work almost instantly... Regards, Jim Cone San Francisco, USA -------------------------- Sub CreateSuperSizeMeArray_R1() Dim arr(1 To 66000, 1 To 5) As Long Dim arrCol() As Long Dim j As Long Dim i As Long For i = 1 To 66000 For j = 1 To 5 arr(i, j) = i + j * 100 Next j Next i 'Create single column array containing the 3rd column of arr. ReDim arrCol(LBound(arr, 1) To UBound(arr, 1), 1 To 1) For i = LBound(arr, 1) To UBound(arr, 1) arrCol(i, 1) = arr(i, 3) Next 'i MsgBox arrCol(1, 1) & vbCr & _ arrCol(33000, 1) & vbCr & _ arrCol(66000, 1) End Sub '------------ "Tom Ogilvy" wrote in message Just to add that this fails for an array 65536 rows. Since the OP said it was too large to put on a worksheet. (which could mean it is larger than the limit) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what do you call the "button" that lets you select a single item | Excel Worksheet Functions | |||
Change data in a single column from "last, first" to "first last" | Excel Discussion (Misc queries) | |||
If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming | |||
select & format "single" axis line in charts | Charts and Charting in Excel | |||
Copy column range of "single word" cells with spaces to a single c | Excel Discussion (Misc queries) |