How to select a single "column" of a VBA array?

[pinkfloydfan]

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

 

[Jim Cone]

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"
<[email protected]>
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

 

[Tom Ogilvy]

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)

 

[Guest]

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.

 

[Jim Cone]

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" <[email protected]>
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" <[email protected]>
wrote in message
Actually you can use Index to return a specific column of an array...

 

[Alan Beban]

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