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

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