wilmott.com

I am trying to write a fairly simple function in VBA which takes in a large array (eg 3000 rows and 160 columns) from excel, does some moderately simple manipulations and calculations and returns the results back into excel via a large array (of similar size). It seems that it crashes when I increase the number of rows above about 500.So then I tried a slightly different tack and re-wrote my function as a Sub. It reads the input by using eg value = Range("A3").Offset(i,j).Value for various i and j. However, it crashes whenever i is more than 533. Is there a limit in excel? A limit of 533 rows looks crazy - I am sure many people must be using bigger arrays than that? Does anyone know or have a work-around, please? Basically, I am a novice at VBA but I tried every way possible, googled for help and I cant seem to get it to work.If it helps, the arrays are doubles.Can anyone help, please? Any advice on a simple work-around would be much appreciated (the intended application is not speed critical so anything that works is fine).

Note sure what your issue could be, this test function works fine here (Excel 2003) with 3000 rows, 160 cols. Using offset is a bit odd here though, usually you're better off passing in the entire range you want to operate on in as a parameter. Public Function testBigArray(arrayIn As Range, rows As Long, cols As Long) As VariantDim result() As DoubleDim i As LongDim j As LongReDim result(0 To rows - 1, 0 To cols - 1)For i = 0 To rows - 1 For j = 0 To cols - 1 result(i, j) = arrayIn.Offset(i, j).Value2 Next jNext itestBigArray = resultEnd Function

If you want to work with large arrays of doubles, you should use xll's, not VBA. Man up, squint your eyes, and use C++. Try out the link in my sig.

Thanks, adcockj. I wonder if it the size of my spreadsheet? I will have a go at what you suggested. BTW, is there any difference between arrayIn.Offset(i, j).Value2andarrayIn.Offset(i, j).Value ?

QuoteOriginally posted by: dougal12BTW, is there any difference between arrayIn.Offset(i, j).Value2andarrayIn.Offset(i, j).Value ?Not much, using Value2 avoids some wierd conversions you sometimes get with dates/currency formatted values and iirc is faster.John

The most efficient way to do that in VBA is to Dim myArray As VariantmyArray = Sheets("Sheet1").Range("A1:IV3000")myArray(1, 1) = "Another value"Sheets("Sheet1").Range("A1:IV3000") = myArrayThis might fail if one of the cell in the range returns an error.Looping over the cells within the range is MUCH slower.AFAIK the only size limitation should be the memory that Excel can get (I have used it for very large arrays).