Ivan, more help with saving arrays please please please!!


Posted by Anna Daly on November 13, 2000 3:23 AM

Hi Ivan,

I didn't really understand the answear that you gave. Could you explain more please. Here is my problem again, this time I will try and explain it better.

In a spreadsheet I have several forms each cotaining several textboxes for user input. The values put into these textboxes are stored in an array. In all there are about 8 arrays, all contain mixed data types, of fixed size, and all are of a size of about 10 by 75. Quite a bit of data as you can imagin. The problem is that when the user quites Excel I need the values contained within these arrays to be saved. Like I explained before, the only method that I can think of doing this by is with a for loop that writes the value of each array location to an individual cell. When the workbook is opened the values within these cells are read back into the arrays. This process is extreamly slow. Do you know any better method that I can use, ie one that does not have to wirte/read each individual array value to an individual cell.


cheers for your help

kind regards

Anna Daly
annadaly@hotmail.com



Posted by Ivan Moala on November 13, 2000 4:04 AM

Anna
If you store 1 of your 8 arrays @ Range("a1") then
use a Variant Array eg.

Reads cells into variant array

Dim DataArray as VAriant
DataArray = Range("a1").Resize(10,75)

============================================

So to read it back into the sheet use;
MySheet.Range("a1").Resize(10, 75).Value = DataArray

Do this for each Array BUT they must be a variant
AND 2 dimentional.

If this still gives you problem then send me workbook.


Ivan