macros and screen updates

  • Thread starter Thread starter Tim Laud
  • Start date Start date
T

Tim Laud

I've written a macro that updates several sheets and
graphs. Execution is very slow because of the constant
refreshing of the display.

Is there a way to turn off the screen updates until the
macros have finished?
 
Tim said:
I've written a macro that updates several sheets and
graphs. Execution is very slow because of the constant
refreshing of the display.

Is there a way to turn off the screen updates until the
macros have finished?
Click to expand...

Application.ScreenUpdating = False

Calculations can also slow down your macros. You might want to add
Application.Calculation = xlCalculationManual to the start of your macro
and Application.Calculation = xlCalculationAutomatic to the end.
 
-----Original Message-----
I've written a macro that updates several sheets and
graphs. Execution is very slow because of the constant
refreshing of the display.

Is there a way to turn off the screen updates until the
macros have finished?
.
Click to expand...
At the beginning of your macro add in
Application.ScreenUpdating = False
Make sure that at the end of your macro that you set
Application.ScreenUpdating = True

HTH
David
 
Tim

Early on in code use these lines

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


At end of code these

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


Gord Dibben Excel MVP - XL97 SR2 & XL2002
 
Hello Tim,

There certainly is a way to control screen updating within a VBA macro. The
following is from the VBA help file, with a query on "ScreenUpdating."

ScreenUpdating Property
See Also Applies To Example Specifics
True if screen updating is turned on. Read/write Boolean.

Remarks
Turn screen updating off to speed up your macro code. You won't be able to
see what the macro is doing, but it will run faster.

Remember to set the ScreenUpdating property back to True when your macro
ends.

Example
This example demonstrates how turning off screen updating can make your
code run faster. The example hides every other column on Sheet1, while
keeping track of the time it takes to do so. The first time the example
hides the columns, screen updating is turned on; the second time, screen
updating is turned off. When you run this example, you can compare the
respective running times, which are displayed in the message box.

Dim elapsedTime(2)
Application.ScreenUpdating = True
For i = 1 To 2
If i = 2 Then Application.ScreenUpdating = False
startTime = Time
Worksheets("Sheet1").Activate
For Each c In ActiveSheet.Columns
If c.Column Mod 2 = 0 Then
c.Hidden = True
End If
Next c
stopTime = Time
elapsedTime(i) = (stopTime - startTime) * 24 * 60 * 60
Next i
Application.ScreenUpdating = True
MsgBox "Elapsed time, screen updating on: " & elapsedTime(1) & _
" sec." & Chr(13) & _
"Elapsed time, screen updating off: " & elapsedTime(2) & _
" sec."

--------------------
From: "Tim Laud" <[email protected]>
Subject: macros and screen updates
Date: Fri, 1 Aug 2003 08:42:12 -0700
I've written a macro that updates several sheets and
graphs. Execution is very slow because of the constant
refreshing of the display.

Is there a way to turn off the screen updates until the
macros have finished?
Click to expand...

Regards,
Chris Jensen[MSFT]

This posting is provided “AS IS” with no warranties, and confers no rights.

“Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026?  If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
http://www.microsoft.com/security/security_bulletins/ms03-026.asp and/or to
visit Windows Update at http://windowsupdate.microsoft.com to install the
patch. Running the SCAN program from the Windows Update site will help to
insure you are current with all security patches, not just MS03-026.”
 
Back
Top