83
Name
Returns
Description
PivotLayout
PivotLayout
Read-only. Returns an object to manipulate the location of
fields for a PivotChartreport
PlotArea
PlotArea
Read-only. Returns an object to manipulate formatting,
gridlines, data markers, and other visual items for the area
where the chart is actually plotted. Inside the chart area
PlotBy
XlRowCol
Set/Get whether columns in the original data are used as
individual data series (xlColumns), or if the rows in the
original data are used as data series (xlRows)
PlotVisible
Boolean
Set/Get whether only visible cells are plotted or if
Only
invisible cells are plotted too (False)
Previous
Read-only. Returns the previous sheet in the workbook
(from right to left) as an object
Protect
Boolean
Read-only. Returns whether the chart and everything in it
Contents
is protected from changes
ProtectData
Boolean
Set/Get whether the source data can be redirected for a
chart
Protect
Boolean
Read-only. Returns whether the shapes in the chart can be
Drawing
modified (ProtectDrawingObjects=False)
Objects
Protect
Boolean
Set/Get whether the user can modify the points on a
GoalSeek
chart with a mouse action
Protect
Boolean
Set/Get whether formatting can be changed for a chart
Formatting
Protection
Boolean
Read-only. Returns whether protection has been applied
Mode
to the user interface. Even if a chart has user interface pro-
tection on, any VBAcode associated with the chart can
still be accessed
Protect
Boolean
Set/Get whether parts of a chart can be selected and if
Selection
shapes can be put into a chart
RightAngle
Variant
Set/Get whether axes are fixed at right angles for 3D
Axes
charts, even if the perspective of the chart changes
Rotation
Variant
Set/Get what angle of rotation around the z-axis, in
degrees, the viewer sees on a 3D chart. Valid degrees vary
depending on the type of 3D chart
Shapes
Shapes
Read-only. Returns all the shapes contained by the chart
ShowDataLabels
Boolean
Set to True,this property ensures that data labels are
OverMaximum
shown even if the data point exceeds the size of the axis;
this property applies to 2D charts only
Table continued on following page
689
Chart Properties
74
Name
Returns
Description
SideWall
Walls
Read-only. Returns a Wallsobject allowing users to for-
mat the side wall of a 3D chart
Tab
Tab
Read-only. Returns a Tabobject for a chart or a worksheet
Visible
XlSheet
Set/Get whether or not the chart is visible. The Visible
Visibility
property can also be set to xlVeryHiddento make the
chart inaccessible to the end user
Walls
Walls
Read-only. Returns an object to manipulate the formatting
of the walls on a 3D chart
Chart Methods
Name
Returns
Parameters
Description
Activate
Activates the chart, making it
the ActiveChart
ApplyChart
FileNameAsString
Activates and applies a
Template
template file for the chart
ApplyData
[TypeAsXl
Sets the point labels for a chart.
Labels
DataLabels
The Typeparameter specifies
Type], [Legend
whether no label, a value, a
Key], [Auto
percentage of the whole, or a
Text], [Has
category label is shown. The
LeaderLines],
legend key can appear by the
[ShowSeries
point by setting the LegendKey
Name], [Show
parameter to True
CategoryName],
[ShowValue],
[Show
Percentage],
[ShowBubble
Size],
[Separator]
ApplyLayout
LayoutAsLong
Allows a user to apply any one
of the predefined layouts shown
in the Ribbon
Axes
Object
Type, AxisGroup
Returns the Axisobject or the
AsXlAxisGroup
Axescollection for the associ-
ated chart. The type of axis and
the axis group can be specified
with the parameters
690
Chart Methods
92
Name
Returns
Parameters
Description
ChartGroups
Object
[Index]
Returns either a single chart group
(ChartGroup) or a collection of chart
groups (ChartGroups) for a chart
ChartObjects
Object
[Index]
Returns either a single embedded chart
(ChartObject) or a collection of embed-
ded charts (ChartObjecs) in a chart
ChartWizard
[Source],
Asingle method to modify the key proper-
[Gallery],
ties associated with a chart. Specify the
[Format],
properties that you want to change. The
[PlotBy],
Sourcespecifies the data source. Gallery
[Category
specifies the chart type. Formatcan
Labels],
specify one of the 10 built-in chart auto-
[Series
formats. The rest of the parameters set up
Labels],
how the source will be read, the source of
[HasLegend],
category labels, the source of the series
[Title],
labels, whether a legend appears, and the
[Category
titles of the chart and the axis. If Sourceis
Title],
not specified, this method can only be
[ValueTitle],
used if the sheet containing the chart is
[ExtraTitle]
active
CheckSpelling
[Custom
Checks the spelling of the text in the
Dictionary],
chart. Acustom dictionary can be speci-
[Ignore
fied (CustomDictionary), all uppercase
Uppercase],
words can be ignored (IgnoreUppercase),
[Always
and Excel can be set to display a list of
Suggest],
suggestions (AlwaysSuggest)
[SpellLang]
ClearToMatch
Resets the formatting for all chart elements
Style
to automatic
Copy
[Before],
Adds a new copy of the chart to the
[After]
position specified at the Beforeor After
parameters
CopyPicture
[Appearance
Copies the chart into the clipboard as a
AsXlPicture
picture. The Appearanceparameter can be
Appearance],
used to specify whether the picture is
[FormatAsXl-
copied as it looks on the screen or when
CopyPicture
printed. The Formatparameter can specify
Format], [Size
the type of picture that will be put into the
AsXlPicture
clipboard. The Sizeparameter is used
Appearance]
when dealing with chart sheets to describe
the size of the picture
Delete
Deletes the chart
Table continued on following page
691
Chart Methods
80
Name
Returns Parameters
Description
Deselect
Unselects the selected object within a
chart. This is equivalent to pressing the Esc
key while working in a chart
Evaluate
Variant
Name
Evaluates the Namestring expression as if
it were entered into a worksheet cell
Export
Boolean
FilenameAs
Saves the chart as a picture (jpg or gif
String,
format) with the name specified by
[FilterName],
Filename
[Interactive]
ExportAsFixed
Boolean
TypeAsxlFixed
Exports a file to a format specified by using
Format
FormatType,
the xlFixedFormatTypeconstants
FileNameAs
Variant,
QualityAs
Variant,
IncludeDoc
Properties
AsVariant,
IgnorePrint
AreasAsVariant,
FromAsVariant,
ToAsVariant,
OpenAfter
Publish
GetChartElement
xAsLong, yAs
Returns what is located at the coordinates
Long, ElementID
x and y of the chart. Only the first two
AsLong, Arg1As
parameters are sent. Variables must be put
Long, Arg2As
in the last three parameters. After the
Long
method is run, the last three parameters
can be checked for return values. The
ElementIDparameter will return one of
the XlChartItemparameters. The Arg1
and Arg2parameters may or may not hold
data, depending on the type of element
Location
Chart
WhereAs
Moves the chart to the location specified
XlChart
by the Whereand Nameparameters. The
Location,
Wherecan specify if the chart is moving to
[Name]
become a chart sheet or an embedded object
Move
[Before],
Moves the chart to the position specified
[After]
by the parameters
OLEObjects
Object
[Index]
Returns either a single OLEobject
(OLEObject) or a collection of OLEobjects
(OLEObjects) for a chart
692
Chart Methods
76
Name
Returns Parameters
Description
Paste
[Type]
Pastes the data or pictures from the
clipboard into the chart. The Typeparame-
ter can be used to specify if only formats,
formulas, or everything is pasted
PrintOut
[From],[To],
Prints the chart. The printer, number of
[Copies],
copies, collation, and whether a print
[Preview],
preview is desired can be specified with
[ActivePrinter],
the parameters. Also, the sheets can be
[PrintToFile],
printed to a file by using the PrintToFile
[Collate],
and PrToFileNameparameters. The From
[PrToFile
and Toparameters can be used to specify
Name]
the range of printed pages
PrintPreview
[EnableChanges]
Displays the current chart in the collection
in a print preview mode. Set the
EnableChangesparameter to Falseto
disable the Margins and Setup buttons,
hence not allowing the viewer to modify the
page setup
Protect
[Password],
Protects the chart from changes. A
[DrawingObjects],
case-sensitive Passwordcan be specified.
[Contents],
Also, determines whether shapes are
[Scenarios],
protected (DrawingObjects), the entire
[User
contents are protected (Contents), or only
Interface
the user interface is protected
Only]
(UserInterfaceOnly)
Refresh
Refreshes the chart with the data source
SaveAs
FilenameAs
Saves the current chart into a new
String,
workbook with the filename specified by
[FileFormat],
the Filenameparameter. Afile format,
[Password],
password, write-only password, creation
[WriteRes
of backup files, and other properties of the
Password],
saved file can be specified with the
[ReadOnly
parameters
Recommended],
[CreateBackup],
[AddToMru],
[TextCodepage],
[TextVisual
Layout],
[Local]
Table continued on following page
693
Chart Methods
76
Name
Returns Parameters
Description
SaveChart
FilenameAs
Saves the specified chart as a custom chart
Template
String
template. The chart template is saved to
the template directory unless a location is
explicitly provided, telling Excel to use
that location instead
Select
[Replace]
Selects the chart
Series
Object
[Index]
Returns either a single series (Series) or a
Collection
collection of series (SeriesCollection)
for a chart
SetBackground
FileNameAs
Sets the chart’s background to the picture
Picture
specified by the FileNameparameter
String
SetDefault
FileNameAs
Specifies the name of the chart template
Chart
that is used when new charts are created
String
SetElement
ElementAs
Sets/Gets the elements on a chart. Use the
MsoChart-
MsoChartElementTypeconstants to
ElementType
identify the elements you want to set/get
SetSourceData
SourceAs
Sets the source of the chart’s data to the
Range, [PlotBy]
range specified by the Sourceparameter.
The PlotByparameter uses the XlRowCol
constants to choose whether rows or
columns of data will be plotted
Unprotect
[Password]
Deletes the protection set up for a chart. If
the chart was protected with a password,
the password must be specified now
Chart Events
Name
Parameters
Description
Activate
Triggered when a chart is made to have focus
BeforeDouble
ElementIDAs
Triggered just before a user double-clicks a chart. The
Click
element that was double-clicked in the chart is passed
XlChartItem,
into the event procedure as ElementID. The Arg1and
Arg1AsLong,
Arg2parameters may or may not hold values depending
Arg2AsLong,
on the ElementID. The double-click action can be
CancelAs
canceled by setting the Cancelparameter to True
Boolean
694
Chart Events
81
Name
Parameters
Description
BeforeRight
CancelAs
Triggered just before a user right-clicks a chart. The
Click
Boolean
right-click action can be canceled by setting the Cancel
parameter to True
Calculate
Triggered after new or changed data is plotted on the chart
Deactivate
Triggered when the chart loses focus
DragOver
Triggered when a cell range is dragged on top of a chart.
Typically used to change the mouse pointer or give a
status message
DragPlot
Triggered when a cell range is dropped onto a chart.
Typically used to modify chart attributes
MouseDown
ButtonAs
Triggered when the mouse button is pressed down on a
XlMouse
chart. Which mouse button is pressed is passed in with
Button, Shift
the Buttonparameter. The Shiftparameter holds
AsLong, xAs
information regarding the state of the Shift, Ctrl, and Alt
Long, yAs
keys. The xand yparameters hold the x and y
Long
coordinates of the mouse pointer
MouseMove
ButtonAs
Triggered when the mouse is moved on a chart. Which
XlMouse
mouse button is pressed is passed in with the Button
Button, Shift
parameter. The Shiftparameter holds information
AsLong, xAs
regarding the state of the Shift, Ctrl, and Alt keys. The x
Long, yAsLong
and yparameters hold the x and y coordinates of the
mouse pointer
MouseUp
ButtonAs
Triggered when the mouse button is released on a chart.
XlMouse
Which mouse button is pressed is passed in with the
Button, Shift
Buttonparameter. The Shiftparameter holds
AsLong, xAs
information regarding the state of the Shift, Ctrl, and Alt
Long, yAsLong
keys. The xand yparameters hold the x and y coordinates
of the mouse pointer
Resize
Triggered when the chart is resized
Select
ElementIDAs
Triggered when one of the elements in a chart is selected.
XlChartItem,
The element that was selected in the chart is passed into
Arg1AsLong,
the event procedure as ElementID. The Arg1and Arg2
Arg2AsLong
parameters may or may not hold values depending on the
ElementID
SeriesChange
SeriesIndex
Triggered when the value of a point on a chart is changed.
AsLong,
SeriesIndexreturns the location of the series in the
PointIndexAs
chart series collection. PointIndexreturns the point
Long
location in the series
695
Chart Events
45
Chart Object and the Charts Collection Example
This example creates a 3D chart from a given range, formats it, and saves a picture of it as a .jpgimage:
Sub CreateAndExportChart()
Dim oCht As Chart
‘Create a new (blank) chart
Set oCht = Charts.Add
‘Format the chart
With oCht
.ChartType = xl3DColumnStacked
‘Set the data source and plot by columns
.SetSourceData Source:=Range(“Sheet1!$B$2:$D$8”), PlotBy:=xlColumns
‘Create a new sheet for the chart
.Location Where:=xlLocationAsNewSheet
‘Size and shape matches the window it’s in
.SizeWithWindow = True
‘Turn of stretching of chart
.AutoScaling = False
‘Set up a title
.HasTitle = True
.ChartTitle.Caption = “Main Chart”
‘No titles for the axes
.Axes(xlCategory).HasTitle = False
.Axes(xlValue).HasTitle = False
‘Set the 3D view of the chart
.RightAngleAxes = False
.Elevation = 50 ‘degrees
.Perspective = 30 ‘degrees
.Rotation = 20 ‘degrees
.HeightPercent = 100
‘No data labels should appear
.ApplyDataLabels Type:=xlDataLabelsShowNone
‘Save a picture of the chart as a jpg image
.Export “c:\” & .Name & “.jpg”, “jpg”, False
End With
End Sub
ChartArea Object
The ChartAreaobject contains the formatting options associated with a chart area. For 2D charts,
ChartAreaincludes the axes, axes titles, and chart titles. For 3D charts, ChartAreaincludes the chart
title and its legend. The part of the chart where data is plotted (plot area) is not part of the ChartArea
object. Please see the PlotAreaobject for formatting related to the plot area. The parent of the
ChartAreais always the Chartobject.
ChartArea Common Properties
The Application, Creator, and Parentproperties are defined at the beginning of this appendix.
696
Chart Object and the Charts Collection Example
65
ChartArea Properties
Name
Returns
Description
AutoScaleFont
Variant
Set/Get whether the font size changes in the
ChartAreawhenever the Chartchanges sizes
Format
ChartFormat
Read-only. Returns the ChartFormatobject, which con-
trols the line, fill, and effect formatting for the chart area
Height
Double
Set/Get the height of the chart area in points
Left
Double
Set/Get the left edge of the chart area in relation to the
chart in points
Name
String
Read-only. Returns the name of the chart area
Shadow
Boolean
Set/Get whether a shadow effect appears around the
chart area
Top
Double
Set/Get the top edge of the chart area in relation to the
chart in points
Width
Double
Set/Get the width of the chart area in points
ChartArea Methods
Name
Returns Parameters
Description
Clear
Variant
Clears the chart area
ClearContents
Variant
Clears the data from the chart area without
affecting formatting
ClearFormats
Variant
Clears the formatting from the chart area
without affecting the data
Copy
Variant
Copies the chart area into the clipboard
Select
Variant
Activates and selects the chart area
ChartArea Object Example
This example sizes the chart area for a chart and gives the chart a shadow:
Sub SetChartColorFormat()
With Worksheets(“Sheet4”).ChartObjects(“Chart 2”).Chart
.ChartArea.Height = 300
.ChartArea.Width = 500
.ChartArea.Shadow = True
End With
End Sub
697
ChartArea Properties
Documents you may be interested
Documents you may be interested
