Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing cells using Range() slows down with successive writes
Hello,
I am creating a data logging system for an industrial application. I need to write to 1000-10000 cells/second. My higher-level program opens excel files, adds sheets, writes data, etc. The problem is when I write array data using the Range() property, the writes slow down drastically as the number of writes increases. Writing data using the Cells() property does not cause this problem, but is far too slow to get the job done. Initially I thought this had something to do with the UNDO capability in excel, but I disabled this in the registry. Furthermore, the UNDO buffer is cleared when a save occurs. If I save, the problem still exists. I also thought this problem could be somehow related to the excel file growing, but I created a simple example that just rewrites new data to only the first line, and the problem still exists. I have also tried this with the excel window visible and hidden to see if it was a screen-draw issue, but the problem still exists. Here is some VBA code that shows the problem clearly (this problem exists using VB 6.0 and external activex commands aswell). Each time you run the subroutine, the write time increases when using the Range() property. What could be causing this problem??? Thanks for any help! Private Sub CommandButton1_Click() Dim i As Long Dim aryChars(256) As String lngTimer = Timer For i = 1 To 100 'create new/unique data for each cell For j = 0 To 255 aryChars(j) = "A" & CStr(j) & CStr(i) & CStr(Timer) Next 'if I use Range() alone, then problem Range("A1:IV1").Value2 = aryChars() 'if I use Cells() alone, then NO problem 'Cells(1, 1).Value2 = aryChars(0) Next MsgBox "dt = " & CStr(Timer - lngTimer) & " seconds" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing cells using Range() slows down with successive writes
Charles Williams has a lot of information on Excel performance on his site.
I'm not sure if he addresses your issue directly but it might be worth poking around there. http://www.decisionmodels.com/index.htm -- Jim wrote in message ... | Hello, | | I am creating a data logging system for an industrial application. I | need to write to 1000-10000 cells/second. My higher-level program | opens excel files, adds sheets, writes data, etc. The problem is when | I write array data using the Range() property, the writes slow down | drastically as the number of writes increases. Writing data using the | Cells() property does not cause this problem, but is far too slow to | get the job done. | | Initially I thought this had something to do with the UNDO capability | in excel, but I disabled this in the registry. Furthermore, the UNDO | buffer is cleared when a save occurs. If I save, the problem still | exists. I also thought this problem could be somehow related to the | excel file growing, but I created a simple example that just rewrites | new data to only the first line, and the problem still exists. I have | also tried this with the excel window visible and hidden to see if it | was a screen-draw issue, but the problem still exists. | | Here is some VBA code that shows the problem clearly (this problem | exists using VB 6.0 and external activex commands aswell). Each time | you run the subroutine, the write time increases when using the | Range() property. What could be causing this problem??? | | Thanks for any help! | | Private Sub CommandButton1_Click() | Dim i As Long | Dim aryChars(256) As String | lngTimer = Timer | | For i = 1 To 100 | 'create new/unique data for each cell | For j = 0 To 255 | aryChars(j) = "A" & CStr(j) & CStr(i) & CStr(Timer) | Next | | 'if I use Range() alone, then problem | Range("A1:IV1").Value2 = aryChars() | | 'if I use Cells() alone, then NO problem | 'Cells(1, 1).Value2 = aryChars(0) | Next | | MsgBox "dt = " & CStr(Timer - lngTimer) & " seconds" | End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Not able to drag formula to successive cells | Excel Discussion (Misc queries) | |||
Writing array data to range object always writes 0's on worksheet | Excel Programming | |||
Pasting in successive cells | Excel Discussion (Misc queries) | |||
Writing to a range of cells | Excel Programming | |||
Speeding up writes to cells? | Excel Programming |