Chart Properties

Name

Returns

Description

AutoScaling

Boolean

Set/Get whether Excel will stretch a 3D chart to match its 2D chart equivalent. RightAngleAxes must be true

BackWall

Walls

Read-only. Returns a Walls object allowing users to format the back wall of a 3D chart

BarShape

XlBarShape

Set/Get the basic shape used in 3D bar or column charts (for example, box, cylinder, pyramid, and so on)

ChartArea

ChartArea

Read-only. Returns the part of a chart containing axes, titles, legends, and formatting properties

ChartStyle

Variant

Set/Get a number from 1-48, indicating the chart style for the chart

ChartTitle

ChartTitle

Read-only. Returns an object manipulating the chart title's properties. Use with the HasTitle property

ChartType

XlChart Type

Set/Get what the type of chart is. This property determines which other chart properties are valid. For example, if the ChartType is set to xl3DBarClustered, then the Bar3DGroup property can be used to access the chart group properties

CodeName

String

Read-only. Returns the programmatic name of the chart set at design-time in the VBA editor

DataTable

DataTable

Read-only. Returns an object to manipulate a chart's data table

DepthPercent

Long

Set/Get the percentage of a 3D chart's depth (y-axis) in relation to its width (x-axis)

Name

Returns

Description

Display BlanksAs

XlDisplay BlanksAs

Set/Get how blank cells are treated when plotting data in a chart (for example, xlNotPlotted, xlZero, or xlInterpolated)

Elevation

Long

Set/Get the angle of elevation, in degrees, at which the viewer sees a 3D chart. Valid degrees vary depending on the type of 3D chart

Floor

Floor

Read-only. Returns an object with the formatting properties of the floor (base) of a 3D chart

GapDepth

Long

Set/Get the percentage depth of a data series in relation to the marker width

HasAxis

Variant

Parameters: [Indexl], [Index2]. Set/Get whether axes exist for the chart. The parameters can be used to specify the axis type (using the xlAxisType constants with the first parameter) and the axis group (using the xlAxisGroup constants with the second parameter)

HasDataTable

Boolean

Set/Get whether a data table is associated (and therefore displayed). Use with the DataTable property

HasLegend

Boolean

Set/Get whether the chart has a legend. Use with the Legend property

HasTitle

Boolean

Set/Get whether the chart has a title. Use with the ChartTitle property

Height Percent

Long

Set/Get the percentage of a 3D chart's height (z-axis) in relation to its width (x-axis)

Hyperlinks

Hyperlinks

Read-only. Returns the collection of hyperlinks associated with the chart

Index

Long

Read-only. Returns the spot in the parent collection where the current chart is located

Legend

Legend

Read-only. Returns the formatting properties for a Legend. Use with the HasLegend property

MailEnvelope

MsoEnvelope

Set/Get the e-mail header for a document

Name

String

Set/Get the name of the chart

Next

Object

Read-only. Returns the next sheet in the workbook (from left to right) as an object

PageSetup

PageSetup

Read-only. Returns an object to manipulate the page setup properties for the chart

Perspective

Long

Sets the perspective, in degrees, at which a 3D chart will be viewed if the RightAngleAxes property is set to False

Name

Returns

Description

PivotLayout

PivotLayout

Read-only. Returns an object to manipulate the location of fields for a PivotChart report

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 Only

Boolean

Set/Get whether only visible cells are plotted or if invisible cells are plotted too (False)

Previous

Read-only. Returns the previous sheet in the workbook (from right to left) as an object

Protect Contents

Boolean

Read-only. Returns whether the chart and everything in it is protected from changes

ProtectData

Boolean

Set/Get whether the source data can be redirected for a chart

Protect Drawing Objects

Boolean

Read-only. Returns whether the shapes in the chart can be modified (ProtectDrawingObjects = False)

Protect GoalSeek

Boolean

Set/Get whether the user can modify the points on a chart with a mouse action

Protect Formatting

Boolean

Set/Get whether formatting can be changed for a chart

Protection Mode

Boolean

Read-only. Returns whether protection has been applied to the user interface. Even if a chart has user interface protection on, any VBA code associated with the chart can still be accessed

Protect Selection

Boolean

Set/Get whether parts of a chart can be selected and if shapes can be put into a chart

RightAngle Axes

Variant

