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

ActiveChart.ChartArea.Select
With ActiveChart
.Elevation = 35
.Perspective = 30
.Rotation = 0
.RightAngleAxes = False
.HeightPercent = 100
End With
' changed "Chart 1" to the numeric location of the chart - not the chart
name
ActiveSheet.Shapes(1).ScaleHeight 1.25,
msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes(1).ScaleHeight 1.5,
msoFalse, msoScaleFromBottomRight

'ActiveSheet.Shapes("Chart 1").ScaleHeight 1.25,
msoFalse, msoScaleFromTopLeft
'ActiveSheet.Shapes("Chart 1").ScaleHeight 1.5,
msoFalse, msoScaleFromBottomRight

Application.CommandBars("Task Pane").Visible = False
End Sub

The Excel macro code in Figure 12.4.10 is described here.
● The code module name is my_graph_1. It is a public subroutine stored in the “Module1” area of
the workbook called “PERSONAL.XLSB” Excel 2007, 2010, 2013 or “PERSONAL.XLS” (Excel
2003). This subroutine has one parameter that is created in the “ThisWorkbook” area of
“PERSONAL.XLSB”.
● The code from Figure 12.4.8 above “Call Module1.my_graph_1(my_sheet)” executes this macro
from within the “Select Case” statement. The value of “my_sheet” is the name of the Excel sheet
to process.
● The selected range of cells is always A1:B11 on the first sheet of the workbook.
• This data comes from the output from the SAS program, and the range is “Hard-Coded” to be
exactly the data that was output by the PROC PRINT command. Because this is a proof-of-
concept program, no effort was put into making the subroutine flexible enough to accept a variable
size graph space. This Excel command adds a chart to the workbook.

The ActiveChart commands shown in Figure 12.4.10 define several features of the chart.

Table 12.3.3: Active Chart Actions from Figure 12.4.10.

ActiveChart Excel Object
names

Function

ChartType Defined the chart as a pie chart.

SetSourceData Define the sheet name, data range, how to plot the data.

Location Define where to place the chart.

HasTitle Toggle a title on.

ChartTitle.Characters.text The text of the title.

HasLegend Toggle a legend on.

Position Put legend on the bottom of the chart.

AutoScaleFont Toggle automatic font scaling on.

Font Select several items about the font.

ChartArea.Select Set several items that relate to the screen location of the charts.