I have been spending a lot of time trying to figure out dynamic chart
ranges and just can't figure it out. I have a chart (scatter plot)
that needs to have the X and Y values of a series linked to the number
of values present, which will be constantly changing. It would also be
nice to have the maximum/minimum scale adjustable as well, for both
axes. the code I am using (below) is causing errors.

Private Sub UpdateChart()
span = Sheets("sheet1").Range("C4").Value * 12
stepsize = Sheets("sheet1").Range("C6").Value * 12
Set CurrentChart = Sheets("sheet2").ChartObjects(1).Chart
CurrentChart.Parent.Width = 450
CurrentChart.Parent.Height = 150

'ActiveChart.SeriesCollection(1).XValues = "=Sheet2!R2C2:R2C" & 2
+_
(span / stepsize) & ""
'ActiveChart.SeriesCollection(1).Values = "=Sheet2!R1C2:R1C" & 2 +
_
(span / stepsize) & ""

ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = span / stepsize + 1
.MinorUnit = 12
.MajorUnit = 24
.Crosses = 0
End With

' Save chart as GIF
Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
CurrentChart.Export Filename:=Fname, FilterName:="GIF"

' Show the chart
Image3.Picture = LoadPicture(Fname)
End Sub

Jacob wrote:
I have been spending a lot of time trying to figure out dynamic chart
ranges and just can't figure it out. I have a chart (scatter plot)
that needs to have the X and Y values of a series linked to the number
of values present, which will be constantly changing. It would also be
nice to have the maximum/minimum scale adjustable as well, for both
axes. the code I am using (below) is causing errors.

Private Sub UpdateChart()
span = Sheets("sheet1").Range("C4").Value * 12
stepsize = Sheets("sheet1").Range("C6").Value * 12
Set CurrentChart = Sheets("sheet2").ChartObjects(1).Chart
CurrentChart.Parent.Width = 450
CurrentChart.Parent.Height = 150

'ActiveChart.SeriesCollection(1).XValues = "=Sheet2!R2C2:R2C" & 2
+_
(span / stepsize) & ""
'ActiveChart.SeriesCollection(1).Values = "=Sheet2!R1C2:R1C" & 2 +
_
(span / stepsize) & ""

ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = span / stepsize + 1
.MinorUnit = 12
.MajorUnit = 24
.Crosses = 0
End With

' Save chart as GIF
Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
CurrentChart.Export Filename:=Fname, FilterName:="GIF"

' Show the chart
Image3.Picture = LoadPicture(Fname)
End Sub

1) Why did you set CurrentChart then switch to ActiveChart? Using
CurrentChart would be more consistent.

