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