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