Set/Get whether axes are fixed at right angles for 3D 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 OverMaximum

Boolean

Set to True, this property ensures that data labels are shown even if the data point exceeds the size of the axis; this property applies to 2D charts only

Name

Returns

Description

SideWall

Walls

Read-only. Returns a Walls object allowing users to format the side wall of a 3D chart

Tab

Tab

Read-only. Returns a Tab object for a chart or a worksheet

Visible Visibility

XlSheet

Set/Get whether or not the chart is visible. The Visible property can also be set to xlVeryHidden to 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

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 Template

FileName As String

Activates and applies a template file for the chart

ApplyData Labels

[Type As Xl DataLabels Type] , [Legend Key], [Auto Text] , [Has LeaderLines], [ShowSeries Name] , [Show CategoryName], [ShowValue], [Show

Percentage] , [ShowBubble Size] , [Separator]

Sets the point labels for a chart. The Type parameter specifies whether no label, a value, a percentage of the whole, or a category label is shown. The legend key can appear by the point by setting the LegendKey parameter to True

ApplyLayout

Layout As Long

Allows a user to apply any one of the predefined layouts shown in the Ribbon

Axes

Object

Type, AxisGroup As XlAxisGroup

Returns the Axis object or the Axes collection for the associated chart. The type of axis and the axis group can be specified with the parameters

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 embedded charts (ChartObjecs) in a chart

ChartWizard [Gallery], [Format], [PlotBy], [Category Labels], [Series Labels] , [HasLegend], [Title], [Category Title], [ValueTitle], [ExtraTitle]

[Source],

A single method to modify the key properties associated with a chart. Specify the properties that you want to change. The Source specifies the data source. Gallery specifies the chart type. Format can specify one of the 10 built-in chart autoformats. The rest of the parameters set up how the source will be read, the source of category labels, the source of the series labels, whether a legend appears, and the titles of the chart and the axis. If Source is not specified, this method can only be used if the sheet containing the chart is active

CheckSpelling Dictionary], [Ignore Uppercase], [Always Suggest] , [SpellLang]

