Page 144 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Mode
Microsoft Office Excel 2003 Programming Inside Out

ScreenUpdating Property
VBA macros execute very quickly, especially when compared with the speed at which a human
could perform the same steps. As Excel performs the different actions programmed in the
macro, the screen changes to reflect those actions. Enough actions could happen fast enough
that the screen updates aren’t processed as quickly as the actions in the macro and it appears
as if the screen is flashing as it attempts to reflect the different steps performed. These screen
updates also require some processing time, which will slow down the execution of the macro.
To minimize the screen flashing and also maybe to gain a few seconds in processing time,
especially on macros that require a lot of processing time, you can disable screen updates
using the ScreenUpdating property.
Setting the ScreenUpdating property to False at the start of a macro instructs Excel not to
update the screen during execution until the property has been reset to True. It is very impor­
tant to fully test a macro for errors and add some type of error trapping when turning off
screen updates so that the user does not think the macro or Excel has crashed when there is
actually an error condition that’s not being displayed to the user. It’s also important to set
ScreenUpdating to True, the default value, when the procedure has executed so that any other
procedure that might have called the one that turns the screen updating off doesn’t have to be
concerned with whether or not the screen updates were turned on or off outside its own scope.
The following procedure creates a new chart based on the data in the SalesByCategory.xls
workbook and then waits three seconds before terminating the procedure.

Sub BuildChart()
Application.StatusBar = "Adding new chart..."
Application.Wait (Now + TimeValue("0:00:03"))
Application.ScreenUpdating = False
Charts.Add 'Add a new chart object
ActiveChart.ChartType = xl3DColumn 'Set the Chart type to #D Column
ActiveChart.SetSourceData Source:=Sheets("ByCategory").Range("C1:G13"), _
PlotBy:=xlColumns 'Set the data source
ActiveChart.Location Where:=xlLocationAsObject, Name:="ByCategory"
Application.StatusBar = "Configuring new chart..."
With ActiveChart 'Format the chart
.HasTitle = True
.ChartTitle.Characters.Text = "Monthly Sales by Category"
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Characters.Text = "Month"
.Axes(xlSeries).HasTitle = True
.Axes(xlSeries).AxisTitle.Characters.Text = "Category"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Characters.Text = "Sales"
End With
ActiveSheet.Shapes(1).IncrementLeft -133.5 'Position the chart
ActiveSheet.Shapes(1).IncrementTop 214.5
ActiveSheet.Shapes(1).ScaleWidth 1.77, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes(1).ScaleHeight 1.35, msoFalse, msoScaleFromTopLeft
Application.Wait (Now + TimeValue("0:00:03"))
Application.StatusBar = False
Chapter 6
Application.ScreenUpdating = True
End Sub
118