Cleaning up the chart creation macro recorder output

Much of the code generated for the macro in the previous section is not necessary; it sets values for properties that really don't need to be set. Following is the listing for my edited macro. This performs exactly like the macro in the previous section, but it's significantly shorter and more efficient. Setting the ScreenUpdating property to False eliminates the screen refreshing.

Sub CleanedMacro()

Application.ScreenUpdating = False Charts.Add

ActiveChart.Location _

Where:=xlLocationAsObject, Name:="Sheet1" With ActiveChart

.SetSourceData Range("A1:F2") .HasTitle = True .ChartType = xlColumnClustered .HasLegend = False

.ApplyDataLabels Type:=xlDataLabelsShowValue .Axes(xlCategory).TickLabels.Orientation = xlHorizontal .ChartTitle.Font.Bold = True .ChartTitle.Font.Size = 12 .PlotArea.Top = 18 .PlotArea.Height = 162 .Axes(xlValue).MaximumScale = 0.6 .Deselect End With

Application.ScreenUpdating = True End Sub


When you create a chart with the Add method of the Charts collection,the created chart is always a chart sheet. In the preceding code, the Location method moves the chart to a worksheet.




A workbook that contains both the recorded macro and the cleaned-up macro is included on the companion CD-ROM, so you can compare the performance.

0 0

Post a comment