charts

[defj]

I want to use VBA to add data on the fly to just 1 series
of a multiple series chart. I've tried:

....SeriesCollection(x).Extend Worksheets(z).Range(y)

apparently the Extend method only works for the entire
collection of series (almost too logical) you can set
individual series properties using SeriesCollecion(x) but
not the method...how do i break this down to the
individual series level?

 

[Jon Peltier]

What you need to do is to find the X and Y ranges in the sheet, then
extend these by a cell. The painful part is that VBA can't get the data
range of a series. Fortunately John Walkenbach has posted a class module
that parses the series formula and tells VBA what it is. Download the
ChartSeriesInfo Class example (it's under charting in the Developer Tips
part of his site, http://j-walk.com), and drag the class module into
your workbook's VBA project.

Now you just need to assign the chartinfoclass to your series. Pop this
code into a regular code module (not the class module), then select a
series (or point) and run it. Nothing will happen if the selected item
isn't a point or a series.

Sub ExtendSeries()
' Extend selected series by one point
' Requires John Walkenbach's ChartSeriesInfo Class
' http://j-walk.com
Dim MyChart As New ChartSeries
Dim sSers As String
Dim rngSers As Range

If TypeName(Selection) = "Series" Or _
TypeName(Selection) = "Point" Then
sSers = Application.ExecuteExcel4Macro("SELECTION()")
If InStr(1, sSers, "P") Then sSers = _
Left(sSers, InStr(1, sSers, "P") - 1)
sSers = Mid(sSers, 2)
With MyChart
.Chart = ActiveChart
.ChartSeries = CInt(sSers)
If .ValuesType = "Range" Then
If .Values.Rows.Count > 1 Then
.Values = .Values.Resize _
(rowsize:=.Values.Rows.Count + 1)
ElseIf .Values.Columns.Count > 1 Then
.Values = .Values.Resize _
(columnsize:=.Values.Columns.Count + 1)
End If
End If
If .XValuesType = "Range" Then
If .XValues.Rows.Count > 1 Then
.XValues = .XValues.Resize _
(rowsize:=.XValues.Rows.Count + 1)
ElseIf .XValues.Columns.Count > 1 Then
.XValues = .XValues.Resize _
(columnsize:=.XValues.Columns.Count + 1)
End If
End If
End With
End If
End Sub

- Jon

 

[Jon Peltier]

Doh! The way I wrote the previous macro, only the values from the new
range were put into the chart series formula. The following macro
actually changes the range referenced in the series formula.

Sub ExtendSeries()
' Extend selected series by one point
' Requires John Walkenbach's ChartSeriesInfo Class
' http://j-walk.com
Dim MyChart As New ChartSeries
Dim sSers As String
Dim rngSers As Range

If TypeName(Selection) = "Series" Or _
TypeName(Selection) = "Point" Then
sSers = Application.ExecuteExcel4Macro("SELECTION()")
If InStr(1, sSers, "P") Then sSers = _
Left(sSers, InStr(1, sSers, "P") - 1)
sSers = Mid(sSers, 2)
With MyChart
.Chart = ActiveChart
.ChartSeries = CInt(sSers)
If .ValuesType = "Range" Then
Set rngSers = .Values
If rngSers.Rows.Count > 1 Then
Set rngSers = rngSers.Resize _
(rowsize:=rngSers.Rows.Count + 1)
ElseIf rngSers.Columns.Count > 1 Then
Set rngSers = rngSers.Resize _
(columnsize:=rngSers.Columns.Count + 1)
End If
.Values = rngSers
End If
If .XValuesType = "Range" Then
Set rngSers = .XValues
If rngSers.Rows.Count > 1 Then
Set rngSers = rngSers.Resize _
(rowsize:=rngSers.Rows.Count + 1)
ElseIf rngSers.Columns.Count > 1 Then
Set rngSers = rngSers.Resize _
(columnsize:=rngSers.Columns.Count + 1)
End If
.XValues = rngSers
End If
End With
End If
End Sub

Sub ExtendSeries()
' Extend selected series by one point
' Requires John Walkenbach's ChartSeriesInfo Class
' http://j-walk.com
Dim MyChart As New ChartSeries
Dim sSers As String
Dim rngSers As Range

If TypeName(Selection) = "Series" Or _
TypeName(Selection) = "Point" Then
sSers = Application.ExecuteExcel4Macro("SELECTION()")
If InStr(1, sSers, "P") Then sSers = _
Left(sSers, InStr(1, sSers, "P") - 1)
sSers = Mid(sSers, 2)
With MyChart
.Chart = ActiveChart
.ChartSeries = CInt(sSers)
If .ValuesType = "Range" Then
Set rngSers = .Values
If rngSers.Rows.Count > 1 Then
Set rngSers = rngSers.Resize _
(rowsize:=rngSers.Rows.Count + 1)
ElseIf rngSers.Columns.Count > 1 Then
Set rngSers = rngSers.Resize _
(columnsize:=rngSers.Columns.Count + 1)
End If
.Values = rngSers
End If
If .XValuesType = "Range" Then
Set rngSers = .XValues
If rngSers.Rows.Count > 1 Then
Set rngSers = rngSers.Resize _
(rowsize:=rngSers.Rows.Count + 1)
ElseIf rngSers.Columns.Count > 1 Then
Set rngSers = rngSers.Resize _
(columnsize:=rngSers.Columns.Count + 1)
End If
.XValues = rngSers
End If
End With
End If
End Sub