[RESOLVED] [Excel VBA] ScreenUpdating = False to speed up code

[ActSciMan]

So, one of the first tricks I learned to speed up code was Application.ScreenUpdating = False. I used it on one of the first programs I was working on and it made the program 9 times as fast, with one line of code. That program was written very badly when I got it, and by the time I was finished with it, it ran 125 times as fast as the original. I then found a program that was written well and tried the same thing, but it wasn't noticeably faster. In my test runs, it was actually slower sometimes. This is a program that took a long time to run, half hour or so.

So, have you noticed that this doesn't help all that much with well written code?

[nO_OnE]

It depends on how you are writing your code and what you are doing with it. For example, a simple code:

    Workbooks("test").Worksheets("1").Range("A1").Select
    Selection.Copy
    Workbooks("test").Worksheets("1").Range("A2").Select
    Selection.Paste

In itself this doesn't run too slow, but if you actually select cells, change sheets, etc... this will be extremely slow. You can speed up the above of code by:

    Workbooks("test").Worksheets("1").Range("A2") = Workbooks("test").Worksheets("1").Range("A1")

This will copy the value from the cell "A1" on worksheet 1, in the workbook named "Test". This will happen as long as the workbook "Test" is open, it doesn't even have to be active, the copy will happen behind the seen, therefore it won't matter if you turn off screen updating...

[ActSciMan]

I'm not sure you answered my question. I know that the first bit of code will be slow and I know the second bit will be faster. I know turning screen updating off will make the first one faster. My question is, what happens in the second case if you are looking at the screen where those changes are being made? Even though no sheet changes and all that is being done, the screen is still updating with lots of values being changed (think a loop of 100,000 iterations). So, it does take some amount of processor to do this. You sort of say this, but my question is, is the amount of processing power used in this negligible?

But, that gives me an idea. I can just write a loop that writes some values to cells many times and test it both ways. So, I have answered my own question.

Sub test2()
    Dim t As Single
    Application.ScreenUpdating = False
    t = Timer

    Dim cell As Range
    Dim j As Long
    
    For j = 1 To 1000
        For Each cell In Worksheets("Sheet1").Range("E15:G18")
            cell.Value = j
        Next cell
    Next j
    
    MsgBox Timer - t
End Sub

This took about 3.875 seconds when I had screen updating on and about 0.656 seconds when I had screen updating off. No selecting, copying, moving sheets. Of course, it would be faster to write the whole range at once, but I'm testing the screen updating theory.