Microsoft Office Tutorials and References
In Depth Information
Changing the Data Used in a Chart
Set ChtObj = ActiveSheet.ChartObjects(1)
UserRow = ActiveCell.Row
If UserRow < 4 Or IsEmpty(Cells(UserRow, 1)) Then
ChtObj.Visible = False
ChtObj.Chart.SeriesCollection(1).Values = _
Range(Cells(UserRow, 2), Cells(UserRow, 6))
ChtObj.Chart.ChartTitle.Text = Cells(UserRow, 1).Text
ChtObj.Visible = True
The UserRow variable contains the row number of the active cell. The If statement checks that
the active cell is in a row that contains data. (The data starts in row 4.) If the cell cursor is in a
row that doesn’t have data, the ChartObject object is hidden, and the underlying text is visible
(“Cannot display chart”). Otherwise, the code sets the Values property for the Series object
to the range in columns 2–6 of the active row. It also sets the ChartTitle object to correspond
to the text in column A.
This example, named chart active cell.xlsm , is available on the companion
Using VBA to determine the ranges used in a chart
The previous example demonstrated how to use the Values property of a Series object to
specify the data used by a chart series. This section discusses using VBA macros to identify the
ranges used by a series in a chart. For example, you might want to increase the size of each
series by adding a new cell to the range.
Following is a description of three properties that are relevant to this task:
h Formula property: Returns or sets the SERIES formula for the Series . When you select
a series in a chart, its SERIES formula is displayed in the formula bar. The Formula
property returns this formula as a string.
h Values property: Returns or sets a collection of all the values in the series. This property
can be specified as a range on a worksheet or as an array of constant values, but not a
combination of both.
h XValues property: Returns or sets an array of X values for a chart series. The XValues
property can be set to a range on a worksheet or to an array of values, but it can’t be a
combination of both. The XValues property can also be empty.
If you create a VBA macro that needs to determine the data range used by a particular chart
series, you might think that the Values property of the Series object is just the ticket.
Similarly, the XValues property seems to be the way to get the range that contains the X values
(or category labels). In theory, that certainly seems correct. But, in practice, it doesn’t work.