Problem setting SeriesCollections source range

In VB6 sp4, I'm having problems setting the SeriesCollection() range. The
end row of the range will be dependent on the number of bins the user
declares so will always be variable. I've created a macro in Excel to use as
a guideline, but have had little luck with it. The chart will have 4
SeriesCollections one of which should only be visible/used for the XValues.
Dimming the strRange1 and strRange2 as Objects and using the Set statment to
define them stops compiling at the start of the Sub. Perhaps I'm using the
Set statment incorrectly. Dimming the strRange1 and strRange2 as xlLineChart
gives a message that "Compile Error: user-defined type not defined". I've
tried so many different ways of getting this to work, I've lost count and by
now I have a real mess. I'm really hoping someone out there can help.

Dim xlApp As Excel.Application
Dim xlWkBook As Excel.Workbook
Dim xlWkSheet As Excel.Worksheet
Dim xlLineChart As ChartObject

Set xlApp = New Excel.Application
Set xlWkBook = xlApp.Workbooks.Add

Dim strRange1 As String
Dim strRange2 As String
Dim varSeriesRange As Variant

eRows = 9 'start row for data output
NumLags = Val(txtLag.Text) 'assigns user input # of bins
intEnd = eRows + (NumLags * 2) 'end row for data output

strRange1 = "A" & eRows & ":A" & intEnd
strRange2 = "B" & eRows & ":B" & intEnd

varSeriesRange = "=R9" & "C1" & ":R" & intEnd & "C1"

Set xlLineChart = ActiveSheet.ChartObjects.Add(230, 100, 400, 250)
xlLineChart.Activate
xlLineChart.Chart.ChartType = xlLineMarkers
With xlLineChart.Chart
.SetSourceData ActiveSheet.Range(strRange1, strRange2)
.SetSourceData Range(strRange1, strRange2), .PlotBy = xlColumns

'*****Compiles to here then error 438 - Object doesn't support
'*****This doesn't work even hard coding the source range

.SeriesCollection(2).Add ActiveSheet.Range(strRange2) '("b9:b29")

.SeriesCollection(1).XValues = varSeriesRange '"=R9C1:R29C1"
End With

strRange1 and 2 are strings, as is varSeriesRange. You can't declare a
variable of type xlLineChart, because xlLineChart is an Excel constant.

change the declaration statement to:
Dim xlLineChart As Excel.ChartObject

don't do this:
xlLineChart.Activate

why use both:
.SetSourceData ActiveSheet.Range(strRange1, strRange2)
.SetSourceData Range(strRange1, strRange2), .PlotBy = xlColumns
use this:
.SetSourceData activesheet.Range(strRange1, strRange2), .PlotBy =
xlColumns

finally, the fatal problem. This fails because SeriesCollection(2) is a
series, and you can only use the Add method with the series collection:
.SeriesCollection(2).Add ActiveSheet.Range(strRange2)
try this:
.SeriesCollection.Add ActiveSheet.Range(strRange2)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"Lead Foot" wrote in message
...
In VB6 sp4, I'm having problems setting the SeriesCollection() range. The
end row of the range will be dependent on the number of bins the user
declares so will always be variable. I've created a macro in Excel to use
as
a guideline, but have had little luck with it. The chart will have 4
SeriesCollections one of which should only be visible/used for the
XValues.
Dimming the strRange1 and strRange2 as Objects and using the Set statment
to
define them stops compiling at the start of the Sub. Perhaps I'm using
the
Set statment incorrectly. Dimming the strRange1 and strRange2 as
xlLineChart
gives a message that "Compile Error: user-defined type not defined". I've
tried so many different ways of getting this to work, I've lost count and
by
now I have a real mess. I'm really hoping someone out there can help.

Dim xlApp As Excel.Application
Dim xlWkBook As Excel.Workbook
Dim xlWkSheet As Excel.Worksheet
Dim xlLineChart As ChartObject

Set xlApp = New Excel.Application
Set xlWkBook = xlApp.Workbooks.Add

Dim strRange1 As String
Dim strRange2 As String
Dim varSeriesRange As Variant

eRows = 9 'start row for data output
NumLags = Val(txtLag.Text) 'assigns user input # of bins
intEnd = eRows + (NumLags * 2) 'end row for data output