2) Replace "=Sheet2!R2C2:R2C" & 2 + (span / stepsize) & ""
by "=Sheet2!R2C2:R2C" & (2 + (span / stepsize)) & """

Note the extra double quote - "" is the empty string

3) A more radical suggestion: when you create the chart, let the X and
Y values be *names*. For example, I have a chart (involving dice
probabilities) where I created a chart and typed this in the source
data under series/X Values:

='Dice Sums.xls'!xvals

And the same thing for y (but with "y" of course). "xvals" is the name
of the range which is to contain the xvals.
Then - in the code I create a Range variable xRange and, after loading
it with what I want by a combination of the Range function and Offset
something like:

Set xRange = Range(Range("A1"),Range("A1").Offset(k))

I simply use the statement

xRange.Name = "xvals"

and now the Chart will have the right x-vals

Hope that helps

-semiopen

Thanks for the suggestions. I am better off avoiding the radical
solution for now. plus I want to learn more about manipulating charts
from VBA.

I am not sure what I doing. that's why I switched from Current to
Active. I changed that and tried to add the quote, but it would
automatically add one on top of that. so I get this:

Private Sub UpdateChart()
span = Sheets("sheet1").Range("C4").Value * 12
stepsize = Sheets("sheet1").Range("C6").Value * 12
Set CurrentChart = Sheets("sheet2").ChartObjects(1).Chart
CurrentChart.Parent.Width = 450
CurrentChart.Parent.Height = 150

CurrentChart.SeriesCollection(1).XValues = "=Sheet2!R2C2:R2C" & (2
+ (span /_ stepsize)) & """"
CurrentChart.SeriesCollection(1).Values = "=Sheet2!R1C2:R1C" & (2 +
(span /_ stepsize)) & """"

CurrentChart.Axes(xlCategory).Selec
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = span / stepsize + 1
.MinorUnit = 12
.MajorUnit = 24
.Crosses = 0
End With

' Save chart as GIF
Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
CurrentChart.Export Filename:=Fname, FilterName:="GIF"

' Show the chart
Image3.Picture = LoadPicture(Fname)
End Sub


I get the following error:

Unable to set XValues property of the series class


semiopen wrote:
Jacob wrote:
I have been spending a lot of time trying to figure out dynamic chart
ranges and just can't figure it out. I have a chart (scatter plot)
that needs to have the X and Y values of a series linked to the number
of values present, which will be constantly changing. It would also be
nice to have the maximum/minimum scale adjustable as well, for both
axes. the code I am using (below) is causing errors.

Private Sub UpdateChart()
span = Sheets("sheet1").Range("C4").Value * 12
stepsize = Sheets("sheet1").Range("C6").Value * 12
Set CurrentChart = Sheets("sheet2").ChartObjects(1).Chart
CurrentChart.Parent.Width = 450
CurrentChart.Parent.Height = 150

'ActiveChart.SeriesCollection(1).XValues = "=Sheet2!R2C2:R2C" & 2
+_
(span / stepsize) & ""
'ActiveChart.SeriesCollection(1).Values = "=Sheet2!R1C2:R1C" & 2 +
_
(span / stepsize) & ""

ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = span / stepsize + 1
.MinorUnit = 12
.MajorUnit = 24
.Crosses = 0
End With

' Save chart as GIF
Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
CurrentChart.Export Filename:=Fname, FilterName:="GIF"

' Show the chart
Image3.Picture = LoadPicture(Fname)
End Sub

1) Why did you set CurrentChart then switch to ActiveChart? Using
CurrentChart would be more consistent.

2) Replace "=Sheet2!R2C2:R2C" & 2 + (span / stepsize) & ""
by "=Sheet2!R2C2:R2C" & (2 + (span / stepsize)) & """

Note the extra double quote - "" is the empty string

3) A more radical suggestion: when you create the chart, let the X and
Y values be *names*. For example, I have a chart (involving dice
probabilities) where I created a chart and typed this in the source
data under series/X Values:

='Dice Sums.xls'!xvals

And the same thing for y (but with "y" of course). "xvals" is the name
of the range which is to contain the xvals.
Then - in the code I create a Range variable xRange and, after loading
it with what I want by a combination of the Range function and Offset
something like:

Set xRange = Range(Range("A1"),Range("A1").Offset(k))

I simply use the statement

xRange.Name = "xvals"

and now the Chart will have the right x-vals

Hope that helps

-semiopen

Jacob wrote:
Thanks for the suggestions. I am better off avoiding the radical
solution for now. plus I want to learn more about manipulating charts
from VBA.

I am not sure what I doing. that's why I switched from Current to
Active. I changed that and tried to add the quote, but it would
automatically add one on top of that. so I get this:

Private Sub UpdateChart()
span = Sheets("sheet1").Range("C4").Value * 12
stepsize = Sheets("sheet1").Range("C6").Value * 12
Set CurrentChart = Sheets("sheet2").ChartObjects(1).Chart
CurrentChart.Parent.Width = 450
CurrentChart.Parent.Height = 150

CurrentChart.SeriesCollection(1).XValues = "=Sheet2!R2C2:R2C" & (2
+ (span /_ stepsize)) & """"
CurrentChart.SeriesCollection(1).Values = "=Sheet2!R1C2:R1C" & (2 +
(span /_ stepsize)) & """"


Try CurrentChart.SeriesCollection(1).XValues = _
Sheets("sheet2").Range(Range(R2C2),Range(R2C2).Off set(0,span/stepsize))

XValues is expecting a range - not a string naming a range. Also - be
careful with stray line continuation characters that might arise in
editing.

