Excel 2003 - VBA - Charts

[C Brandt]

I am beginning an effort using VBA to create a custom chart to show data
over a period of time for a select client. I realize this is going to be a
long row to hoe but I need the results that the efforts will provide.

I have done quite a bit of work with charting under VBA, but every time I
start a new program, it's like I'm a virgin all over again.

Previously, I have created an embedded chart, than modified it using VBA.
This time I would like to recreate the chart every time as the chart may
vary significantly with each pass; different types of plots, using one or
two axis, etc.



The following is the start of my code (Dim excluded):



Sheets("Calc").Select

BegCht = Cells(1, 9) ' Get Start Date

BegChtR = Cells(1, 10) ' Get Start Date Row

EndCht = Cells(1, 12) ' Get End Date

EndChtR = Cells(1, 13) ' Get End Date Row

WhoCht = Cells(2, 14) ' Get who the chart belongs to



' Create Chart and Name it

Charts.Add Before:=Sheets("Calc")

ActiveChart.Name = "Returns"

ActiveChart.HasTitle = True

ActiveChart.ChartTitle.Text = "Returns Chart for " & WhoCht & Chr(10) &
"From " & BegCht & " To " & EndCht & ""



Up to here I have received the desired results. Now I want to add the first
series.



Charts("Returns").SeriesCollection(1).Add _
Source:=Sheets("Calc").Range(Cells(BegChtR, 9), Cells(EndChtR, 9))



I get the error [Method 'cells' of object '_Global' failed].



There seems to be two sets of very vague rules for working with embedded
charts and those you elect to create on a Chart sheet. Very frustrating.



Any help would be greatly appreciated.

Thank,

Craig

 

[Jon Peltier]

The simple answer is to reference the cells by their parent worksheet:

Charts("Returns").SeriesCollection(1).Add _
Source:=Sheets("Calc").Range( _
Sheets("Calc").Cells(BegChtR, 9), _
Sheets("Calc").Cells(EndChtR, 9))

I find that an easier approach is to use .NewSeries, using one of the
following:

With Charts("Returns").SeriesCollection.NewSeries
' series Y Values
.Values = Sheets("Calc").Range( _
Sheets("Calc").Cells(BegChtR, 9), _
Sheets("Calc").Cells(EndChtR, 9))
' other stuff relating to the new series:
'.XValues
'.Name
'.ChartType
' formatting, etc
End With

or

Dim MySrs As Series
Set MySrs = Charts("Returns").SeriesCollection.NewSeries
MySrs.Values = Sheets("Calc").Range( _
Sheets("Calc").Cells(BegChtR, 9), _
Sheets("Calc").Cells(EndChtR, 9))
' other stuff:
'MySrs.XValues
'etc

More chart VBA stuff:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

- Jon