Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export to CSV to speed up Loop
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? --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export to CSV to speed up Loop
-----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? --- Message posted from http://www.ExcelForum.com/ . 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export to CSV to speed up Loop
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 -- Regards, Tom Ogilvy "ExcelMonkey " wrote in 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? --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
export re-order input fields to export file [csv] | Excel Worksheet Functions | |||
Can you speed UP drag speed? | Excel Discussion (Misc queries) | |||
Worksheet_Change - loop within a loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |