Excel 2010 Bible

Chapter 44: VBA Examples

899

Applying chart formatting

This example applies several different formatting types to the specified chart (in this case, Chart 1
on the active sheet):

Sub ChartMods()

With ActiveSheet.ChartObjects(“Chart 1”).Chart

.ChartType = xlArea

.ChartArea.Font.Name = “Arial”

.ChartArea.Font.FontStyle = “Regular”

.ChartArea.Font.Size = 9

.PlotArea.Interior.ColorIndex = 6

.Axes(xlValue).TickLabels.Font.Bold = True

.Axes(xlCategory).TickLabels.Font.Bold = True

End With

End Sub

One way to learn about these properties is to record a macro while you apply various changes
to a chart.

VBA Speed Tips

VBA is fast, but it’s often not fast enough. This section presents programming examples that you
can use to help speed your macros.

Turning off screen updating

You’ve probably noticed that when you execute a macro, you can watch everything that occurs in
the macro. Sometimes, this view is instructive; but, after you get the macro working properly, it
can be annoying and slow things considerably.

Fortunately, you can disable the normal screen updating that occurs when you execute a macro.
Insert the following statement to turn off screen updating:

Application.ScreenUpdating = False

If, at any point during the macro, you want the user to see the results of the macro, use the follow-
ing statement to turn screen updating back on:

Application.ScreenUpdating = True