software

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 quot;Compile Error: user-defined type not definedquot;. 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 = quot;Aquot; amp; eRows amp; quot;:Aquot; amp; intEnd
strRange2 = quot;Bquot; amp; eRows amp; quot;:Bquot; amp; intEnd

varSeriesRange = quot;=R9quot; amp; quot;C1quot; amp; quot;:Rquot; amp; intEnd amp; quot;C1quot;

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) '(quot;b9:b29quot;)

.SeriesCollection(1).XValues = varSeriesRange 'quot;=R9C1:R29C1quot;
End WithstrRange1 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
PeltierTech.com/
_______

quot;Lead Footquot; gt; wrote in message
...
gt; In VB6 sp4, I'm having problems setting the SeriesCollection() range. The
gt; end row of the range will be dependent on the number of bins the user
gt; declares so will always be variable. I've created a macro in Excel to use
gt; as
gt; a guideline, but have had little luck with it. The chart will have 4
gt; SeriesCollections one of which should only be visible/used for the
gt; XValues.
gt; Dimming the strRange1 and strRange2 as Objects and using the Set statment
gt; to
gt; define them stops compiling at the start of the Sub. Perhaps I'm using
gt; the
gt; Set statment incorrectly. Dimming the strRange1 and strRange2 as
gt; xlLineChart
gt; gives a message that quot;Compile Error: user-defined type not definedquot;. I've
gt; tried so many different ways of getting this to work, I've lost count and
gt; by
gt; now I have a real mess. I'm really hoping someone out there can help.
gt;
gt; Dim xlApp As Excel.Application
gt; Dim xlWkBook As Excel.Workbook
gt; Dim xlWkSheet As Excel.Worksheet
gt; Dim xlLineChart As ChartObject
gt;
gt; Set xlApp = New Excel.Application
gt; Set xlWkBook = xlApp.Workbooks.Add
gt;
gt; Dim strRange1 As String
gt; Dim strRange2 As String
gt; Dim varSeriesRange As Variant
gt;
gt; eRows = 9 'start row for data output
gt; NumLags = Val(txtLag.Text) 'assigns user input # of bins
gt; intEnd = eRows (NumLags * 2) 'end row for data output
gt;
gt; strRange1 = quot;Aquot; amp; eRows amp; quot;:Aquot; amp; intEnd
gt; strRange2 = quot;Bquot; amp; eRows amp; quot;:Bquot; amp; intEnd
gt;
gt; varSeriesRange = quot;=R9quot; amp; quot;C1quot; amp; quot;:Rquot; amp; intEnd amp; quot;C1quot;
gt;
gt; Set xlLineChart = ActiveSheet.ChartObjects.Add(230, 100, 400, 250)
gt; xlLineChart.Activate
gt; xlLineChart.Chart.ChartType = xlLineMarkers
gt; With xlLineChart.Chart
gt; .SetSourceData ActiveSheet.Range(strRange1, strRange2)
gt; .SetSourceData Range(strRange1, strRange2), .PlotBy = xlColumns
gt;
gt; '*****Compiles to here then error 438 - Object doesn't support
gt; '*****This doesn't work even hard coding the source range
gt;
gt; .SeriesCollection(2).Add ActiveSheet.Range(strRange2) '(quot;b9:b29quot;)
gt;
gt; .SeriesCollection(1).XValues = varSeriesRange 'quot;=R9C1:R29C1quot;
gt; End With
gt;
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

quot;Jon Peltierquot; wrote:

