size limitations of VBA arrays?

hi all,

I have previously read somewhere that entire spreadsheets can be stored as VBA arrays but am currently reading "Excel 2003: Programming Inside Out" by Curtis Frye, Wayne Freeze & Felicia Buckingham and p185 states...

Copying Data Between Ranges and Arrays
All of the information within a workbook is easily available for manipulation through a VBA macro. Why would you want to copy that information to someplace else before working with it? Speed. It’s a very time-consuming process for VBA to read or write information to a worksheet. By minimizing the number of times that VBA needs to read or write to the worksheet, you can greatly reduce the amount of time needed for your procedure to operate. How can you reduce the number of read and writes to the worksheet?
By reading or writing a range of cells at a time. It’s the setup time that VBA needs to access a worksheet that takes time. Unfortunately, VBA goes through the same setup process every time it needs to read or write another range, whether the range consists of only one cell or several hundred cells.
Warning As with most things, there is a point of diminishing returns or outright failure. VBA cannot transfer more than about 3000 cells at one time. As long as you stay well below that number, you should have no problems. So the question now becomes how can you read or write to multiple cells at one time? Transferring multiple cells between a workbook and VBA is done through the use of variant arrays...

Has anyone experienced VBA erroring when attempting to transfer from a VBA array back to a spreadsheet (after performing "in memory" calculations)?
If so, how did/do you overcome the error?
For example, do you limit the size of the initial VBA array to a smaller size?
Or do you still use a large VBA array but somehow limit the amount that is written back to the spreadsheet in one go?

I will try testing this myself but appreciate other's thoughts & experiences.

Thanks
Rob

Hello Rob,

Warning As with most things, there is a point of diminishing returns or outright failure. VBA cannot transfer more than about 3000 cells at one time.

I can't vouch for this statement being true. Arrays do have limits as to the number of dimensions. As for overall size, that is limited by available memory.

The reason I have a hard time with this statement is Windows has an API function it uses for block transfers within memory. There is no limit on the number of bytes that can be transferred in a single block move. Perhaps the memory manager will restrict this as the maximum time allowed for a process cycle is 20 msec before it must yield to another process. Given current CPU speeds and memory speeds, I would say it is a fair bet 3000 cells is on the low side.

I have never encountered this error and never heard of such an error till now. Speed can be increased by dimensioning the array to the type of data it will hold. Variant arrays will hold any type of information and require more memory than other data types. Consequently, these factors will slow down the process. So, use a Variant only when you can't use anything else.

Thanks Leith for the reassuring feedback. I also have/had a misconception that memeory to range transfers could only occur with variant arrays, but I'll check this out some more because in other cases I do try to explicitly identify variables specifically for the memory requirements :-)

Rob

hi,

I haven't got around to testing for my initial questions but while researching a separate matter I came across the below KB Alert article which supports a limit where Transpose is used when writing an array back to the spreadsheet.http://kbalertz.com/177991/Limitatio...utomation.aspx

Speed can be increased by dimensioning the array to the type of data it will hold. Variant arrays will hold any type of information and require more memory than other data types. Consequently, these factors will slow down the process. So, use a Variant only when you can't use anything else.

Leith (or others),
How would you change the code I have provided in post #4 of http://www.excelforum.com/excel-prog...o-formula.html to dimension the array (based on values in the OP's uploaded file, I'm assuming...) to Long?


Thanks
Rob