Writing in cell is extrmely slow and freezes the application

[hammaway]

So I am assuming I am missing something basic as this is supposed to be a very simple operation.
I have a collection of items that I have cleaned and prepared to write on the first 3 columns of the second sheet.
When I execute this loop, CPU usage goes through the roof and it freezes Excel.
I tested using numbers instead of the function calls but that is not the problem, it's the actual write operation. If I debug and run it manually, it goes very slow and if I try to write more than 1 data every second it starts "lagging". I am assuming I am doing something very stupid. Please help.

Function WriteData(cleanCollection As Collection)
Application.ScreenUpdating = False
    lineCount = 1
    Sheets(2).Cells.ClearContents
    For Each e In cleanCollection
        Sheets(2).Cells(lineCount, 1) = e.GetDate()
        Sheets(2).Cells(lineCount, 2) = e.GetHour()
        Sheets(2).Cells(lineCount, 3) = e.GetCmd()
        lineCount = lineCount + 1
    Next
End Function

 

[mikerickson]

Roll it into an array and then put the array into the cells

Dim MyArray() as Variant
Dim i as Long

Redim myArray(1 to cleanCollection.Count, 1 to 3)
i = 0
For each e in cleanCollection
    i = i + 1
    myArray(i, 1) = e.GetDate
    myArray(i, 2) = e.GetHour
    myArray(i, 3) = e.GetCmd
Next e

With Sheets(2)
    .ClearContents
    .Range("A1").Resize(i, 3).Value = myArray
End With

 

[johnnyL]

What does 'e' represent?

 

[mikerickson]

E is the looping variable used in my code and the OP code.

 

[hammaway]

Roll it into an array and then put the array into the cells

Dim MyArray() as Variant
Dim i as Long

Redim myArray(1 to cleanCollection.Count, 1 to 3)
i = 0
For each e in cleanCollection
    i = i + 1
    myArray(i, 1) = e.GetDate
    myArray(i, 2) = e.GetHour
    myArray(i, 3) = e.GetCmd
Next e

With Sheets(2)
    .ClearContents
    .Range("A1").Resize(i, 3).Value = myArray
End With

Hi mikerickson, thanks for your reply.
The problem doesn't seem to be what I write in cells; as a test I did something like:

    For Each e In cleanCollection
        Sheets(2).Cells(lineCount, 1) = 1
        Sheets(2).Cells(lineCount, 2) = 1
        Sheets(2).Cells(lineCount, 3) = 1
        lineCount = lineCount + 1
    Next

And I am getting the same thing. The problem appears to be writing in another sheet that is not active, as if I do:

    For Each e In cleanCollection
        Cells(lineCount, 1) = e.GetDate()
        Cells(lineCount, 2) = e.GetHour()
        Cells(lineCount, 3) = e.GetCmd()
        lineCount = lineCount + 1
    Next

everything works fine.
Any idea why? Am I forced to write in the AcriveSheet to avoid slow/freeze?

 

[mikerickson]

You are writing to the worksheet every loop.
My code has only one mass write to worksheet instruction.
The number of times that you write to a worksheet is more of a time factor than the number cells that you wite to.

 

[hammaway]

You are writing to the worksheet every loop.
My code has only one mass write to worksheet instruction.
The number of times that you write to a worksheet is more of a time factor than the number cells that you wite to.

Ok, I am not aware of how this works:

With Sheets(2)
    .ClearContents
    .Range("A1").Resize(i, 3).Value = myArray
End With

I guess I will have to look it up.
Thank you!

 

[ten21]

Hi forum,

I am new in this forum and I am not pro user but I have a problem with Excel!
When I want to edit current data (text) in a cell, Excel responds slowly (like it is freezing), it is good after installing Office but after 5 to 10 times opening the specific file and editing the cell content it gets slow and slower! The Cell contents are just simple text entry (raw data with no formula). I have disabled hardware acceleration but it didn't work, I have disabled auto calculation but nothing, I start Excel with safe mode and it works! I checked add-ins but I have no third party add-ins or apps installed!
Every time I delete Excel "Settings" registry key it works great like the first one use! But after about 5 to 10 times open this specified file it gets slow and can't edit any cells (it is slow)!
I would appreciate any help because I got stuck with this problem since 2019! (You can check my SS below)

 

[ten21]

You mean there is not a solution?! Why at first time it is normal (even after about 10 times opening and saving it is normal!)?