Building a System of Excel Macros Executable by SAS - Exchanging Data Between SAS and Microsoft Excel - page 207

12.4.4 Build an Excel Graph Using an Excel Macro
The object of this task is to create a simple graph. In Section 12.2.3 of this chapter I described how to
record an Excel macro. The same process was used here to record an Excel macro to create a graph. The
steps are listed here to aid you in creating an Excel macro to build a graph.
● Open Excel.
● Turn on Macro recording.
● Select all of the data in columns A and B.
● Select the pie chart graph from the Excel toolbar.
● Add a title.
● Add a legend.
● Apply the minor name change edits described in Figure 12.4.10.
● Save the macro in your Personal Macro Workbook (PERSONAL.XLSB) Module1 as
“my_graph_1”.
● This graph is neither pretty nor optimized; it always uses cells A1:B11 and must be the first graph
in the workbook. As noted, only the Chart1 name was changed.
• The four lines that begin with Activesheet.shapes may not copy correctly with a “Copy and Paste”
and may need to be adjusted with either a continuation character (“_”) at the end of the line or by
moving the data back one line for each of the four lines affected.
Figure 12.4.10: My_graph_1 Excel Macro Code.
Sub my_graph_1(my_sheet As String)
'
' my_graph_1 Macro
'
' This VBA macro generates a pie chart with minimal number of extras
' The code is reproduced here exactly as it was produced by the
' Excel Macro Record feature - except the name values were adjusted
Range("A1:B11").Select
Charts.Add
ActiveChart.ChartType = xl3DPie
ActiveChart.SetSourceData Source:=Sheets(my_sheet).Range("A1:B11"), _
PlotBy :=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:=my_sheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "My_new_Pie_chart"
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.Legend.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.Position = xlLeft