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