Is there anyway to see why ScreenUpdating is taking so long?

**foxguy** · 10-11-2011, 02:40 AM

I turn off ScreenUpdating at the beginning of all Events, and turn it back on when it's done.

I have managed to determine that when ScreenUpdating is set True that it takes longer than I like.
I know that turning it on automatically triggers a calculation, which I assume is the reason it takes longer than necessary.
Is there any way to determine what is being calculated? And more specifically how long each formula is taking?

I'm guessing that turning ScreenUpdating to True and back to False while the macro is running would not help when the macro ends. Is that right?

BYW: I've seen several people say that ScreenUpdating automatically gets set True when the macro ends, but the help file says that it doesn't.

**Bob Phillips** · 10-11-2011, 03:09 AM

ScreenUpdating = True does not trigger a calculation, it forces a re-display of the screen. Calculation forces a calculation.

The only reason I could see for it taking a long time is because there is a lot on the spreadsheet that needs re-displaying.

Maybe people do say it gets turned back on, maybe MS say it doesn't. Do you want to trust either, and trust that it will always be that way, when it is just one simple line of code? Even if it happens automatically, it won't save you anything, the re-display will still happen.

**foxguy** · 10-11-2011, 03:37 AM

Bob;
I believe you're wrong about it triggering a calculation. I know that I read it somewhere in the last few days, but I can't remember where. I'm going to look for it again.

I just tested it with this sub:

Sub TestScreenUpdating()
    Dim dStart As Double _
            , dMiddle As Double _
            , dEnd As Double
    
    dStart = Timer
    With Application
        .ScreenUpdating = False
        dMiddle = Timer
        .ScreenUpdating = True
    End With
    dEnd = Timer
    
    Debug.Print dStart, dMiddle, dEnd
    Debug.Print dEnd - dMiddle, dMiddle - dStart, dEnd - dStart
End Sub

Here are the results

1891.43005371094            1891.43298339844            1891.49694824219 
 0.06396484375               0.0029296875                0.06689453125

As you can see turning ScreenUpdating back on with absolutely no changes to the workbook took more than 20 times as long as turning it off.

**romperstomper** · 10-11-2011, 03:43 AM

So you're surprised that doing something takes longer than doing nothing?
ScreenUpdating does not cause a calculation. It does however redraw the screen whether anything has changed or not.

**foxguy** · 10-11-2011, 04:17 AM

RS;
I guess I have to concede the point. I put a Stop in Workbook_Calculate and it didn't trigger when I turned ScreenUpdting to True.

I know I read it somewhere, but that source must have been wrong.