[Custom

Checks the spelling of the text in the chart. A custom dictionary can be specified (CustomDictionary), all uppercase words can be ignored (IgnoreUppercase), and Excel can be set to display a list of suggestions (AlwaysSuggest)

ClearToMatch Style

Resets the formatting for all chart elements to automatic

Copy

[Before], [After]

Adds a new copy of the chart to the position specified at the Before or After parameters

CopyPicture

[Appearance As XlPicture Appearance] , [Format As Xl-CopyPicture Format], [Size As XlPicture Appearance]

Copies the chart into the clipboard as a picture. The Appearance parameter can be used to specify whether the picture is copied as it looks on the screen or when printed. The Format parameter can specify the type of picture that will be put into the clipboard. The Size parameter is used when dealing with chart sheets to describe the size of the picture

Delete

Deletes the chart

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 Name string expression as if it were entered into a worksheet cell

Export String, [FilterName], [Interactive]

Boolean

Filename As

Saves the chart as a picture (jpg or gif format) with the name specified by Filename

ExportAsFixed Format

FormatType,

FileName As

Variant,

Quality As

Variant,

IncludeDoc

Properties

As Variant,

IgnorePrint

Areas As Variant

From As Variant,

To As Variant,

OpenAfter

Publish

Exports a file to a format specified by using the xlFixedFormatType constants

GetChart Element Long,ElementID As Long, Arg1 As Long, Arg2 As Long

x As Long, y As

Returns what is located at the coordinates x and y of the chart. Only the first two parameters are sent. Variables must be put in the last three parameters. After the method is run, the last three parameters can be checked for return values. The ElementID parameter will return one of the XlChartItem parameters. The Arg1 and Arg2 parameters may or may not hold data, depending on the type of element

Location XlChart Location, [Name]

Chart

Where As

Moves the chart to the location specified by the Where and Name parameters. The Where can specify if the chart is moving to become a chart sheet or an embedded object

Move [After]

[Before],

Moves the chart to the position specified by the parameters

OLEObjects

Object

[Index]

Returns either a single OLE object (OLEObject) or a collection of OLE objects (OLEObjects) for a chart

Name

Returns Parameters

Description

Paste

Printout [Copies], [Preview], [ActivePrinte] [PrintToFile], [Collate], [PrToFile Name]

PrintPreview

[EnableChanges]

Protect

[DrawingObject

[Contents],

[Scenarios],

[User

Interface

Only]

[Password],

Refresh

SaveAs String, [FileFormat], [Password], [WriteRes Password], [Readonly Recommended], [CreateBackup] [AddToMru], [TextCodepage] [TextVisual Layout], [Local]

Filename As

Pastes the data or pictures from the clipboard into the chart. The Type parameter can be used to specify if only formats, formulas, or everything is pasted

Prints the chart. The printer, number of copies, collation, and whether a print preview is desired can be specified with the parameters. Also, the sheets can be printed to a file by using the PrintToFile and PrToFileName parameters. The From and To parameters can be used to specify the range of printed pages

Displays the current chart in the collection in a print preview mode. Set the EnableChanges parameter to False to disable the Margins and Setup buttons, hence not allowing the viewer to modify the page setup

Protects the chart from changes. A case-sensitive Password can be specified. Also, determines whether shapes are protected (DrawingObjects), the entire contents are protected (Contents), or only the user interface is protected (UserInterfaceOnly)

Refreshes the chart with the data source

Saves the current chart into a new workbook with the filename specified by the Filename parameter. A file format, password, write-only password, creation of backup files, and other properties of the saved file can be specified with the parameters

Table continued on following page

Name

Returns

Parameters

Description

SaveChart Template

Filename As String

Saves the specified chart as a custom chart 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 Collection

Object

[Index]

Returns either a single series (Series) or a collection of series (SeriesCollection) for a chart

SetBackground

Picture

String

FileName As

Sets the chart's background to the picture specified by the FileName parameter

SetDefault

Chart

String

FileName As

Specifies the name of the chart template that is used when new charts are created

SetElement

Element As MsoChart-ElementType

Sets/Gets the elements on a chart. Use the MsoChartElementType constants to identify the elements you want to set/get

SetSourceData Range, [PlotBy]

Source As Sets the source of the chart's data to the range specified by the Source parameter. The PlotBy parameter uses the XlRowCol constants to choose whether rows or columns of data will be plotted

Source As Sets the source of the chart's data to the range specified by the Source parameter. The PlotBy parameter 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 Click

XlChartItem, Argl As Long, Arg2 As Long, Cancel As Boolean

ElementID As Triggered just before a user double-clicks a chart. The element that was double-clicked in the chart is passed into the event procedure as ElementID. The Arg1 and Arg2 parameters may or may not hold values depending on the ElementID. The double-click action can be canceled by setting the Cancel parameter to True

SetSourceData Range, [PlotBy]

Name

Parameters

Description

BeforeRight Click

Cancel As Boolean

Triggered just before a user right-clicks a chart. The 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 XlMouse Button, Shift As Long, x As Long, y As Long

Button As

Triggered when the mouse button is pressed down on a chart. Which mouse button is pressed is passed in with the Button parameter. The Shift parameter holds information regarding the state of the Shift, Ctrl, and Alt keys. The x and y parameters hold the x and y coordinates of the mouse pointer

MouseMove XlMouse Button, Shift As Long, x As Long, y As Long

Button As

Triggered when the mouse is moved on a chart. Which mouse button is pressed is passed in with the Button parameter. The Shift parameter holds information regarding the state of the Shift, Ctrl, and Alt keys. The x and y parameters hold the x and y coordinates of the mouse pointer

MouseUp XlMouse Button,Shift As Long, x As Long, y As Long

Button As

Triggered when the mouse button is released on a chart. Which mouse button is pressed is passed in with the Button parameter. The Shift parameter holds information regarding the state of the Shift, Ctrl, and Alt keys. The x and y parameters hold the x and y coordinates of the mouse pointer

Resize

Triggered when the chart is resized

Select XlChartItem, Arg1 As Long, Arg2 As Long

ElementID As

Triggered when one of the elements in a chart is selected. The element that was selected in the chart is passed into the event procedure as ElementID. The Arg1 and Arg2 parameters may or may not hold values depending on the ElementID

SeriesChange As Long, PointIndex As Long

Seriesindex

Triggered when the value of a point on a chart is changed. SeriesIndex returns the location of the series in the chart series collection. PointIndex returns the point location in the series

+1 0

Post a comment