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