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