Export to CSV to speed up Loop

[ExcelMonkey]

I have a loop that fills a 3-D array with random numbers. My loop is
large and I am worried about excel choking or lagging due to the large
amount of data that accumulates in the array. Is it possible to
temporarily export portions of the array to a text or CSV file within
the loop for the purposes of increasing looping speed/memory? I would
do this within the third dimension loop. It would be like exporting a
2D sheet from excel. It would have to append new info to the existing
info already in the text/CSV file.

Then when the final loop is finished, I would import all the data again
from the same text/CSV file.

Dim DataArray() As Variant
Dim A as Integer
Dim B as Long
Dim C as Integer

For A = 1 to 65
For B = 1 to 65000
For C = 1 to 200
DataArray(B,C,A) = Rnd()
Next C
Next B
'Export data from array to text file (2d portion of 3darray)
Export logic ??????
Next A

'Import Data from text/CSV file back to DataArray

Can this be done?

 

[K Dales]

-----Original Message-----
I have a loop that fills a 3-D array with random numbers. My loop is
large and I am worried about excel choking or lagging due to the large
amount of data that accumulates in the array. Is it possible to
temporarily export portions of the array to a text or CSV file within
the loop for the purposes of increasing looping speed/memory? I would
do this within the third dimension loop. It would be like exporting a
2D sheet from excel. It would have to append new info to the existing
info already in the text/CSV file.

Then when the final loop is finished, I would import all the data again
from the same text/CSV file.

Dim DataArray() As Variant
Dim A as Integer
Dim B as Long
Dim C as Integer

For A = 1 to 65
For B = 1 to 65000
For C = 1 to 200
DataArray(B,C,A) = Rnd()
Next C
Next B
'Export data from array to text file (2d portion of 3darray)
Export logic ??????
Next A

'Import Data from text/CSV file back to DataArray

Can this be done?

I don't think you would find your idea to be any more
efficient than just saving your array in Excel. You are
talking lots of disk access! My suggestion: Set up an ADO
recordset (disconnected; i.e. no DB server) that could be
used to store your array values. This should be fairly
fast and efficient assuming you have sufficient memory
(RAM and virtual) on your machine.

 

[Tom Ogilvy]

If that is your actual code then

Dim DataArray(1 to 65, 1 to 65000, 1 to 200) As Single
Dim A as Integer
Dim B as Long
Dim C as Integer

For A = 1 to 65
For B = 1 to 65000
For C = 1 to 200
DataArray(B,C,A) = Rnd()
Next C
Next B
'Export data from array to text file (2d portion of 3darray)
Export logic ??????
Next A

If that works, then there is no reason to export anything - you don't save
anything. If it doesn't work, then you can't do it anyway. (you would have
to reduce the size of your array) Again, you don't save anything by
exporting your data - a zero takes up as much memory as any other number.

If you needed to export, you would do it like this

Dim DataArray(1 to 65000, 1 to 200) As Single
Dim A as Integer
Dim B as Long
Dim C as Integer


For A = 1 to 65
For B = 1 to 65000
For C = 1 to 200
DataArray(B,C) = Rnd()
Next C
Next B
'Export data from array to text file (save array and reuse)
Export logic ??????
Next A