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