gt; strRange1 and 2 are strings, as is varSeriesRange. You can't declare a
gt; variable of type xlLineChart, because xlLineChart is an Excel constant.
gt;
gt; change the declaration statement to:
gt; Dim xlLineChart As Excel.ChartObject
gt;
gt; don't do this:
gt; xlLineChart.Activate
gt;
gt; why use both:
gt; .SetSourceData ActiveSheet.Range(strRange1, strRange2)
gt; .SetSourceData Range(strRange1, strRange2), .PlotBy = xlColumns
gt; use this:
gt; .SetSourceData activesheet.Range(strRange1, strRange2), .PlotBy =
gt; xlColumns
gt;
gt; finally, the fatal problem. This fails because SeriesCollection(2) is a
gt; series, and you can only use the Add method with the series collection:
gt; .SeriesCollection(2).Add ActiveSheet.Range(strRange2)
gt; try this:
gt; .SeriesCollection.Add ActiveSheet.Range(strRange2)
gt;
gt; - Jon
gt; -------
gt; Jon Peltier, Microsoft Excel MVP
gt; Peltier Technical Services
gt; Tutorials and Custom Solutions
gt; PeltierTech.com/
gt; _______
gt;
gt; quot;Lead Footquot; gt; wrote in message
gt; ...
gt; gt; In VB6 sp4, I'm having problems setting the SeriesCollection() range. The
gt; gt; end row of the range will be dependent on the number of bins the user
gt; gt; declares so will always be variable. I've created a macro in Excel to use
gt; gt; as
gt; gt; a guideline, but have had little luck with it. The chart will have 4
gt; gt; SeriesCollections one of which should only be visible/used for the
gt; gt; XValues.
gt; gt; Dimming the strRange1 and strRange2 as Objects and using the Set statment
gt; gt; to
gt; gt; define them stops compiling at the start of the Sub. Perhaps I'm using
gt; gt; the
gt; gt; Set statment incorrectly. Dimming the strRange1 and strRange2 as
gt; gt; xlLineChart
gt; gt; gives a message that quot;Compile Error: user-defined type not definedquot;. I've
gt; gt; tried so many different ways of getting this to work, I've lost count and
gt; gt; by
gt; gt; now I have a real mess. I'm really hoping someone out there can help.
gt; gt;
gt; gt; Dim xlApp As Excel.Application
gt; gt; Dim xlWkBook As Excel.Workbook
gt; gt; Dim xlWkSheet As Excel.Worksheet
gt; gt; Dim xlLineChart As ChartObject
gt; gt;
gt; gt; Set xlApp = New Excel.Application
gt; gt; Set xlWkBook = xlApp.Workbooks.Add
gt; gt;
gt; gt; Dim strRange1 As String
gt; gt; Dim strRange2 As String
gt; gt; Dim varSeriesRange As Variant
gt; gt;
gt; gt; eRows = 9 'start row for data output
gt; gt; NumLags = Val(txtLag.Text) 'assigns user input # of bins
gt; gt; intEnd = eRows (NumLags * 2) 'end row for data output
gt; gt;
gt; gt; strRange1 = quot;Aquot; amp; eRows amp; quot;:Aquot; amp; intEnd
gt; gt; strRange2 = quot;Bquot; amp; eRows amp; quot;:Bquot; amp; intEnd
gt; gt;
gt; gt; varSeriesRange = quot;=R9quot; amp; quot;C1quot; amp; quot;:Rquot; amp; intEnd amp; quot;C1quot;
gt; gt;
gt; gt; Set xlLineChart = ActiveSheet.ChartObjects.Add(230, 100, 400, 250)
gt; gt; xlLineChart.Activate
gt; gt; xlLineChart.Chart.ChartType = xlLineMarkers
gt; gt; With xlLineChart.Chart
gt; gt; .SetSourceData ActiveSheet.Range(strRange1, strRange2)
gt; gt; .SetSourceData Range(strRange1, strRange2), .PlotBy = xlColumns
gt; gt;
gt; gt; '*****Compiles to here then error 438 - Object doesn't support
gt; gt; '*****This doesn't work even hard coding the source range
gt; gt;
gt; gt; .SeriesCollection(2).Add ActiveSheet.Range(strRange2) '(quot;b9:b29quot;)
gt; gt;
gt; gt; .SeriesCollection(1).XValues = varSeriesRange 'quot;=R9C1:R29C1quot;
gt; gt; End With
gt; gt;
gt;
gt;
gt;