strRange1 = "A" & eRows & ":A" & intEnd
strRange2 = "B" & eRows & ":B" & intEnd

varSeriesRange = "=R9" & "C1" & ":R" & intEnd & "C1"

Set xlLineChart = ActiveSheet.ChartObjects.Add(230, 100, 400, 250)
xlLineChart.Activate
xlLineChart.Chart.ChartType = xlLineMarkers
With xlLineChart.Chart
.SetSourceData ActiveSheet.Range(strRange1, strRange2)
.SetSourceData Range(strRange1, strRange2), .PlotBy = xlColumns

'*****Compiles to here then error 438 - Object doesn't support
'*****This doesn't work even hard coding the source range

.SeriesCollection(2).Add ActiveSheet.Range(strRange2) '("b9:b29")

.SeriesCollection(1).XValues = varSeriesRange '"=R9C1:R29C1"
End With

Thanks Jon -

That helps a lot. This was the first time I have tried to create a chart
using VB programming and was following the macro that was created in Excel.
I have had a lot of problems just finding the info I needed. I discovered
your website after doing a search in MS Newgroups on SeriesCollection. Your
response has helped me to see why it wasn't working and your website helped
me to see how it should be programmed. Thanks again for the response. I
will rewrite the sub and see if I can get it right this time. Lead Foot

"Jon Peltier" wrote:

strRange1 and 2 are strings, as is varSeriesRange. You can't declare a
variable of type xlLineChart, because xlLineChart is an Excel constant.

change the declaration statement to:
Dim xlLineChart As Excel.ChartObject

don't do this:
xlLineChart.Activate

why use both:
.SetSourceData ActiveSheet.Range(strRange1, strRange2)
.SetSourceData Range(strRange1, strRange2), .PlotBy = xlColumns
use this:
.SetSourceData activesheet.Range(strRange1, strRange2), .PlotBy =
xlColumns

finally, the fatal problem. This fails because SeriesCollection(2) is a
series, and you can only use the Add method with the series collection:
.SeriesCollection(2).Add ActiveSheet.Range(strRange2)
try this:
.SeriesCollection.Add ActiveSheet.Range(strRange2)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"Lead Foot" wrote in message
...
In VB6 sp4, I'm having problems setting the SeriesCollection() range. The
end row of the range will be dependent on the number of bins the user
declares so will always be variable. I've created a macro in Excel to use
as
a guideline, but have had little luck with it. The chart will have 4
SeriesCollections one of which should only be visible/used for the
XValues.
Dimming the strRange1 and strRange2 as Objects and using the Set statment
to
define them stops compiling at the start of the Sub. Perhaps I'm using
the
Set statment incorrectly. Dimming the strRange1 and strRange2 as
xlLineChart
gives a message that "Compile Error: user-defined type not defined". I've
tried so many different ways of getting this to work, I've lost count and
by
now I have a real mess. I'm really hoping someone out there can help.

Dim xlApp As Excel.Application
Dim xlWkBook As Excel.Workbook
Dim xlWkSheet As Excel.Worksheet
Dim xlLineChart As ChartObject

Set xlApp = New Excel.Application
Set xlWkBook = xlApp.Workbooks.Add

Dim strRange1 As String
Dim strRange2 As String
Dim varSeriesRange As Variant

eRows = 9 'start row for data output
NumLags = Val(txtLag.Text) 'assigns user input # of bins
intEnd = eRows + (NumLags * 2) 'end row for data output

strRange1 = "A" & eRows & ":A" & intEnd
strRange2 = "B" & eRows & ":B" & intEnd

varSeriesRange = "=R9" & "C1" & ":R" & intEnd & "C1"

Set xlLineChart = ActiveSheet.ChartObjects.Add(230, 100, 400, 250)
xlLineChart.Activate
xlLineChart.Chart.ChartType = xlLineMarkers
With xlLineChart.Chart
.SetSourceData ActiveSheet.Range(strRange1, strRange2)
.SetSourceData Range(strRange1, strRange2), .PlotBy = xlColumns

'*****Compiles to here then error 438 - Object doesn't support
'*****This doesn't work even hard coding the source range

.SeriesCollection(2).Add ActiveSheet.Range(strRange2) '("b9:b29")

.SeriesCollection(1).XValues = varSeriesRange '"=R9C1:R29C1"
End With