You are right about needing an extra quote - I'd forgotten how to
escape a quote mark and wrote a simple test routine before I posted,
but didn't notice that the VBE had supplied the missing "

-semiopen

semiopen wrote:
Jacob wrote:
Thanks for the suggestions. I am better off avoiding the radical
solution for now. plus I want to learn more about manipulating charts
from VBA.

I am not sure what I doing. that's why I switched from Current to
Active. I changed that and tried to add the quote, but it would
automatically add one on top of that. so I get this:

Private Sub UpdateChart()
span = Sheets("sheet1").Range("C4").Value * 12
stepsize = Sheets("sheet1").Range("C6").Value * 12
Set CurrentChart = Sheets("sheet2").ChartObjects(1).Chart
CurrentChart.Parent.Width = 450
CurrentChart.Parent.Height = 150

CurrentChart.SeriesCollection(1).XValues = "=Sheet2!R2C2:R2C" & (2
+ (span /_ stepsize)) & """"
CurrentChart.SeriesCollection(1).Values = "=Sheet2!R1C2:R1C" & (2 +
(span /_ stepsize)) & """"


Try CurrentChart.SeriesCollection(1).XValues = _
Sheets("sheet2").Range(Range(R2C2),Range(R2C2).Off set(0,span/stepsize))

XValues is expecting a range - not a string naming a range. Also - be
careful with stray line continuation characters that might arise in
editing.

You are right about needing an extra quote - I'd forgotten how to
escape a quote mark and wrote a simple test routine before I posted,
but didn't notice that the VBE had supplied the missing "

I realized that my last code snippet won't work. The Range function is
expecting strings naming cells in the A1 style notation (which is the
only style I use). So - in the above code just replace each R2C2 by
"B2" (with the quote marks)

Sorry for any confusion

-semiopen

I still can't get it to work. I was able to set the maxvalue and
minvalue and that might suffice for now. here is my code:

Private Sub UpdateChart()
span = Sheets("sheet1").Range("C4").Value * 12
stepsize = Sheets("sheet1").Range("C6").Value
Set CurrentChart = Sheets("sheet2").ChartObjects(1).Chart
CurrentChart.Parent.Width = 450
CurrentChart.Parent.Height = 150

With CurrentChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = span
.MinorUnit = 12
.MajorUnit = 24
'.Crosses = 0
End With

' Save chart as GIF
Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
CurrentChart.Export Filename:=Fname, FilterName:="GIF"

' Show the chart
Image3.Picture = LoadPicture(Fname)
End Sub


semiopen wrote:
semiopen wrote:
Jacob wrote:
Thanks for the suggestions. I am better off avoiding the radical
solution for now. plus I want to learn more about manipulating charts
from VBA.

I am not sure what I doing. that's why I switched from Current to
Active. I changed that and tried to add the quote, but it would
automatically add one on top of that. so I get this:

Private Sub UpdateChart()
span = Sheets("sheet1").Range("C4").Value * 12
stepsize = Sheets("sheet1").Range("C6").Value * 12
Set CurrentChart = Sheets("sheet2").ChartObjects(1).Chart
CurrentChart.Parent.Width = 450
CurrentChart.Parent.Height = 150

CurrentChart.SeriesCollection(1).XValues = "=Sheet2!R2C2:R2C" & (2
+ (span /_ stepsize)) & """"
CurrentChart.SeriesCollection(1).Values = "=Sheet2!R1C2:R1C" & (2 +
(span /_ stepsize)) & """"


Try CurrentChart.SeriesCollection(1).XValues = _
Sheets("sheet2").Range(Range(R2C2),Range(R2C2).Off set(0,span/stepsize))

XValues is expecting a range - not a string naming a range. Also - be
careful with stray line continuation characters that might arise in
editing.

You are right about needing an extra quote - I'd forgotten how to
escape a quote mark and wrote a simple test routine before I posted,
but didn't notice that the VBE had supplied the missing "

I realized that my last code snippet won't work. The Range function is
expecting strings naming cells in the A1 style notation (which is the
only style I use). So - in the above code just replace each R2C2 by
"B2" (with the quote marks)

Sorry for any confusion

-semiopen